FILTER vs VLOOKUP: return every match, not just the first
VLOOKUP finds the first row that matches and returns one value. FILTER returns every row that matches, spilling them into a range. If a key can appear more than once — all orders for a customer, all students in a class — VLOOKUP silently drops the rest and FILTER is what you actually want.
The differences at a glance
| FILTER | VLOOKUP | |
|---|---|---|
| How many results | Every matching row (spills) | Only the first match |
| Returns | A whole range/array you can shape | A single value |
| Look left of the key | Yes — columns are independent | No — lookup column must be leftmost |
| Multiple conditions | Yes — multiply conditions: (a)*(b) | No — needs a helper column or XLOOKUP |
| No match | #CALC! (set the [if_empty] argument) | #N/A (wrap in IFERROR) |
| Compatibility | Sheets: all; Excel 365 & LibreOffice 24.8+ (see the FILTER page) | Universal — every version of all three apps |
Which should you use?
- VLOOKUP — One key maps to one value and you just need that value — a price for a SKU, a name for an ID. It's universal, so it's the portable choice for older Excel or shared files.
- FILTER — A key can match many rows and you need them all, you want to pull columns to the left of the key, or you need multi-condition logic. Modern Sheets/Excel only — check the compat note before shipping to old Excel.
Compatibility (from executed tests)
VLOOKUP runs everywhere; FILTER is newer — native in all Google Sheets, but Excel needs 365 (dynamic arrays) and LibreOffice needs 24.8+. On a version without FILTER it errors as #NAME?, so for files that may open in old Excel, stick with VLOOKUP/INDEX-MATCH or gate on the version. Where both exist, FILTER also sidesteps VLOOKUP's leftmost-column limitation and its silent 'first match only' behavior.
Example formulas
| VLOOKUP — first price for a SKU | =VLOOKUP("A100",A2:C500,3,FALSE) |
| FILTER — every order for a customer | =FILTER(C2:C500,A2:A500="A100") |
| FILTER — two conditions, with empty fallback | =FILTER(C2:C500,(A2:A500="East")*(B2:B500>100),"none") |
Full per-version details on each function page: FILTER · VLOOKUP.
See also: VLOOKUP vs XLOOKUP · FILTER vs QUERY.