Excel two way look up from a row and data range to give topmost column value

[RealGii]

Hi,

I am new to Excel Index/Match: Its a two way look up to index the column headings and using row match with a value which is part of the table.
The two look up criteria are 300 from column A, and 321 from the table to return corresponding Column headings in B1:G1.

So I tried the following: =INDEX(B1:G1,MATCH(300,A2:A7,.....What should I put here to Match 321 from the range B2:G7 to look upwards to row B1:G1 to get value in E1 (WK5)?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]WK1[/TD]
[TD]WK2[/TD]
[TD]WK3[/TD]
[TD]WK4[/TD]
[TD]WK5[/TD]
[TD]WK6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]546[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]368[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]278[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]225[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]189[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]164[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]150[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]606[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]407[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]308[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]249[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]209[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]181[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]200[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]666[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]447[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]338[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]273[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]229[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]198[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]250[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]725[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]487[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]368[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]297[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]249[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]215[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]300[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]785[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]527[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]398[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]321[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]269[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]232[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]350[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]845[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]567[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]428[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]345[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]289[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]249[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[RealGii]

Sorry WK4

 

[kvsrinivasamurthy]

Do you want to search 321 in 6th row only because 300 is in row 6 of column A.
If 321 is available in any other row that is to rejected.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1		WK1	WK2	WK3	WK4	WK5	WK6		300	WK4
2	100	546	368	278	225	189	164		321
3	150	606	407	308	249	209	181
4	200	666	447	338	273	229	198
5	250	725	487	368	297	249	215
6	300	785	527	398	321	269	232
7	350	845	567	428	345	289	249
sheet1

Cell Formulas
Range	Formula
J1	=INDEX(B1:G1,,MATCH(I2,INDEX(B2:G7,MATCH(I1,A2:A7,0),0),0))

 

[RealGii]

Do you want to search 321 in 6th row only because 300 is in row 6 of column A.
If 321 is available in any other row that is to rejected.

Thanks,

I think Fluff got the answer.

Cheers.

 

[RealGii]

Thank You Fluff,

I got it working with your formula.

Cheers

realGii

 

[Aladin Akyurek]

Is it guaranteed that there are no duplicates in A2:G7?

 

[RealGii]

Is it guaranteed that there are no duplicates in A2:G7?

There will be certainly duplicates in the range A2:G7 (rounded off). I made the match by adding -1 in the formula condition to look for the smallest greater than value, which is what I wanted.
INDEX(B1:G1,,MATCH(I2,INDEX(B2:G7,MATCH(I1,A2:A7,0),0),-1))

 

[Aladin Akyurek]

There will be certainly duplicates in the range A2:G7 (rounded off). I made the match by adding -1 in the formula condition to look for the smallest greater than value, which is what I wanted.
INDEX(B1:G1,,MATCH(I2,INDEX(B2:G7,MATCH(I1,A2:A7,0),0),-1))

Thanks for coming back. Since A2:A7 is in ascending order, the formula can be adapted to that...

=INDEX(B1:G1,,MATCH(I2,INDEX(B2:G7,MATCH(I1,A2:A7,1),0),-1))