Lookup between two values and matching criteria

RJB85

New Member
Joined
Nov 27, 2018
Messages
9
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 1StartFinishCode 2
A01X
A12X
A23Y
B02Y
B24X

<tbody>
</tbody>

Code 1TimeMatch Code 2
A0.5X
B1.7X

<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
 

RJB85

New Member
Joined
Nov 27, 2018
Messages
9
Noticed a typo. Table should be as below

Code 1StartFinishCode 2
A01X
A12X
A23Y
B02Y
B24X

<tbody>
</tbody>


Code 1TimeMatch Code 2
A0.5X
B1.7Y

<tbody>
</tbody>
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
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
Joined
Nov 27, 2018
Messages
9
That only worked for the first 'Code A' on the large dataset I have.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
Seems ok for me!

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

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
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
Joined
Nov 27, 2018
Messages
9
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
Joined
Apr 29, 2003
Messages
5,890
Post a selection of data from just before it goes awry to a little after, e.g. rows 15 to 40
 

RJB85

New Member
Joined
Nov 27, 2018
Messages
9
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 1StartFinishCode 2Row
A0.000.90X1
A0.902.20X2
A2.203.70X3
A3.705.20X4
A5.206.70X5
A6.708.20X6
A8.209.70X7
A9.7011.20X8
A11.2012.70Y9
A12.7014.20Y10
A14.2015.70Y11
A15.7017.20Y12
A17.2018.70Y13
A18.7020.20Y14

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


Code 1TimeCode 2Row Ref
A5.60X31
A7.75X42
A11.20Y9
A19.65Y14
A20.60Y15
A21.60Y15
A23.70Y18

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

Forum statistics

Threads
1,077,851
Messages
5,336,750
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top