Lookup with more than one criteria

[EllenKristina]

Hi, 

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? 

[#2]

@EllenKristina 

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.

[#3]

@Rodrigo_ 

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...

[#4]

Hi @EllenKristina 

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

[#5]

Hi @Lorenzo 

I'm on Version 2210 (Build 15726.20202)

[#6]

@EllenKristina 

I'm on Version 2210 (Build 15726.20202)

OK, so you run Excel 365 🙂

See one way with FILTER in the attached file

[#7]

@EllenKristina 

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.

[#8]

@EllenKristina 

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],
      LAMBDA(date,code,
          LET(
              filteredDates, IF(CXRateTbl[Code]=code, CXRateTbl[Date]),
              XLOOKUP(date,  filteredDates, CXRateTbl[Rate],"No match",1)
          )
      )
  )

[#9]

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...