MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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