Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Lookup with more than one criteria

Copper Contributor


I have an Excel sheet with several transactions in different currencies and on different dates. In the next sheet I have an overview of all the different currencies on each day for a given period of time. 

In column A I have a date, and in Column F I have a currency code. In column G I need to return the currency rate from sheet two where the criteria’s date AND currency code is met.


What formula(s) should I use? 

8 Replies


Based on your explanation, you could use INDEX and MATCH functions
also there is a VLOOKUP and XLOOKUP functions.

I'm not an expert but I can help you with my knowledge, if you want, you can share your file here so I can have better understanding on what you're looking for.



Thank you so much,


An example of this case is attached. Long story short - i need to know the rate to each purchase.  


Edit: I was not able to upload the file for some reason...






Hi @EllenKristina 


So nobody waste time: which version of Excel do you run?

Hi @L z. 


I'm on Version 2210 (Build 15726.20202)



I'm on Version 2210 (Build 15726.20202)

OK, so you run Excel 365 :)


See one way with FILTER in the attached file


Here it is, hope you get what you're looking for.
I used concatenate for your criteria, "DATE" and "Currency Code" which I named it LOOKUP HELPER on your Currency sheet, and I use the VLOOKUP function on your Transaction sheet with the same concatenated cell/word of your criteria as a lookup value.


Another strategy is to blank out the dates with that correspond to the wrong currency and then use XLOOKUP to return the exact match from the remaining dates or, failing that, the next latest.

= LET(
      filteredDates, IF(CXRateTbl[Code]=[@Code], CXRateTbl[Date]),
      XLOOKUP( [@Date], filteredDates, CXRateTbl[Rate],"No match",1)

Placed within the Transactions Table the formula will propagate down the column.  If used outside the Table one should aim at returning results as a dynamic array.  This require the use of MAP and LAMBDA to get the inequalities between two arrays to work correctly

= MAP(Transactions[Date], Transactions[Code],
              filteredDates, IF(CXRateTbl[Code]=code, CXRateTbl[Date]),
              XLOOKUP(date,  filteredDates, CXRateTbl[Rate],"No match",1)


Hi @EllenKristina 

You got several options. If any of them solves the issue, there's a link to mark the solution at the bottom :) - This helps those who search


Thanks & nice day...