**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 ** Power**Pivot ended up being a

**way to create pivots, and didn’t just dramatically increase the power of pivots, but also broadly expanded the audience that even uses pivots?**

*Simpler*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.

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?

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

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

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

Hi,

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?

Thanks!

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

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

http://www.powerpivotpro.com/2011/11/why-powerpivot-is-better-fed-from-a-database-pt2/

Excellent! Thanks!

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

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…)