How to return the entire row of a match

Look up a key and pull back the whole record — every column for that customer, order, or SKU — not just one field.

The formula

How it works

VLOOKUP returns a single column; to pull a whole record you want FILTER, which keeps every column of the rows whose condition is TRUE. Here filtering the three-column table on name = "Amy" spills back her entire row — Amy, 90, Blue — into adjacent cells. Because it's a dynamic array the result grows or shrinks with the data, and if the key matches several rows you get all of them stacked, which is exactly what you want for one-to-many lookups. On versions without FILTER you have three fallbacks: VLOOKUP/XLOOKUP with an array of column numbers ({1,2,3}) to spill columns, INDEX(range, MATCH(...)) written once per column with the key locked absolute, or XLOOKUP returning a whole row by pointing its return argument at the full table. Add more conditions by multiplying them inside FILTER: FILTER(A2:C100,(A2:A100="Amy")*(B2:B100>50)).

Verified, not just documented

We ran =FILTER(A2:C4,A2:A4="Amy") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned Amy, 90, Blue — exactly the expected result. Every formula here is confirmed by actually executing it.

Functions used

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

Related recipes

• 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
• How to calculate a median with a condition (MEDIANIF)

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