XLOOKUP Formula To Return All Matches
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)))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.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![4 PRO Ways to Create A Filtering Search Box [Excel Download]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/10/ExcelFilteringSearchBox.png)
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.