[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

nested VLookup

Posted on 2011-09-06
10
Medium Priority
?
323 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

656 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