asked on

VLOOKUP function with different lookup values

I'm trying to streamline the process of manual keying each month data extracted from a Forecasting tool and automatically populate with correct values in an Excel Worksheet. I'm attaching an example of what I'm trying to accomplish. Every month I extract a set records and bring it in the worksheet called "Extract" I then want the "Update Model" worksheet to update with the correct value based on the site, measure for each month.
I need a Vlookup or something similar that will look at the site and the measure and then place it in the right section of the worksheet... I'm not sure how to get that going with different elements..

Clearly the attached version is very simplistic we actually have many sites with many more measures.
Samle1.xls

barry houdini

Hello joeserrone

try this formula in Update Model B3 copied across and down

=INDEX(Extract!C:C,MATCH($A$1,Extract!$A:$A,0)+ROWS(C$3:C3)-1)

for the next block you can use exactly the same formula except $A$1 becomes $A$9

This works on the assumption that the months and the 4 categories will be in the same order in both sets of data, see attached

regards, barry
26917591.xls

joeserrone

ASKER

Thank you Barry, unfortunately I can't guarantee that the measure is in the same order from the extract worksheet, sometimes the extract will show:
Incoming Volume
Outgoing Volume
Research Volume
Process Volume

Sometimes it can be reversed based if that is there or not. That's why I was thinking about a VLOOKUP

joeserrone

ASKER

I read an interesting option ...

article

ASKER CERTIFIED SOLUTION

barry houdini

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

dlmille

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

dlmille

I used to be a big fan of helper columns - and if you have the time to set them up, etc., they are probably faster in processing via index/match/vlookup than the wizardry we've provided. However, these past few months, I've begun to lean on lookup/sumproduct or even sumifs (if you have Excel 2007+, sumifs is better at self-documenting). But, they can be hard to understand (at least at first), whereas the helper column makes things a bit easier to understand.

For completeness, since you raised it, here's the same solution (again, date order independent) using helper columns in both the extract and report sheet, and as a result, the formula is written once, and copied for all.

with helper columns in column A on both sheets, the formula becomes:

=VLOOKUP($A3,Extract!$A:$O,MATCH(C$2,Extract!$1:$1,0),0)

Pasted in the first intersection of the data report, then copied wherever you have measure/date intersection. Please see attached...

Again, I've simplified by not limiting # rows/columns and you can pare that being more explicit if performance suffers (e.g., $A:$O would be $A$1:$O$1000)

Dave
Samle1-r2.xls

joeserrone

ASKER

Thanks for all your help