# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
0
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
0
Author Commented:
I read an interesting option ...

article

0
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
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
0
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
0
Author Commented:
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.