multiple lookup

I have two sheets, 1st sheet contains data and second sheet result.

in second sheet I want the data for cell C2 to C18 appear based on C1 where C1 is a list.

In short if sheet RESULT C1=M then sheet RESULT C2=DATA!P2

Excel sheet is attached

thanks

Try ....

=INDEX(DATA!$D$2:$P$18,MATCH($B2,DATA!$C$2:$C$18,0),MATCH(RESULT!C$1,DATA!$D$1:$P$1,0))

Hi Marvin,
if I select A in sheet Result!C1, it returns AAA in front of AAA whereas it should return 0.27

Originally Posted by MarvinP

Hi siddiqsh and welcome to the forum,

John's answer is perfect but you can also use a match function in a VLookup formula like

=VLOOKUP(B2,DATA!$C$2:$P$18,MATCH(RESULT!$C$1,DATA!$D$1:$P$1,0))

See both formulas in the attached.

thanks, now it is perfect

Originally Posted by MarvinP

Sorry I was one off in my columns... Try this instead

=VLOOKUP(B2,DATA!$C$2:$P$18,1+MATCH(RESULT!$C$1,DATA!$D$1:$P$1,0))