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,082,360
Messages
5,364,920
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top