How to return ALL matches, not just the first (VLOOKUP for multiple results)

A lookup that lists every row matching the key — where VLOOKUP/XLOOKUP stop at one.

The formula

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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 150, 80, 200 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

FILTER — see full Excel, Google Sheets & LibreOffice compatibility for each.

Related recipes

• How to average a range that contains errors
• How to count unique values that match a condition
• How to FILTER by multiple criteria (AND / OR)
• How to find values that appear in both of two lists
• How to use the FILTER function

Related comparisons

• ARRAYFORMULA vs dynamic arrays: the same idea, three dialects
• FILTER vs QUERY: Sheets' two ways to slice data
• FILTER vs VLOOKUP: return every match, not just the first