Price by SKU
=XLOOKUP(A2, Products!A:A, Products!C:C, "Missing", 0) returns unit price or a clear label.
What is XLOOKUP in Google Sheets?
XLOOKUP searches a lookup array for a key and returns a matching value from a return array. It handles left lookups, approximate match, and custom not-found text without INDEX/MATCH gymnastics.
When to use it
Use XLOOKUP for price lists, employee records, and SKU tables when keys may appear anywhere relative to the return column.
When to skip it
Skip XLOOKUP on unsorted huge tables needing binary search without understanding search_mode. Legacy files with stable VLOOKUP may not need migration yet.
How it works
-
1
Put the search key in the formula or cell reference.
-
2
Point lookup_array at the column or row holding keys.
-
3
Point return_array at values to fetch when a key matches.
-
4
Set if_not_found to a friendly message instead of #N/A.
-
5
Choose search_mode for exact or next-smaller approximate matches.
-
6
Use one XLOOKUP per row or MAP/BYROW for column-wide fills.
Examples in Google Sheets
Left lookup
=XLOOKUP(E2, Codes!B:B, Codes!A:A) fetches an ID from a code column to the right of names.
Tier rate
=XLOOKUP(D2, Tiers!A:A, Tiers!B:B, , -1) approximate match picks the correct bracket rate.
Build this without starting from a blank cell
Use a Better Sheets tool for XLOOKUP, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Lookup and return arrays different lengths causing errors.
- Exact match mode on numbers stored as text keys.
- Duplicate keys returning only the first match without warning.
- Replacing VLOOKUP without fixing column index documentation.
- Omitting if_not_found so reports show ugly #N/A to clients.
Frequently asked questions
- XLOOKUP vs VLOOKUP?
- XLOOKUP searches any direction and has clearer not-found handling.
- XLOOKUP vs INDEX MATCH?
- XLOOKUP combines both in one function with optional search modes.
- Multiple results for one key?
- XLOOKUP returns first match. FILTER for all matches.
- XLOOKUP approximate match?
- Use search_mode -1 or 1 per documentation for sorted approximate lookups.
- XLOOKUP on rows?
- Yes with horizontal lookup and return arrays.
- Wildcards in XLOOKUP?
- Use exact mode and prepare keys, or REGEXMATCH workflows for patterns.
- XLOOKUP array spill?
- Pass array of search keys to return a spilled column of results.
Related Tutorials
Watch how XLOOKUP works
How do I reference a different spreadsheet in Apps Script?
Use Index Match when you want Vlookup
Search Every Function in Google Sheets
Related blog posts
Guides that explain XLOOKUP in more depth.
When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa
Esa is a beginner at Google Sheets. I discover the moment he discovered the deepest secrets of Google Sheets. And we wax poetic about AI in sheets, and data flow.
Read post →YouTuber Talks About Sheets | Sheet Talking Episode 3 Prolific Oaktree
Sheet Talking is a series of casual conversations with Spreadsheet Professionals. This one with Prolific Oaktree.
Read post →Five Frustrating Formulas and How to Conquer Them
Google Sheets formulas can be frustrating for a few reasons. First, you’re trying to remember what to use on the fly. Then you can't remember the name of a formula. If you do figure out the name, you can't remember the syntax. Then you don't know the proper way to write the data. FRUSTRATING!
Read post →Related glossary terms
VLOOKUP
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
Read guide →INDEX
INDEX fetches a value from a range by row and column number. Combined with MATCH it powers flexible lookups. Alone it picks nth items from lists, dynamic headers, or matrix intersections without VLOOKUP column limits.
Read guide →MATCH
MATCH searches a range for a value and returns its relative position. Feeding INDEX or OFFSET with MATCH replaces brittle column index math. Sorted approximate match modes support tier tables and date brackets.
Read guide →FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →Done reading about XLOOKUP?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.