Solved

nested VLookup

Posted on 2011-09-06
10
305 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

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

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

738 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