Lookup between two values and matching criteria

R

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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Code 1[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Code 2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Code 1[/TD]
[TD]Time[/TD]
[TD]Match Code 2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0.5[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1.7[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Noticed a typo. Table should be as below

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Code 1[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Code 2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Code 1[/TD]
[TD]Time[/TD]
[TD]Match Code 2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0.5[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1.7[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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:
Upvote 0
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]
 
Upvote 0
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!
 
Upvote 0
Post a selection of data from just before it goes awry to a little after, e.g. rows 15 to 40
 
Upvote 0
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.

[TABLE="width: 335"]
<colgroup><col width="67" span="5" style="width:50pt"> </colgroup><tbody>[TR]
[TD="width: 67"]Code 1[/TD]
[TD="width: 67"]Start[/TD]
[TD="width: 67"]Finish[/TD]
[TD="width: 67"]Code 2[/TD]
[TD="width: 67"]Row[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]0.00[/TD]
[TD="class: xl63, align: right"]0.90[/TD]
[TD]X[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]0.90[/TD]
[TD="class: xl63, align: right"]2.20[/TD]
[TD]X[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]2.20[/TD]
[TD="class: xl63, align: right"]3.70[/TD]
[TD]X[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]3.70[/TD]
[TD="class: xl63, align: right"]5.20[/TD]
[TD]X[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]5.20[/TD]
[TD="class: xl63, align: right"]6.70[/TD]
[TD]X[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]6.70[/TD]
[TD="class: xl63, align: right"]8.20[/TD]
[TD]X[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]8.20[/TD]
[TD="class: xl63, align: right"]9.70[/TD]
[TD]X[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]9.70[/TD]
[TD="class: xl63, align: right"]11.20[/TD]
[TD]X[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]11.20[/TD]
[TD="class: xl63, align: right"]12.70[/TD]
[TD]Y[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]12.70[/TD]
[TD="class: xl63, align: right"]14.20[/TD]
[TD]Y[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]14.20[/TD]
[TD="class: xl63, align: right"]15.70[/TD]
[TD]Y[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]15.70[/TD]
[TD="class: xl63, align: right"]17.20[/TD]
[TD]Y[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]17.20[/TD]
[TD="class: xl63, align: right"]18.70[/TD]
[TD]Y[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]18.70[/TD]
[TD="class: xl63, align: right"]20.20[/TD]
[TD]Y[/TD]
[TD="align: right"]14
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 268"]
<colgroup><col width="67" span="4" style="width:50pt"> </colgroup><tbody>[TR]
[TD="width: 67"]Code 1[/TD]
[TD="width: 67"]Time[/TD]
[TD="width: 67"]Code 2[/TD]
[TD="width: 67"]Row Ref[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65, align: right"]5.60[/TD]
[TD]X[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65, align: right"]7.75[/TD]
[TD]X[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65, align: right"]11.20[/TD]
[TD]Y[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65, align: right"]19.65[/TD]
[TD]Y[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65, align: right"]20.60[/TD]
[TD]Y[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65, align: right"]21.60[/TD]
[TD]Y[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65, align: right"]23.70[/TD]
[TD]Y[/TD]
[TD="align: right"]18[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Matching multiple criteria that includes comma separated value
Replies
2
Views
100
Rokcmd
R
megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
286
etaf
etaf
B
  • Solved
How to return closest value based on two criteria?
Replies
6
Views
320
Peter_SSs
Peter_SSs
L
  • Question Question
VBA Match function for two criteria / columns
Replies
9
Views
209
Domenic
D
C
  • Solved
Lookup and Match Formula
Replies
5
Views
67
ThatOneDude
T

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

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
Back
Top