Looking up items in a list is easy when we use the COUNTIF function.
COUNTIF function returns a count of number of times a criterion appears in a dataset.
=COUNTIF(dataset, criterion)
For example, in the image below, we are counting if the currencies appear in our list (ZAR, USD, CHF)
If the currency appear in the list, it returns 1 else zero.

The above count of appearances array plays a big role in looking up items as shown in the 3 below methods.
Using the VLOOKUP Function to return items in a list
=VLOOKUP(
LARGE(COUNTIF(list,Sales[Currency])*ROW(Sales[Currency]),ROW(A1)),
IF({1,0},ROW(Sales[Currency]),Sales[Customer]),
2,0)
See below video showing how the formula works:
Using the FILTER Function to return items in a list
=FILTER(Sales[Customer],COUNTIF(list,Sales[Currency]))
See below video showing how the formula works:
Using the TOCOL and IFS Function to return items in a list
=TOCOL(IFS(COUNTIF(list,Sales[Currency]),Sales[Customer]),2)
See below video showing how the formula works:
Recent Comments