“Vlookuping” Against More Than One Column Value – Magically!

The challenge: 

You want to use a vlookup, but you have two columns you want to check against.  Such as:

  A B C D
1 Data Day Resource Hours
2   3/17/14 Duke 4
3   3/18/14 Duke 8
4   3/17/14 Edgar 10

And you have another page (sheet 2) where you want this to show up:

  A B C D
1 Data Day Resource Hours
2   3/17/14 Duke  
3   3/18/14 Duke  

In this scenario, you want Duke’s allocations for 3/17/14 and 3/18/14, but not Edgar’s.  A normal vlookup will only give you the first “Duke” entry, not both, because it’s rude.  (there are lots of reasons you’d want to do this, I use it in several “comparison” type workbooks that are looking for changes in data.  If you don’t have an application for this, try to think of one, because it’s cool.)

Before you start in, I know that there are other ways to do this, including  using match and ugly things like that.  I like this one better, sue me.

The Solution:

Create another Column to the LEFT of the day column, as shown.  Now we’re going to combine values from the two cells that we want check against  using this little doo-hicky:

=B2&C2

The output will look like this:

3/17/14Duke

3/18/14Duke

And so forth.  Put that on both of your sheets. Drag down forever. That’s the cell you’re going to reference for your vlookup in Column D of the second sheet: 

It will look something like this:

=IF(B2<>””,VLOOKUP(B3,’Sheet1′!B:D,3,FALSE),””)

And you can hide that ugly column and it will all appear to work like magic!

Now it’s going to pull those happy little hours in for 3/17/14Duke and 3/18/14Duke and so forth. 

Extra Credit:

You can do this in a pivot table pulling data from Project Server (or any SQL Database) too!

Just stick this into your select statement:

Day+Resource as data

Etc. and so forth.  You could have two pivot tables side by side and only refresh one to compare data!  The uses are technically not endless, but vast!  Now go have fun with your magic tricks!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s