# 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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### RJB85

##### New Member
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>

#### gaz_chops

##### Well-known Member
Should "B" be "Y"? 1.7 is between 0 & 2.

#### gaz_chops

##### Well-known Member
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:

#### RJB85

##### New Member
That only worked for the first 'Code A' on the large dataset I have.

#### gaz_chops

##### Well-known Member
Seems ok for me!

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

#### gaz_chops

##### Well-known Member
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]``````

#### RJB85

##### New Member
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!

#### gaz_chops

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

#### RJB85

##### New Member
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>