fx =FILTER(A1:D100,B1:B100>50)ABCSKU-4821Widget A$24.99SKU-7293Gadget B$89.50SKU-1056Tool C$12.75
🔍 Lookup

FILTER Formula for Lookup

Return rows matching criteria (dynamic filter). Copy-paste ready for Excel, Google Sheets, and Airtable.

=FILTER(A1:D100,B1:B100>50)
When you need this: When you need to show only orders exceeding $50 (amount in B1:B100) from your full order table (A1:D100).

How it works

  1. 1Specify the array to filter (A1:D100 = all columns).
  2. 2Define the filter criterion (B1:B100>50 = amount greater than $50).
  3. 3FILTER returns only matching rows, updating dynamically as data changes.

Variations

FILTER with multiple criteria
=FILTER(A1:D100,(B1:B100>50)*(C1:C100="Complete"))
FILTER with OR condition
=FILTER(A1:D100,(B1:B100="NY")+(B1:B100="CA"))

Related Formulas

VLOOKUP — Look up a value in a table by matching the first columnLookupINDEX/MATCH — Flexible lookup - more powerful than VLOOKUPLookupXLOOKUP — Modern replacement for VLOOKUP (Excel 365)LookupSORT — Sort a range dynamicallyLookup

Need a formula that's not here?

Generate custom formula →