Lookup using two arrays with range

[JustinNED]

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:

	A	B	C	D
1	Segment	From	To
2	RoadA1	0	100
3	RoadA1	500	1000
4	RoadB2	2000	2500
5	RoadB2	2500	3000
6	RoadA1	2500	3000

<tbody>
</tbody>

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

	A	B	C	D
1	Road	From	To	Part
2	RoadA1	0	1000	A1a
3	RoadA1	1000	2000	A1b
4	RoadA1	2000	3000	A1c
5	RoadB2	0	1000	B2a
6	RoadB2	1000	2000	B2b
7	RoadB2	2000	3000	B2c

<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:

	A	B	C	D
1	Segment	From	To	Part
2	RoadA1	0	100	A1a
3	RoadA1	500	1000	A1a
4	RoadB1	2000	2500	B1c
5	RoadB1	2500	3000	B1c
6	RoadA1	2500	3000	A1c

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

 

[DRSteele]

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.

	A	B	C	D
1	Table A
2	Segment	From	To
3	RoadA1	0	100
4	RoadA1	500	1000
5	RoadB2	2000	2500
6	RoadB2	2500	3000
7	RoadA1	2500	3000
8
9	Table B (lookup for Part)
10	Road	From	To	Part
11	RoadA1	0	1000	A1a
12	RoadA1	1000	2000	A1b
13	RoadA1	2000	3000	A1c
14	RoadB2	0	1000	B2a
15	RoadB2	1000	2000	B2b
16	RoadB2	2000	3000	B2c
17
18	Table C (results)
19	Segment	From	To	part
20	RoadA1	0	100	A1a
21	RoadA1	500	1000	A1a
22	RoadB2	2000	2500	B2c
23	RoadB2	2500	3000	B2c
24	RoadA1	2500	3000	A1c

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

Sheet20​

Array Formulas Cell Formula 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]

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]

You're welcome. I'm glad you can move forward with your project now.