Improve company productivity with a Business Account.Sign Up

x
?
Solved

nested VLookup

Posted on 2011-09-06
10
Medium Priority
?
327 Views
Last Modified: 2012-06-27
VLookup Data
I want to perfom a nested vlookup on the following data.  

For the value entered in  VL OFFER (1A) find the price for item (A) and put it in the VL Price Field.

Thanks.
0
Comment
Question by:vmccune
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36491917
This is an array formula, so use Ctrl+Shift+Enter to enter:

=INDEX($G$5:$G$10,MATCH(1,IF($E$5:$E$10=$C$2,IF($F$5:$F$10=B5,1)),0))
0
 

Author Comment

by:vmccune
ID: 36491973
Perfect!

How do I deal with not knowing what the last row of the offer, item, price array will be?
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36491994
Perhaps the quickest way is to use whole-columnreferences, but you could use a dynamic named range.

=INDEX($G:$G,MATCH(1,IF($E:$E=$C$2,IF($F:$F=B5,1)),0))
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 50
ID: 36492418
Hello,

you can also use a non-array formula like this, starting in C5 and copied down:

=Index(G:G,Match($C$2&B5,Index(E:E&F:F,0),0))

No need to specify the rows of the lookup range.

cheers, teylyn
0
 
LVL 35

Expert Comment

by:Rob Henson
ID: 36494165
As you are using excel 2007, you could also use

=SUMIFS($E:$E,$C$2,$F:$F,$B5,$G:$G)

Thanks
Rob H
0
 
LVL 50
ID: 36494298
Good one, Rob!

vmccune, note the difference between Rob's latest suggestion and the ones posted above.

Rob's formula in the previous post will find ALL rows where both "Offer" and "Item" meet the conditions, and add them up.

The first formula Rob posted, and the alternative I suggested, will find only the FIRST item where both conditions are true.

You will need to figure out which of these two different scenarios works best for your purposes, but I trust you should now have a few tools to choose from.

cheers, teylyn
0
 

Author Comment

by:vmccune
ID: 36494560
OK. One more rub.  I dont need to sum but I do want the data on a different sheet called "Data".  How do I modify Index(G:G,Match($C$2&B5,Index(E:E&F:F,0),0))

 to point to the data worksheet?

0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36494613
Hello,

define three range names

Item      =OFFSET(Offer,0,1)
Offer      =Sheet1!$E$5:INDEX(Sheet1!$E:$E,MATCH("zzzz",Sheet1!$E:$E,1))
Price      =OFFSET(Offer,0,2)

Assuming that the cells on Sheet2 are in the same position as you outlined above for Sheet1, the formula would then be

=INDEX(Price,MATCH($C$2&B5,INDEX(Offer&Item,0),0))

See attached file for a working example with source data on Sheet1 and parameters and results on Sheet2.

The file has been created in Excel 2003 and will work in 2007 in the same way.In Excel 2010 a solution may be simpler, since that version can refer to whole column ranges on another sheet where 2007 cannot.

cheers, teylyn
Book1.xls
0
 

Author Closing Comment

by:vmccune
ID: 36494673
Thanks!
0
 
LVL 35

Expert Comment

by:Rob Henson
ID: 36495143
I had assumed the use of SUMIF would be OK because I would have thought there would only be one occasion of both criteria being met therefore the sum would be a single value anyway.

Thanks
Rob H
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question