Recipes / Recipe 6

XLOOKUP patterns that replace VLOOKUP headaches

Copy-paste lookups for the cases that trip people up: two criteria, whole columns at once, the latest match, tiered pricing.

FormulasLookups Setup time: 2 minutes

You'll need: Google Sheets or Excel 365. Examples assume a Products tab with SKU in A, Size in B, Price in C.

The patterns

Basic lookup with a friendly fallbackFormula

=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

Return several columns at once (spills right)Formula

=XLOOKUP(A2, Products!A:A, Products!B:D)

Two criteria (SKU + size), Google Sheets-safeFormula

=IFERROR(INDEX(FILTER(Products!C:C, Products!A:A=A2, Products!B:B=B2), 1), "Not found")

Two criteria, Excel 365 styleFormula

=XLOOKUP(1, (Products!A:A=A2)*(Products!B:B=B2), Products!C:C, "Not found")

Most recent match (search bottom-up), e.g. last order date per customerFormula

=XLOOKUP(A2, Orders!B:B, Orders!A:A, "None", 0, -1)

Tiered pricing: find the tier at or below a quantity (Tiers!A = min qty, B = unit price)Formula

=XLOOKUP(B2, Tiers!A:A, Tiers!B:B, , -1)

Fill a whole column with one formula (Google Sheets)Formula

=ARRAYFORMULA(IF(A2:A="", , IFERROR(VLOOKUP(A2:A, Products!A:C, 3, FALSE), "Not found")))

Works in every version of Excel and SheetsFormula

=IFERROR(INDEX(Products!C:C, MATCH(A2, Products!A:A, 0)), "Not found")

Good to know

Most “not found” errors are invisible differences: trailing spaces or numbers stored as text. Wrap the key in TRIM(), or see the clean messy data recipe.
XLOOKUP defaults to an exact match, unlike VLOOKUP, which is one reason it's safer.
In the tiered-pricing pattern, -1 means “exact match, or the next smaller value”. Sort isn't required.

Do it yourself

Recipe Club: $12/month

Prefer to set things up yourself? Join the Recipe Club and get access to a private GitHub repository of ready-made, tested automations for Sheets, Apps Script, Python and Node. Each one comes with setup notes and sample data.

A new recipe added every week
Everything in the free library, plus the longer, more complete versions
Use them in your own business as much as you like
Cancel anytime from your Stripe receipt or by email

Recipe Club opens soon: join the waitlist

You'll need a free GitHub account. After checkout, reply with your GitHub username and we'll send the repo invite, usually within one business day.

Or buy a one-time recipe pack ($19–$29)

Clean Data Pack $19
Dedupe, cleaning, merging and normalizing scripts and formulas for messy exports, with a sample sheet.
Coming soon
Forms & Inbox Pack $19
Form auto-replies, request routing, a lead log and follow-up nudges, ready to paste into Apps Script.
Coming soon
Money Ops Pack $29
Invoice reminders, a monthly revenue dashboard with QUERY, a receipts log and nightly backups.
Coming soon

Written with AI assistance. Script logic is unit-tested; formulas follow documented Google Sheets behavior. Always try new automations on a copy of your sheet first.