Formula to look up value based on date range

[RetiredWizard]

Hello,
First post, so I appreciate any help. I am trying to build a formula that will lookup a value and date and return the specific value in the row where the date is within that specific range.

For instance, the formula should lookup the "Key" and "Date Due" from Table 2, and return the appropriate "R Number" where the "Date Due" falls within the given date ranges in Table 1.

Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[TD]Range Start[/TD]
[TD]Range End[/TD]
[TD]R Number[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]2/4/2019[/TD]
[TD]2/10/2019[/TD]
[TD]TL456U[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]2/11/2019[/TD]
[TD]2/17/2019[/TD]
[TD]HU756P[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]2/18/2019[/TD]
[TD]2/24/2019[/TD]
[TD]MW734Q[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[TD]ID Ship[/TD]
[TD]Date Due[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]123[/TD]
[TD]2/12/2019[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]124[/TD]
[TD]2/22/2019[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]125[/TD]
[TD]2/7/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[RetiredWizard]

Hello,
First post, so I appreciate any help. I am trying to build a formula that will lookup a value and date and return the specific value in the row where the date is within that specific range.

For instance, the formula should lookup the "Key" and "Date Due" from Table 2, and return the appropriate "R Number" where the "Date Due" falls within the given date ranges in Table 1.

Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[TD]Range Start[/TD]
[TD]Range End[/TD]
[TD]R Number[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]2/4/2019[/TD]
[TD]2/10/2019[/TD]
[TD]TL456U[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]2/11/2019[/TD]
[TD]2/17/2019[/TD]
[TD]HU756P[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]2/18/2019[/TD]
[TD]2/24/2019[/TD]
[TD]MW734Q[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[TD]ID Ship[/TD]
[TD]Date Due[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]123[/TD]
[TD]2/12/2019[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]124[/TD]
[TD]2/22/2019[/TD]
[/TR]
[TR]
[TD]1741002CP2334[/TD]
[TD]125[/TD]
[TD]2/7/2019[/TD]
[/TR]
</tbody>[/TABLE]

Also note, there will be multiple Key values.

 

[navic]

Try this idea for a few ways
The ARRAY formula in 'D11' cell is the following (CSE formula-copy down)

=INDEX($D$3:$D$6;MIN(IF((A11=$A$3:$A$6)*(C11>=$B$3:$B$6)*(C11<=$C$3:$C$6);MATCH(ROW($D$3:$D$6);ROW($D$3:$D$6)))))

or
The ARRAY formula in 'F11' cell is the following (copy across)

=INDEX($D$3:$D$6;SMALL(IF(($A11=$A$3:$A$6)*($C11>=$B$3:$B$6)*($C11<=$C$3:$C$6);MATCH(ROW($D$3:$D$6);ROW($D$3:$D$6)));COLUMN(A$1)))

or
The ARRAY formula in 'F19' cell is the following (copy across)

=INDEX($D$3:$D$6;SMALL(IF((F$17=$A$3:$A$6)*(F$18>=$B$3:$B$6)*(F$18<=$C$3:$C$6);MATCH(ROW($D$3:$D$6);ROW($D$3:$D$6)));ROW($A1)))

Use IFERROR function to remove #NUM ! or IF/ISERROR for older Excel.