# Lookup with more than one criteria

Copper Contributor

# Lookup with more than one criteria

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?

8 Replies

# Re: Lookup with more than one criteria

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

# Re: Lookup with more than one criteria

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

# Re: Lookup with more than one criteria

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

# Re: Lookup with more than one criteria

Hi @Lorenzo

I'm on Version 2210 (Build 15726.20202)

# Re: Lookup with more than one criteria

I'm on Version 2210 (Build 15726.20202)

OK, so you run Excel 365 🙂

See one way with FILTER in the attached file

# Re: Lookup with more than one criteria

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

# Re: Lookup with more than one criteria

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

# Re: Lookup with more than one criteria

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