Solved

nested VLookup

Posted on 2011-09-06
10
298 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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

Expert Comment

by:Ingeborg Hawighorst
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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

756 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