XLOOKUP can’t return all matches by default; however, here is the workaround with the Excel FILTER function.
If we are talking about lookup functions in Excel, everyone knows that XLOOKUP is a Swiss knife in Excel. It is good to know that it works the same as other lookups; it returns the first matching record corresponding to a lookup value. The good news is that there is a workaround with the FILTER function. Take a closer look at the example and read the step-by-step guide. If you want to learn all about XLOOKUP, check our definitive guide.
General Formula to get all matching values using the FILTER function:
=FILTER(return array, lookup array = lookup value)How do I get XLOOKUP to return all matches?
- Enter the FILTER function
- Define the first argument, return_array
- Use an expression as the second argument: lookup_array = lookup_value
- The formula returns all matching records
Explanation, Syntax, and Arguments
This section will show you how the formula works through a simple example. Before deeply diving into the details, take a closer look at the FILTER function syntax and arguments.
Syntax:
=FILTER(array, include, [if_empty])The FILTER function uses two required and one optional argument to get all matches:
Arguments:
- array: the array that contains the possible matches
- include: filters an array based on the criteria
- [if_empty] – optional argument: error-handling option in case of no match found
First, create two named ranges to simplify the formula. Select the range, locate the name box, and enter a descriptive name for a given range.
- data: range B3:G12
- totals: range G3:12
Enter the following formula in cell I5:
=FILTER(data, totals=281)The second argument, [include], will find and extract all matching records from the “data” range.
You can use the FILTER function to filter data based on custom criteria. In the example, we want to return the matching rows where the total score equals 281. The formula returns multiple results, and Excel will (like other dynamic array functions in Excel) spill all records that meet the criteria.
Download the practice file (.zip)
Additional resources:
- Learn all about the FILTER function
- Find the 2nd match using XLOOKUP
