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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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
6,468
Platform
  1. MacOS
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

ADVERTISEMENT

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

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
6,468
Platform
  1. MacOS

ADVERTISEMENT

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
6,468
Platform
  1. MacOS
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

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