JP Pinto won the fan-favorite Great White Shark award for his previous post about Cascading Validation Lists. The folks at the Office Online blog asked JP to do a new guest post for the Excel blog and JP’s post on using INDEX and SUMPRODUCT to do a two-criteria lookup is now available here. This looks like a cool technique. JP starts out by using SUMPRODUCT to return a numeric value from a column. But if you need to return a text value, the INDEX with SUMPRODUCT and ROW solves the problem.
Category Archives: Uncategorized
I had never tried this, but you can use a wildcard in your VLOOKUP. Charles Williams shows an example. Click here for complete article.
You read correctly – 1979. In Bill’s last installment for VLOOKUP WEEK 2012, we go back to 1979…VisiCalc and the whole ’20 Functions’ available in that time! There were no IF statements and there was no VLOOKUP…but there was ‘LOOKUP’. Follow along and see where VLOOKUP began!
From QUE Publishing:
“It is day #2 of VLOOKUP Week and who better to give the lay of the VLOOKUP land than Paul McFedries, author of “Formulas and Functions for Excel 2010“. In the following post, he breaks it down nice and neat like…” Click Here To Read More…
Excel MVP Debra Dalgleish checks in on her Contextures blog with a beginning VLOOKUP example that actually tracks the number of shark attacks. (Nice use of theme, Debra!) Debra’s post includes the FLOOR function, Data Validation, and links to her videos on VLOOKUP. Read the complete article here.
I formerly worked as the Manager of Dealing with Bad Data.
OK – maybe that wasn’t my real job title, but it is what I did day after day. I was the guy who knew how to use a bad mainframe tool to extract imperfect data sets and then coax useful information out of them in a spreadsheet. I used VLOOKUP constantly to join this data with that data. Why didn’t we just get all the data in one download? Because this was 1989 and it just wasn’t possible with the $100K 4GL tool that my company purchased. Of course, using a $299 spreadsheet and VLOOKUP, everything was possible.
I held this position for 10 years. They gave me other job titles and promotions, but, I was still doing the same thing: dealing with bad data. I overheard someone ask the I.T. manager if we ran our company on Oracle or SAP. He had to admit that we pretty much ran the company on Excel. He probably didn’t mean that as a compliment, but I was proud of his statement.
When I announced VLOOKUP Week on Facebook, one of the comments was from Erica:
“I use vlookup and pivot tables countless times a day, I would be screwed at my job w/out it.”
Erica and thousands of others and I could not live without VLOOKUP. If you are one of those thousands, I invite you to comment on this post with a few lines about how you use VLOOKUP. Feel free to be creative. Be passionate. Write a little love note to VLOOKUP. You don’t need to drop into verse or rock out a haiku, but let the world know that “you have VLOOKUPs and you know how to use them”.
For our door prize today, we have a VLOOKUP Week t-shirt and another copy of Paul McFedries’ Formulas and Functions Excel 2010. To be entered in the drawing, post a comment to this thread with your Ode to VLOOKUP. Enter by 6AM on Wednesday to be eligible.
The QUE blog checks in with a VLOOKUP-themed post about using a helper row with VLOOKUP. This technique makes it easier when copying a VLOOKUP formula across several columns.
By the way, my thanks to QUE for donating five door prizes for VLOOKUP Week. If you haven’t entered the daily door prize drawing, visit the Door Prizes link across the top navigation bar.
Read the complete article here.
CFO Magazine offers a solution for growing pivot tables.
…If you need to add new rows to the lookup table, the obvious place is to type the new rows in X8:Z9. However, these rows are outside of the lookup table, so you must re-enter the existing VLOOKUP formulas…. Read complete article at CFO.com