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 [link removed due to 404] 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.

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

What Is the Microsoft Fabric Free License?

The Microsoft Fabric free license represents a significant offering regarding data management

Read the Blog

What Is New in Microsoft Fabric?

Microsoft has once again made headlines with the introduction of Microsoft Fabric,

Read the Blog

What Is the Difference Between Microsoft Fabric and Databricks?

Businesses seeking to make informed data decisions must appreciate the distinctions between

Read the Blog

Power BI Usage Metric Reporting: A Guide to Game-Changing Insights

If you build it, they will come.  That movie line doesn’t just

Read the Blog