Nested Lookups
Posted by JohnS on October 21, 2001 11:17 AM
I have a 3-column table that contains X-Y-Z data that I need to identify a value in X, then select from a set of values in Y (a single X value has multiple Y values), to yield the desired value in Z. This can be coded in VBA using arrays, but I was wondering whether anyone has any luck using any kind of hard-wired functions in Excel for this nested lookup.
I haven't had much luck with INDEX or MATCH.
Thanks.
Posted by Aladin Akyurek on October 21, 2001 11:41 AM
Care to post 5 rows of data, along with lookup-value(s) and expected result?
==========
Posted by JohnS on October 22, 2001 3:49 AM
Sure thing ...
X Y Z
100 50 .12
100 100 .65
100 200 .05
200 50 .18
200 100 .8
200 200 .65
User "rolls" 200 for X, 100 for Y to get desired answer of 0.8.
Any thoughts on this ...?
Posted by Aladin Akyurek on October 22, 2001 4:20 AM
Assume your sample data to be in A2:C7 excluding labels X, Y, Z.
What the user "rolls" in D2 (for X) and D3 for Y.
In E2 enter: =INDEX(C2:C7,SUMPRODUCT(MATCH(D2&D3,A2:A7&B2:B7,0)))
Aladin
===========
Posted by JohnS on October 25, 2001 2:32 PM
Hey thanks -- that works great!
John
