Using look-up table

Try this:

=INDEX($B$5:$D$5,MATCH(I5,INDIRECT("B"&MATCH(H5,$A$1:$A$3,0)&":D"&MATCH(H5,$A$1:$A$3,0)),0))

and copy down.

Hope this helps.

Pete

Of course it works - I tested it out first !!

Pete

I'm still a dumb, Pete... Help me with this please. I've modified to use in another cell, but dunno what the error is..

You've moved the table down from row 1 to row 7 , so you need to add 6 on. Try this:

=INDEX($L$11:$N$11,MATCH(C6,INDIRECT("L"&MATCH(B6,$J$7:$J$9,0)+6&":N"&MATCH(B6,$J$7:$J$9,0)+6),0))

Hope this helps.

Pete

Got it Pete... Thanks... Forgot that the match function returns the column number and when it's concatenated, it will reference K1 and not K6. So added +6 as follows:
=INDEX($K$11:$M$11,MATCH(C6,INDIRECT("K"&MATCH(B6,$J$7:$J$9,0)+6&":M"&MATCH(B6,$J$7:$J$9,0)+6),0))

is there any other alternative?

I have data in K7:M11 in three sheets namely s_core1,s_core2,s_core3.
The search keys are in A6:C6, A7:B7 and so on.
If A6 is core1, the data in sheet s_core1 should be used to lookup.
If it is core2, the data in sheet s_core2 should be used to lookup.

I don't know the error in the following formula used:

=INDEX(INDIRECT("s_"&A6&"!"&"$K$11:$M$11"),MATCH(C6,INDIRECT("K"&MATCH(B6,INDIRECT("s_"&A6&"!$J$7:$J$9"),0)+6&":M"&MATCH(B6,INDIRECT("s_"&A6&"!$J$7:$J$9"),0)+6),0))

Please help me with that.

Thanks in advance.

You aren't referring to the sheet in the second INDIRECT function - add the bolded part in place of "K"

=INDEX(INDIRECT("s_"&A6&"!"&"$K$11:$M$11"),MATCH(C6,INDIRECT("s_"&A6&"!K"&MATCH(B6,INDIRECT("s_"&A6&"!$J$7:$J$9"),0)+6&":M"&MATCH(B6,INDIRECT("s_"&A6&"!$J$7:$J$9"),0)+6),0))


Thanks barry houdini...