INDEX/MATCH (or XLOOKUP?)

mpleam

New Member
Joined
Jun 6, 2023
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I'm really struggling with this. I have a table with my data:

-31.01.42.02.84.0
-21.01.42.02.84.05.6
-11.01.42.02.84.05.68.0
01.01.42.02.84.05.68.011
11.01.42.02.84.05.68.01116
21.01.42.02.84.05.68.0111622
31.01.42.02.84.05.68.011162232
41.01.42.02.84.05.68.011162232
51.01.42.02.84.05.68.011162232
61.01.42.02.84.05.68.011162232
71.01.42.02.84.05.68.011162232
81.01.42.02.84.05.68.011162232
91.01.42.02.84.05.68.011162232
101.42.02.84.05.68.011162232
112.02.84.05.68.011162232
122.84.05.68.011162232
134.05.68.011162232
145.68.011162232
158.011162232
1611162232
17162232
182232


I need to find the first value in the range B14:R35 that corresponds to the selected search value in Column A. So for example, if I select '2' from column A, the first value should be '1' from column I. However, I have tried varying combinations of INDEX and MATCH (also tried XLOOKUP, and that confused me even more, but cannot get it to work! Can anyone assist? I'm not a great user of Excel these days, so haven't a clue how to achieve this.

Thank you

Mark
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
How about
Fluff.xlsm
ABCDEFGHIJKLMNOPQR
1
2
321
4
5
6
7
8
9
10
11
12
13
14-311.422.84
15-211.422.845.6
16-111.422.845.68
17011.422.845.6811
18111.422.845.681116
19211.422.845.68111622
20311.422.845.6811162232
21411.422.845.6811162232
22511.422.845.6811162232
23611.422.845.6811162232
24711.422.845.6811162232
25811.422.845.6811162232
26911.422.845.6811162232
27101.422.845.6811162232
281122.845.6811162232
29122.845.6811162232
301345.6811162232
31145.6811162232
3215811162232
331611162232
3417162232
35182232
Sheet6
Cell Formulas
RangeFormula
B3B3=LET(x,XLOOKUP(A3,A14:A35,B14:R35),TAKE(FILTER(x,x<>""),,1))
 
Upvote 0
Solution
Brilliant! Thank you so much, your help is much appreciated.

Mark
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
(Edited)
Your data has a pattern. Using col A and B only, try:
Excel Formula:
=MAX(XLOOKUP(A3,$A$14:$A$35,$B$14:$B$35),MIN($B$14:$B$35))
 
Last edited:
Upvote 0
Using the entire range:
Excel Formula:
=MIN(XLOOKUP(A3,$A$14:$A$35,$B$14:$R$35))
 
Upvote 0
Hi again,

Is it possible to modify this formula:

=LET(x,XLOOKUP(A3,A14:A35,B14:R35),TAKE(FILTER(x,x<>""),,1))

So that I can retrieve a value from my (now modified) table?

512001/2560001/1280001/640001/320001/160001/80001/40001/20001/10001/5001/2501/1251/601/301/151/81/4
256001/1280001/640001/320001/160001/80001/40001/20001/10001/5001/2501/1251/601/301/151/81/41/2
128001/640001/320001/160001/80001/40001/20001/10001/5001/2501/1251/601/301/151/81/41/21
64001/320001/160001/80001/40001/20001/10001/5001/2501/1251/601/301/151/81/41/212
32001/160001/80001/40001/20001/10001/5001/2501/1251/601/301/151/81/41/2124
16001/80001/40001/20001/10001/5001/2501/1251/601/301/151/81/41/21248
8001/40001/20001/10001/5001/2501/1251/601/301/151/81/41/2124816
4001/20001/10001/5001/2501/1251/601/301/151/81/41/212481632
2001/10001/5001/2501/1251/601/301/151/81/41/21248163264
1001/5001/2501/1251/601/301/151/81/41/21248163264128
-31.01.42.02.84.0
-21.01.42.02.84.05.6
-11.01.42.02.84.05.68.0
01.01.42.02.84.05.68.011
11.01.42.02.84.05.68.01116
21.01.42.02.84.05.68.0111622
31.01.42.02.84.05.68.011162232
41.01.42.02.84.05.68.01116223264
51.01.42.02.84.05.68.01116223264
61.01.42.02.84.05.68.01116223264
71.01.42.02.84.05.68.01116223264
81.01.42.02.84.05.68.01116223264
91.01.42.02.84.05.68.01116223264
101.42.02.84.05.68.01116223264
112.02.84.05.68.01116223264
122.84.05.68.01116223264
134.05.68.01116223264
145.68.01116223264
158.01116223264
161116223264
1716223264
18223264


So, if I select the value in cell A17 (0), it returns not only the first value in the row (`1.0`)), but also the value from the corresponding cell in the top half of the table (in this case, K12, with a value of `1`) - hope that makes sense...
How about
Fluff.xlsm
ABCDEFGHIJKLMNOPQR
1
2
321
4
5
6
7
8
9
10
11
12
13
14-311.422.84
15-211.422.845.6
16-111.422.845.68
17011.422.845.6811
18111.422.845.681116
19211.422.845.68111622
20311.422.845.6811162232
21411.422.845.6811162232
22511.422.845.6811162232
23611.422.845.6811162232
24711.422.845.6811162232
25811.422.845.6811162232
26911.422.845.6811162232
27101.422.845.6811162232
281122.845.6811162232
29122.845.6811162232
301345.6811162232
31145.6811162232
3215811162232
331611162232
3417162232
35182232
Sheet6
Cell Formulas
RangeFormula
B3B3=LET(x,XLOOKUP(A3,A14:A35,B14:R35),TAKE(FILTER(x,x<>""),,1))
 
Upvote 0
Why should it return the value from K12?
 
Upvote 0

Forum statistics

Threads
1,225,732
Messages
6,186,704
Members
453,369
Latest member
positivemind

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