VLOOKUP Week – Brought to You by Mr. Excel
(Click Image for the Official Site of VLOOKUP Week)
What is the “Scariest” Feature in Excel?
A couple of years ago at lunch, Bill remarked to me that VLOOKUP was the “scariest” feature in Excel, and that PowerPivot’s introduction of relationships was going to make Excel a lot “friendlier” to the average user.
This sparked a few minutes of friendly debate, as I had always considered pivots themselves to be the “scariest” feature in Excel. To be “scary,” a feature must be very useful if you know how to use it, and yet 80% or so of the Excel audience doesn’t know how to use it. VLOOKUP and Pivots both clearly meet those criteria, so it was an interesting discussion.
Should PowerPivot be Named SimplePivot?
In traditional pivots, VLOOKUP is often a required step to prepare your data before pivoting it (combining multiple tables into one). So I remain hopeful that Bill is correct. Wouldn’t it be ironic if PowerPivot ended up being a Simpler way to create pivots, and didn’t just dramatically increase the power of pivots, but also broadly expanded the audience that even uses pivots?
That would rock. Is there anyone out there who can corroborate Bill’s theory? Were you put off by VLOOKUP before, and now use pivots thanks to PowerPivot? Let me know.
Anyway, time to do my part for VLOOKUP week – a full week of posts by Bill and the Excel community focused on that fearsome monster, VLOOKUP.
My take? With PowerPivot, you literally do not need VLOOKUP. Ever.
From the Archives #1: Relationships as Alternative to VLOOKUP
That Looks a Lot Easier than VLOOKUP… Because it IS
This article I wrote in CIMA Insight is probably the best intro I’ve written to relationships in PowerPivot:
From the Archives #2: Using =RELATED() to Inspect Your Data
This post comes from all the way back in 2009 and is an example of me using a new function, RELATED(), that does exactly the same thing as VLOOKUP, but only takes one argument:
Note that once you are done with your =RELATED calc columns for inspection purposes, in most cases it makes more sense to then delete those columns and just use the columns from the other table in your pivots.