I’m making a spreadsheet to convert values based on set exchange rates in Excel for MS 365 v. 2403.

On a Summary worksheet I have Transaction Date and USD Exchange Rate, where the later is pulled from the Exchange Rate Tracker Worksheet.

Worksheet 1: Summary
| Transaction Date | USD Exchange Rate |
| Cell A2 | Cell B2 |
| Cell A3 | Cell B3 |

On the Exchange Rate Tracker sheet I have the set date and the set exchnage rates for different currencies (manually entered, not using the Excel functions).

Worksheet 2: Exchange Rate Tracker

| Date | 01-May-2022|
| USD | Cell B2 |
| CAD | Cell B3 |
| Euro | Cell B4 |
| Yen | Cell B5 |

I want to pull the exchange rate from Worksheet 2 row 2 based on the nearest prior date given in Worksheet 2 row 1 and have it reported into Worksheet 1 column B.

Example Data:

Worksheet 1: Summary
| Transaction Date | USD Exchange Rate |
| 27-May-2024 | Cell B2 |
| 06-Jun-2021 | Cell B3 |

Worksheet 2: Exchange Rate Tracker

| Date | 08-Dec-2019| 01-May-2022|
| USD | 1 | 5 |
| CAD | 2 | 6 |
| Euro | 3 | 7 |
| Yen | 4 | 8 |

I’m trying to nest a HLOOKUP and MATCH function, however I’m having errors with the basic match function using dates as formatted.

Using
=(MATCH(‘Summary’!$A5,’Exchange Rate Tracker’!$B$1:$ZZ$1,1))
I’m getting 01-Jan-1900 (“date 0” in Excel). Was thinking this may be because the entirety of Exchnage Rate Tracker Row 1 isn’t completed with dates, but narrowing the date doesn’t change the response.

I would like something like
=HLOOKUP(MATCH(‘Summary’!$A5,’Exchange Rate Tracker’!$B$1:$ZZ$1,1),’Exchange Rate Tracker’!$B$1:$ZZ$500,2)
Which currently gives an #N/A error.

Any help would be greatly appreciated.