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.
<tbody>
</tbody>
<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
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