VLOOKUP Week: Who Needs VLOOKUP Anymore?


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 weeka 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.

Full post here.

10 Responses to VLOOKUP Week: Who Needs VLOOKUP Anymore?

  1. Dave says:

    In my day we walked up hill to school….both ways! And we also used VLOOKUP() and liked it! I think Powerpivot is a much better approach, but how will we make the noobs suffer like we did?

  2. Dave says:

    One other useful vlookup() feature is its range look up. It’s a great way to “bucketize” numerical data

  3. greg says:

    also I dont think I can use the related functions as I want to “bucketize” by a key measure from the pivot data

  4. Pau says:

    I have a question about the best way to add lookup tables to PowerPivots models. I want to achieve an easy maintenance for users and a good performance for the reports. Excel linked tables? Import from Access?

  5. john says:

    Hi! How do you do the search in the opposite direction of the relation? Thanks, best regards

    • Avichal Singh says:

      John, to go in the opposite direction (from the one side to many side) you can use RELATEDTABLE(). You would of course get multiple results as expected, so you would need to aggregate them somehow (Count, Sum, Avg…)

Leave a Comment or Question