Function Problems

Return Multiple Excel Lookup Results Without Spilling Inside a Table

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Return all records matching a lookup value when the destination is an Excel table.

Return Multiple Excel Lookup Results Without Spilling Inside a Table
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to list every fruit associated with a specified alley.
Observed behavior
XLOOKUP returns the first match, while a FILTER formula cannot spill multiple results inside an Excel table.
Before you start

Check that the source table is named Table1 and has columns named Fruit and Alley; adjust the formulas if your names differ. Place the lookup value in A2, outside the output area.

Solution 1Recommended

Convert the destination table to a range and use FILTER

Use this approach when the destination does not need to remain an Excel table and your Excel version supports FILTER.

1
Convert the destination table

Click a cell in the destination table, then select Table Design > Convert to Range and confirm. Leave the source table, Table1, unchanged.

2
Enter the lookup value

Type the alley to look up in cell A2, outside the intended spill area.

3
Enter the FILTER formula

Select B2 and enter =FILTER(Table1[Fruit],Table1[Alley]=A2,""). Excel will return matching fruits in the cells below.

4
Clear any spill obstruction

If Excel displays #SPILL!, clear occupied cells in the intended output area and check that the formula is outside an Excel table.

Convert the destination table to a range and use FILTER
Keep the spill area available: The number of results can change as the source data or lookup value changes, so leave enough empty cells below the formula.
Free Microsoft Office alternative

Work with Excel lookup data in WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and move between applications easily; formula and table behavior may vary by version.

Work with lookup data and spreadsheet formulas in WPS SpreadsheetsOpen and edit Microsoft Excel formats, including XLSX and XLSUse a familiar interface for a smoother transition from ExcelInstall a lightweight office suite for everyday document work
microsoft office alternative - wps office

Frequently Asked Questions

Why does FILTER show a spill error inside my Excel table?

Excel tables do not support spilled array formulas inside their cells. Select the destination table and use Table Design > Convert to Range, or put the FILTER formula in an empty range outside the table.

Do I have to convert the source table to a range?

No. Keep the source as Table1 so the formula can use structured references such as Table1[Fruit]. Only the destination needs to be outside a table for FILTER to spill.

What if my Excel version does not have FILTER?

Use the INDEX and SMALL formula in the second solution and fill it down the destination rows. In versions earlier than Microsoft 365 or Excel 2021, confirm the formula with Ctrl+Shift+Enter.

Why are some rows blank in the INDEX and SMALL results?

The IFERROR part returns an empty string when there are no more matches. Check the alley value in A2 and the Table1 column names if all result rows are blank.

Can XLOOKUP return every matching row?

A standard XLOOKUP returns the first match it finds. For all matching records, use FILTER in a range outside a table or fill a one-result-per-row formula down a table column.