# Lookup between two values and matching criteria

#### RJB85

##### New Member
I am looking for a formula that will match data between two values and also match another criteria in the table.

As an example I have the two tables below and want to display the 'Code 2' answer in 'Match Code 2'. I have input the correct answers I want it to display below. It's looking for Code 2 where the Time is between the start and finish for the relevant Code 1.
 Code 1 Start Finish Code 2 A 0 1 X A 1 2 X A 2 3 Y B 0 2 Y B 2 4 X

<tbody>
</tbody>

 Code 1 Time Match Code 2 A 0.5 X B 1.7 X

<tbody>
</tbody>

Currently I only have a formula that looks between the values but want to update it so it only looks between the two values of the relevant Code 1 value.

The formula I am using to look between the Start and Finish values to match Code 2 is:
=LOOKUP(2,1/(\$B\$2:\$B\$6<=B9)/(\$C\$2:\$C\$6>=B9),\$D\$2:\$D\$6)

I also want the result to be dependant on Code 1 as it currently ignores it. Any ideas?

Thanks

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Noticed a typo. Table should be as below

 Code 1 Start Finish Code 2 A 0 1 X A 1 2 X A 2 3 Y B 0 2 Y B 2 4 X

<tbody>
</tbody>

 Code 1 Time Match Code 2 A 0.5 X B 1.7 Y

<tbody>
</tbody>

Should "B" be "Y"? 1.7 is between 0 & 2.

Then try

=INDEX(\$D\$2:\$D\$6,MATCH(A10&B10,\$A\$2:\$A\$6&\$B\$2:\$B\$6,1))

Enter as an array, Ctrl Shift & Enter

Last edited:
That only worked for the first 'Code A' on the large dataset I have.

Seems ok for me!

Did you enter as an array? What results, if any did you get?

Code:
``````[TABLE="width: 348"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Code 1[/TD]
[TD="class: xl65, width: 87"]Start[/TD]
[TD="class: xl65, width: 87"]Finish[/TD]
[TD="class: xl65, width: 87"]Code 2[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"]X[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"]X[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65"]Y[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"]Y[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65"]X[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]Code 1[/TD]
[TD="class: xl65"]Time[/TD]
[TD="class: xl65, colspan: 2"]Match Code 2[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]0.5[/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl64, bgcolor: yellow"]X[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]1.7[/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl64, bgcolor: yellow"]Y[/TD]
[/TR]
</tbody>[/TABLE]``````

Yep I entered it as an array.

The dataset Im using it for has around 150 rows. I cant find an option to attached it so you can see but when Code 1 turns to B, about 26 rows down, it starts giving the incorrect answer. I could post the full table here but it might be a bit too much!

Post a selection of data from just before it goes awry to a little after, e.g. rows 15 to 40

I've added a row number column and used the formula to identify which row its looking at (moved the array to row E instead of D). This is what I get for the first few.

 Code 1 Start Finish Code 2 Row A 0.00 0.90 X 1 A 0.90 2.20 X 2 A 2.20 3.70 X 3 A 3.70 5.20 X 4 A 5.20 6.70 X 5 A 6.70 8.20 X 6 A 8.20 9.70 X 7 A 9.70 11.20 X 8 A 11.20 12.70 Y 9 A 12.70 14.20 Y 10 A 14.20 15.70 Y 11 A 15.70 17.20 Y 12 A 17.20 18.70 Y 13 A 18.70 20.20 Y 14

<colgroup><col width="67" span="5" style="width:50pt"> </colgroup><tbody>
</tbody>

 Code 1 Time Code 2 Row Ref A 5.60 X 31 A 7.75 X 42 A 11.20 Y 9 A 19.65 Y 14 A 20.60 Y 15 A 21.60 Y 15 A 23.70 Y 18

<colgroup><col width="67" span="4" style="width:50pt"> </colgroup><tbody>
</tbody>

Replies
3
Views
271
Replies
0
Views
420
Replies
7
Views
399
Replies
3
Views
209
Replies
3
Views
292

1,211,679
Messages
6,103,245
Members
447,849
Latest member
4chase

### 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.

### Which adblocker are you using?

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

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