VLOOKUP function with different lookup values

joeserrone
joeserrone used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013

Commented:
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

Author

Commented:
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

Author

Commented:
I read an interesting option ...

article

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Most Valuable Expert 2013
Commented:
Yes, creating a helper column is one way....but you don't need to do that. This revised version looks up the Site and the mesaure

=LOOKUP(2,1/($A$1=Extract!$A$1:$A$100)/($A3=Extract!$B$1:$B$100),Extract!C$1:C$100)

That's the B3 formula, change $A$1 to $A$9 again for the next block and $A3 would be $A11, see attached.

That copes with 100 rows of data, adjust as required

regards, barry
26917591v2.xls
Most Valuable Expert 2012
Top Expert 2012
Commented:
You can use:

=SUMPRODUCT((Extract!$A:$A=$A$1)*(Extract!$B:$B=$A3)*(Extract!C:C)) for the first block, and

=SUMPRODUCT((Extract!$A:$A=$A$9)*(Extract!$B:$B=$A11)*(Extract!C:C)) for the second block.

THIS assumes, months/dates are all aligned accordingly.

If the dates are not all aligned, you can use:

=SUMPRODUCT((Extract!$A:$A=$A$1)*(Extract!$B:$B=$A3)*(Extract!$C$1:$N$1=B$2)*(Extract!$C:$N)) for the first block, and

=SUMPRODUCT((Extract!$A:$A=$A$9)*(Extract!$B:$B=$A11)*(Extract!$C$1:$N$1=B$2)*(Extract!$C:$N)) for the second block

Note - you can pare down the formulas by using (example)  $A2:$A$1000 RATHER THAN $A:$A when you know what the max size your dataset might be.
see attached,

Dave

For the second
Samle1-r1.xls
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
Thanks for all your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial