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)?

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

<tbody>
</tbody>

 

[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))