Return value based upon lookup value and ranged date

Very hard to describe my struggle without posting an example, so I have simply explained it in the workbook. My hope is to do this without VBA.

Range Lookup Value.xlsx

In summary, I would like to return a value from a look-up table, based upon today's date, when the look-up table provides a date range (but return one row higher) when the date falls within a certain range.

Again, I think the file explains it better.

Thank you for your help and direction.

Try this

=INDEX(E4:E21,MATCH(B1,B4:B21,1))

Originally Posted by Gottoplay

Try this

=INDEX(E4:E21,MATCH(B1,B4:B21,1))

Thanks for the reply Gottoplay. But that returns the value of E in the SAME row. I am looking for the value of E of the row above. For today, 820 should be returned. 880 is returned with your suggestion.

I did not see that you wanted the one above. You can use the same formula with one modification.

=INDEX(E4:E21,MATCH(B1,B4:B21,1)-1)

That should work

I think a small variation gets me there:

=INDEX(E4:E21,MATCH(B1,C4:C21,1))

Thanks to the both of you.

I was making is much more difficult then needed. I forgot about the "1" value to be "largest value less than or equal to"