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.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 10 Comments

  1. Dave

    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

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

    1. greg

      how do i “bucketize” data with power pivot – i really want to throw vlookup in the trash bin

  3. greg

    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

    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?

    1. powerpivotpro

      Of the two options presented I would much prefer to import from Access.

    2. powerpivotpro

      Here’s a post (part 2 in a series) that explains why I prefer db’s.


      1. Pau

        Excellent! Thanks!

  5. john

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

    1. Avichal Singh

      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