How to return multiple matches in Excel

Return every match with FILTER

FILTER takes the values to return and a condition. It returns all rows where the condition is TRUE and spills them into the cells below, so leave those cells empty.

The condition is a comparison on a whole column, like A2:A9="Sales". In a real sheet, point it at an input cell instead, such as A2:A9=G1, so you can switch departments by typing in G1.

Return whole rows, not one column

Give FILTER a wider range to return several columns at once. Here we get the name, start year and rating for each IT employee.

Several conditions

Multiply conditions for AND: both must be true. Use + for OR. Put each condition in its own brackets.

Show a message when nothing matches

With no matches, FILTER returns #CALC!. The third argument sets what to show instead.

Sort the results or put them in one cell

Wrap FILTER in SORT to order the list: =SORT(FILTER(B2:B9,A2:A9="Sales")).

To list every match in a single cell, for a summary table or a label, wrap it in TEXTJOIN with a separator.

In Excel 2019 and earlier

FILTER is not available there. The classic workaround combines INDEX, SMALL, IF and ROW in a formula entered with Ctrl+Shift+Enter, copied down until it returns errors. It works but is hard to maintain.

A simpler option is Data > Filter: click the arrow on the Dept column, tick only Sales, and copy the visible rows. Advanced Filter (Data > Sort & Filter > Advanced) can copy matching rows to another location.