You want to use a vlookup, but you have two columns you want to check against. Such as:
And you have another page (sheet 2) where you want this to show up:
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.
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:
The output will look like this:
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:
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.
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!