XLOOKUP Return All Matches. Possible? (Formula in Excel)

Short Answer: It Can’t. But don’t worry about it because there’s a better function to get what you want. Yes, all matches. The Function is FILTER.

In the above example, all the data matches EAST in the zone column.

The Formula

So, here’s how to use this function to get all the matches:

1. First, enter the FILTER function in a cell.
   
2. After that, in the array argument, select the entire data range.
   
3. Next, in the include argument, select the column from which you want to check for all the matched values.
   
4. In the same argument, after that column range, enter the equal sign (=) and the value to check for the matches. Again, use double quotation marks to specify the text.
   
5. Close the function and hit enter to get the result.

And the moment you hit enter, it will return an array with all the matches where the zone value is “East”.

Yes, FILTER is a dynamic array function, and you don’t need to use CTRL + SHIFT + ENTER to get the result. It will automatically get the answer in a range according to its size. So, you need to ensure that the area where you enter the function has enough space to get all the matches.

If you have a value anywhere in the range that is needed to be used by the array returned by the FILTER function, you will get the #SPILL! Error.