How to return ALL matches, not just the first (VLOOKUP for multiple results)
✓ Verified in LibreOffice 25.8.7.3A lookup that lists every row matching the key — where VLOOKUP/XLOOKUP stop at one.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =FILTER(B2:B6,A2:A6="East") | Excel 365/2021. Spills one row per match; wrap in TEXTJOIN to get a single cell. |
| Google Sheets | =FILTER(B2:B6,A2:A6="East") | Identical. |
| LibreOffice Calc | =FILTER(B2:B6,A2:A6="East") | Needs LibreOffice 24.8+; older versions need the legacy SMALL/IF array pattern. |
How it works
Lookups return one result by design; FILTER returns the whole set — all three East values spill down in source order. Combine as needed: TEXTJOIN(", ",TRUE,FILTER(...)) for one cell, SORT(FILTER(...)) for ordered output, or filter several columns at once by widening the first range. This one formula replaces the notoriously fragile Ctrl+Shift+Enter SMALL/IF/ROW construction from the pre-dynamic-array era.
Verified, not just documented
We ran =FILTER(B2:B6,A2:A6="East") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 150, 80, 200 — exactly the expected result. Every formula here is confirmed by actually executing it.