Return Multiple Excel Lookup Results Without Spilling Inside a Table
Question details
Return all records matching a lookup value when the destination is an Excel 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.
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.
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.
Click a cell in the destination table, then select Table Design > Convert to Range and confirm. Leave the source table, Table1, unchanged.
Type the alley to look up in cell A2, outside the intended spill area.
Select B2 and enter =FILTER(Table1[Fruit],Table1[Alley]=A2,""). Excel will return matching fruits in the cells below.
If Excel displays #SPILL!, clear occupied cells in the intended output area and check that the formula is outside an Excel table.

Fill table rows with an INDEX and SMALL formula
Use this approach when the destination must remain a table and each row should return one match.
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.

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.




