vmccune
asked on
nested VLookup
ASKER
Perfect!
How do I deal with not knowing what the last row of the offer, item, price array will be?
How do I deal with not knowing what the last row of the offer, item, price array will be?
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,I F($F:$F=B5 ,1)),0))
=INDEX($G:$G,MATCH(1,IF($E
Hello,
you can also use a non-array formula like this, starting in C5 and copied down:
=Index(G:G,Match($C$2&B5,I ndex(E:E&F :F,0),0))
No need to specify the rows of the lookup range.
cheers, teylyn
you can also use a non-array formula like this, starting in C5 and copied down:
=Index(G:G,Match($C$2&B5,I
No need to specify the rows of the lookup range.
cheers, teylyn
As you are using excel 2007, you could also use
=SUMIFS($E:$E,$C$2,$F:$F,$ B5,$G:$G)
Thanks
Rob H
=SUMIFS($E:$E,$C$2,$F:$F,$
Thanks
Rob H
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
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
ASKER
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,In dex(E:E&F: F,0),0))
to point to the data worksheet?
to point to the data worksheet?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
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
Thanks
Rob H
=INDEX($G$5:$G$10,MATCH(1,