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:

DOWNLOAD WORKSHEET

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.