Retrieves a value from a range or array based on a lookup key, returning exact or approximate matches without requiring a column index number. The governed replacement for VLOOKUP in every DDL workbook.
XLOOKUP is the answer to a question every spreadsheet eventually asks: given a key, find the matching value in another table. VLOOKUP did this for two decades, but it counted columns instead of naming them, broke when columns moved, and stayed silent when keys were missing. XLOOKUP fixes all three problems in one function. You give it the value you’re looking for, the column to search, the column to return, and a fallback for misses. No counting. No column index. No silent failures.
Here’s a real XLOOKUP. Click each argument to understand what it does and why it’s there.
This is the key — the thing you have, that you want to find a match for. It can be a literal value, a cell reference, or another formula that produces a value. In this case, A2 holds an SKU like SKU-1042 and we want to find the price for it in the Products table.
This is where XLOOKUP looks for matches. It must be a single column or row — the same shape as the return_array. Here we’re using a structured reference (Products[SKU]) instead of a range like B2:B500. That matters: when someone adds a new product, the table grows automatically and the formula keeps working without edits.
Once XLOOKUP finds the matching SKU in the lookup_array, it looks at the same row position in the return_array and returns that value. In VLOOKUP you had to count columns — “the price is the 4th column over” — and pray nobody inserted a column. XLOOKUP names the column directly. No counting. No fragility.
If the SKU in A2 doesn’t exist anywhere in Products[SKU], XLOOKUP returns this value instead of throwing #N/A. This is the argument most people skip — and it’s the most important one for governance. Without it, every missing match becomes a visible error or, worse, propagates as #N/A through every downstream formula.
XLOOKUP supports four match modes: 0 exact (the default and what you almost always want), -1 exact or next smaller, 1 exact or next larger, and 2 wildcard. Setting it explicitly to 0 documents your intent — future readers know you considered the question. Approximate matches are how silent wrong-answer bugs ship.
The same task — one formula is fragile, one is not.
=VLOOKUP( A2, B2:E500, 4, FALSE )
Counts columns. Breaks when a column is inserted. Returns #N/A on misses. Range stops at row 500. Cannot look left.
=XLOOKUP( A2, Products[SKU], Products[Price], "Not Found", 0 )
Names the column. Survives column insertion. Handles missing keys explicitly. Auto-expands with the table. Looks any direction.
XLOOKUP replaces VLOOKUP in every DDL workbook. VLOOKUP should never appear in new builds. Always pass the if_not_found argument, even when you think a miss is impossible — the day someone deletes a row, your formula becomes a quiet liability instead of a loud one.
When you need to retrieve a value from another table based on a lookup key, use XLOOKUP to return exact or approximate matches without column index dependency.
Returns #N/A on a missing match unless if_not_found is provided. Approximate match modes (-1, 1) silently return the wrong neighbor when the lookup_array isn’t sorted — producing plausible but incorrect results that pass review and ship to production. Mismatched lookup_array and return_array shapes throw #VALUE!. The lookup is case-insensitive for text by default, which can cause unintended matches when SKU formats vary.
Faster than INDEX/MATCH on large datasets. Binary search mode (match_mode 2 or -2) is significantly faster on sorted data — reserve it for sorted lookup arrays where the speed gain is real and worth the maintenance burden of keeping the array sorted.
Replaces VLOOKUP in all new builds. VLOOKUP should never appear in DDL workbooks. Every XLOOKUP must pass an explicit if_not_found argument and an explicit match_mode — defaults are how silent bugs ship.
XLOOKUP function · governed-lookup · vlookup-replacement
-- Look up a price by SKU, return "Not Found" on misses =XLOOKUP( A2, Products[SKU], Products[Price], "Not Found", 0 ) -- A2 = "SKU-1042" --> 24.99 -- A2 = "SKU-9999" --> "Not Found"
-- VLOOKUP could not do this. XLOOKUP doesn't care which side. =XLOOKUP( "Hayes", Reps[Name], Reps[ID], // ID column is to the LEFT of Name "", 0 ) -- Returns the rep ID associated with name "Hayes" -- No helper column. No reordering. No INDEX/MATCH workaround.
-- The pattern that ships in production =LET( key, A2, price, XLOOKUP(key, Products[SKU], Products[Price], 0, 0), discount, XLOOKUP(key, Products[SKU], Products[Discount], 0, 0), net, price * (1 - discount), net ) -- The lookup is named once. The math is named. -- Anyone reading this knows what each step represents.
-- Try the customer table first, fall back to defaults =XLOOKUP( A2, Customers[ID], Customers[Tier], XLOOKUP(A2, Defaults[ID], Defaults[Tier], "Standard", 0), 0 ) -- if_not_found is itself another XLOOKUP -- This is the foundation of the Fallback Chain pattern (FRM-0018)