Lookup using two arrays with range

JustinNED

New Member
Joined
Mar 21, 2016
Messages
2
Hi, I’m looking for a combined INDEX/MATCH method, using dependent range values. Let me further elaborate please.

I have table A containing road segments:

ABCD
1SegmentFromTo
2RoadA10100
3RoadA15001000
4RoadB220002500
5RoadB225003000
6RoadA125003000

<tbody>
</tbody>

And table B, providing information which segment belongs to which road part.

ABCD
1RoadFromToPart
2RoadA101000A1a
3RoadA110002000A1b
4RoadA120003000A1c
5RoadB201000B2a
6RoadB210002000B2b
7RoadB220003000B2c

<tbody>
</tbody>

Now, I need to lookup which road segment from table A belongs to which road part in table B. Basically, there are two lookups:
1) a comparison of the segments from table A with the roads from table B, and
2) The from/to from both tables A and B.

Ultimately, I want to have table C, which is table A + the partname from table B:

ABCD
1SegmentFromToPart
2RoadA10100A1a
3RoadA15001000A1a
4RoadB120002500B1c
5RoadB125003000B1c
6RoadA125003000A1c

<tbody>
</tbody>

In the example above, in table A, I can look up that the first segment, RoadA1, has actually three ranges:
0 until 1000 results in A1a
1000 until 2000 results in A1b
2000 until 3000 results in A1c

Now, the trickiest part is to combine a matching lookup, and from that lookup, a different sets of lookups apply.

So in short:

  • First look up the road to which the segment belongs to (using an index/match or vlookup)
  • Dependent on this look up, then check which range applies to the segment
  • Then determine the part name.

I suppose I could write hardcoded IF/AND-statements to replace table B, but this would be a tedious process, not to mention not really failsafe and it’s terribly sensitive to changes. For example (for table A, in column E, the IF/AND statements have changed to fixed values now), something similar as:

=IF(A2=”RoadA1”;IF(AND(D2>0;D2<=1000);”A1a”;
IF(AND(D2>1000;D2<=2000);”A1b”;
IF(AND(D2>2000;D2<=3000);”A1c”;”Not in range”))); IF(A2=”RoadB2”;IF(AND(D2>0;D2<=1000);”B2a”;
IF(AND(D2>1000;D2<=2000);”B2b”;
IF(AND(D2>2000;D2<=3000);”B2c”;”Not in range”)));”Unknown road”) etc. etc. etc.

There has to be a better way to do this, and I have the feeling I just need to nest multiple INDEX/MATCH, but I'm not entirely sure. Suggestions anyone?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,330
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum!

I think I understood what you're after. Try this and let us know if we're on the right track.

ABCD
1Table A
2SegmentFromTo
3RoadA10100
4RoadA15001000
5RoadB220002500
6RoadB225003000
7RoadA125003000
8
9Table B (lookup for Part)
10RoadFromToPart
11RoadA101000A1a
12RoadA110002000A1b
13RoadA120003000A1c
14RoadB201000B2a
15RoadB210002000B2b
16RoadB220003000B2c
17
18Table C (results)
19SegmentFromTopart
20RoadA10100A1a
21RoadA15001000A1a
22RoadB220002500B2c
23RoadB225003000B2c
24RoadA125003000A1c

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet20

Array Formulas
CellFormula
D20{=INDEX($D$11:$D$16,MATCH(1,(A20=$A$11:$A$16)*(B20>=$B$11:$B$16)*(C20<=$C$11:$C$16),0))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

JustinNED

New Member
Joined
Mar 21, 2016
Messages
2
Hi, I was able to reproduce your solution and it worked perfectly fine! Thanks a lot!!
I was a bit confused how I could implement a multi-dependent value, but I knew INDEX/MATCH could fix the job.

I tried a bit around with MS Access as well, although that required a different approach.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,330
Office Version
  1. 365
Platform
  1. Windows
You're welcome. I'm glad you can move forward with your project now.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,502
Members
414,073
Latest member
Contilly

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
Top