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:

## Seeking Microsoft Certification?

No Huff! No Fluff! No Spam!