×

XLOOKUP Formula To Return All Matches

By Chris Newman •  Updated: 09/09/22 •  4 min read
How to write an Excel formula to return all.

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.

XLookUp function returning all matches with Excel Filter Function.

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.

XLookUp function to return all matches

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.

Excel formula to return all 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.

Lookup function return all unique matches sort with Excels XLookUp function.

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


Download Example Excel File

If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.