Solved

nested VLookup

Posted on 2011-09-06
10
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 33

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 500 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 33

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

690 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