Find the latest currency exchange rate from a range of cells, prior to a certain date-CodePudding

I have a sheet with some dates, and currency exchange rates on that date.

I have another sheet with some dates, and what I need is a function to return me the exchange rate on that day, or the latest date prior to that date.

So, something like this:

Sheet1
-----------------------
   |     A     |   B  |
-----------------------
 1 |2022-08-01 | 0.95 |
-----------------------
 2 |2022-07-25 | 0.91 |
-----------------------

Sheet2
----------------------------------------------------
   |     A     |                B                  |
----------------------------------------------------
 1 |2022-08-03 | Function which should return 0.95 |
----------------------------------------------------
 2 |2022-07-25 | Function which should return 0.91 |
----------------------------------------------------

EDIT: This needs to work in Excel 2007 and LibreOffice Calc (without FILTER)

CodePudding user response：

vlookup does the job if your data is sorted.

=VLOOKUP(A1,$Sheet1.A1:B2,2,1)

sheet 2

sheet 1

CodePudding user response：

This function would handle it irrespective of the order,blanks, etc words in column a.

=index(Sheet1!B:B,match(Max(filter(Sheet1!A:A,isnumber(Sheet1!A:A)*(Sheet1!A:A<=A1))),Sheet1!A:A,0),1)