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

R

RealGii

New Member
Joined
Jan 1, 2019
Messages
6
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]
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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.
 
Upvote 0
How about


Excel 2013/2016
ABCDEFGHIJ
1WK1WK2WK3WK4WK5WK6300WK4
2100546368278225189164321
3150606407308249209181
4200666447338273229198
5250725487368297249215
6300785527398321269232
7350845567428345289249
sheet1
Cell Formulas
RangeFormula
J1=INDEX(B1:G1,,MATCH(I2,INDEX(B2:G7,MATCH(I1,A2:A7,0),0),0))
 
Upvote 0
Aladin Akyurek said:
Is it guaranteed that there are no duplicates in A2:G7?
Click to expand...


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))
 
Upvote 0
RealGii said:
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))
Click to expand...

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))
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question Question
Looking up a value on a table based on both column headings and rows
Replies
3
Views
408
Fluff
Fluff
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
114
sijpie
sijpie
G
  • Solved
Loop with column header match and two conditions paste
Replies
3
Views
619
Legacy 143009
L
M
  • Solved
Intersection of column and row based on value match
Replies
5
Views
478
Fluff
Fluff
A
  • Solved
Finding a unique value in a range and returning the value in another column of the same row, without offset / slow formulae.
Replies
8
Views
2K
AaronPMH
A

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top