×

# XLOOKUP Formula To Return All Matches

By Chris Newman •  Updated: 09/09/22 •  5 min read

## Can You Return All Matches With XLOOKUP?

Currently, you cannot return multiple lookup results with Excel’s XLOOKUP function. You can learn exactly what XLOOKUP can and can’t do in my XLOOKUP function overview guide.

Luckily, there is hope!

You can utilize Excel’s FILTER function to get the same effect.

The below example shows a single Excel formula that looks up a person’s name from a data table and returns all the results associated with that person.

In this article, you will learn how to write an Excel formula that will emulate an XLOOKUP function returning all match results based on a particular ID.

## Using FILTER Instead of XLOOKUP For Multiple Results

Since the XLOOKUP function can only return a single row or column’s worth of data when it finds a match, we will need to utilize a different function to carry out our desired result.

The FILTER function allows you to condense a range of data based on criteria. By setting the filter criteria to equalling our desired lookup ID (in the below example “Bob”), we can retrieve results in the Customer Signed column that are only associated with the name ID.

Utilizing the FILTER function will provide you with a list of all the results associated with your lookup value. Just as if you had been using the XLOOKUP function all along to return all the found matches.

### Formula To Lookup All Match Results

Below is the formula I wrote in the above example that you can copy to get started in your own Excel spreadsheet.

``=FILTER(C4:C13,B4:B13=E5)``

## How To Find All Results And Sort Alphabetically

If you would like to have the added benefit of ordering your results alphabetically, you can utilize the SORT function to organize the matched results prior to the values being output onto the spreadsheet.

Simply wrap the SORT function around the entire FILTER function and it will suppress any duplicates in the results.

This function combination should leave you with a well-ordered list of all the results associated with your lookup value. Just as if you had been using the XLOOKUP function all along to return all the found matches.

### Formula To Lookup All Results & Sort Alphabetically

Below is the formula I wrote in the above example that you can copy to get started in your own Excel spreadsheet.

``=SORT(FILTER(C4:C13,B4:B13=E5))``

## How To Find All Results And Remove Duplicates

If you are worried about having duplicate values within your results you can utilize the UNIQUE function to remove any multiples. Simply wrap the UNIQUE function around the entire FILTER function and it will suppress any duplicates in the results.

This function combination should leave you with a clean list of all the results associated with your lookup value. Just as if you had been using the XLOOKUP function all along to return all the found matches.

### Formula To Lookup All Unique Results & Sort Alphabetically

Below is the formula I wrote in the above example that you can copy to get started in your own Excel spreadsheet.

``=UNIQUE(SORT(FILTER(C4:C13,B4:B13=E5)))`` 