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. And the Function is FILTER.

In the above example, we have all the data matching with 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 in which you don’t need to use the 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 should have enough space to get all the matches for you.

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