Lookup with more than one criteria

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

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

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.

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.