Google Sheets Date Error message when using Vlookup AND Match formula

Question

I have 2 tabs in a Google Sheet

1. FXRATES

2. MTDWQ

On the FXRATES tab i have the following shown in the image

On the MTDWQ tab I have the following shown in the second image

As you can see in the image I'm getting the following error message when using the below formula;

=VLOOKUP(A2, FXRATES!$B$2:$G$13, MATCH(B2, FXRATES!$B$1:$G$1, 0), FALSE)

Error: Did not find value '45323' in MATCH evaluation.

I've tried multiple ways of formatting but it still doesn't work. All I'm trying to do is based on the Currency Column and Month column, cross reference those values on the FXRATES table and extract the relevant exchange rate.

Here's a duplicate of the original Google Sheet: Google Sheet Example

Can someone tell me what I'm doing wrong?

I've tried reformatting the date columns but i still get the same error message.

Answer 1

You may try:

=map(A2:A,B2:B,lambda(a,b,if(counta({a,b})<>2,,ifna(filter(filter(FXRATES!B:G,FXRATES!B1:G1=a),FXRATES!A:A=b)))))