XLOOKUP is the newer, more flexible replacement for VLOOKUP. It searches one range, returns from another, and can look left, return several columns, find the last match and match approximately. This page covers each use with a short formula, and ends with what XLOOKUP still cannot do.
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
match_mode: 0 exact (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
search_mode: 1 first to last (the default), -1 last to first.
XLOOKUP defaults to exact match. VLOOKUP defaults to approximate unless you type FALSE.
XLOOKUP takes a result range, not a column number, so inserting a column does not break it.
XLOOKUP can return from the left of the search column. VLOOKUP cannot.
XLOOKUP has a built-in not-found value, so you do not need IFNA.
Row 1 holds headers. Master list, D2:F5 (name, ID, city):
Initech, 101, Perth
Umbrella Corp, 102, Sydney
Acme Pty Ltd, 103, Melbourne
Globex Corporation, 104, Brisbane
=XLOOKUP("Acme Pty Ltd",$D$2:$D$5,$E$2:$E$5,"Not found")
This returns 103. With "Wayne" it shows Not found. If you leave out the fourth argument, a missing value returns #N/A. For the causes of #N/A in lookups, see https://www.snakbacks.com/vlookup-na-error-google-sheets
Give a result range that is wider than one column:
=XLOOKUP("Acme Pty Ltd",$D$2:$D$5,$E$2:$F$5,"Not found")
It spills into two cells: 103 and Melbourne. Keep the cells to the right empty, or you get #REF!.
Search the ID column and return the name from the column on its left:
=XLOOKUP(103,$E$2:$E$5,$D$2:$D$5,"Not found")
This returns Acme Pty Ltd. VLOOKUP cannot do this without INDEX and MATCH.
Wildcards only work when match_mode is 2. Use * for any run of characters, ? for one character, and ~ before a * or ? to find it literally.
=XLOOKUP("*Corp*",$D$2:$D$5,$E$2:$E$5,"Not found",2)
This returns 102 (Umbrella Corp), the first match. Both Umbrella Corp and Globex Corporation contain "Corp", so XLOOKUP returns the first row it finds. To match a typed cell, join the wildcards on: "*"&A2&"*". Without the 2, the stars are treated as ordinary characters and nothing matches. The VLOOKUP version is here: https://www.snakbacks.com/vlookup-partial-match-google-sheets
Take Customer in A2:A6 and Amount in C2:C6: Acme 100, Globex 50, Acme 25, Acme 10, Globex 70. To get the latest amount for a customer, search from the bottom:
=XLOOKUP("Acme",$A$2:$A$6,$C$2:$C$6,"Not found",0,-1)
This returns 10, the last Acme row. The default (search_mode 1) returns 100. For a whole-table version of "keep the latest", see https://www.snakbacks.com/remove-duplicates-keep-latest-google-sheets
With match_mode -1, XLOOKUP returns the row whose key is the exact value or the next smaller one. That suits bands that start at a threshold. Illustrative brackets (made-up numbers, not real tax rates), G2:H5:
0, 10%
10000, 15%
40000, 25%
90000, 30%
=XLOOKUP(52000,$G$2:$G$5,$H$2:$H$5,"Below range",-1)
This returns 25%, the bracket starting at 40000. An income of 40000 exactly also gives 25%. A grade table works the same way: starting scores 0 F, 50 D, 60 C, 70 B, 80 A. A score of 73 with match_mode -1 returns B.
With match_mode 1 you get the next larger value instead. For shipping with weight limits 1, 5 and 10 kg priced 5, 9 and 15, a 3 kg parcel looks up 5 and returns 9:
=XLOOKUP(3,$J$2:$J$4,$K$2:$K$4,"Too heavy",1)
A 12 kg parcel returns Too heavy.
Exact mode needs the text to be identical. "Initech " with a trailing space, or "Inytech" with a typo, comes back as Not found. Trimming fixes spaces:
=XLOOKUP(TRIM(A2),$D$2:$D$5,$E$2:$E$5,"Not found")
Wildcards help when one string sits inside another. Neither fixes typos, swapped words or different spellings, because there is no similarity setting in XLOOKUP. For that you match by similarity: https://www.snakbacks.com/match-names-with-typos-google-sheets
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
If your search values are typos or spelling variants of the master list, the Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas adds FUZZYLOOKUP(values, lookup, return_range, [threshold]) and FUZZYSCORE(a, b), pasted into your sheet once, with a 30-day refund if it does not help. Everything else on this page needs only the free formulas above.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.