XLOOKUP in Google Sheets

Finds a value in one range and returns the matching item from another. Exact match by default, with an optional value for not found.

In Google Sheets

Google Sheets added XLOOKUP in 2022 with the same arguments in the same order, including the value for when nothing is found, match mode and search mode.

How to use XLOOKUP

XLOOKUP is the modern lookup. You tell it what to find, which range to search and which range to bring the answer from. Because the search and return ranges are separate, the answer can sit to the left or right of the key, or on another sheet entirely.

Use it for the same jobs as VLOOKUP: a price from a price list, a department from an employee number, a customer name from an ID. It also handles cases VLOOKUP struggles with. The fourth argument is what to show when nothing matches, so =XLOOKUP(A2,F2:F20,G2:G20,"Not found") needs no IFERROR. Match mode finds the next smaller value for discount tiers or tax brackets, or turns on wildcards. Search mode -1 starts from the bottom, which gives you the last entry when a customer appears several times.

Both ranges must be the same size. XLOOKUP matches exactly unless you say otherwise, so there is no FALSE to forget, and inserting a column does not break it. To return several columns at once, point return_array at all of them and the results spill into the cells to the right. If you need a classic alternative, INDEX with MATCH does the same job.

Syntax

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value
The value to find.
lookup_array
The range to search.
return_array
The range to return from, the same size as lookup_array.
if_not_found (optional)
What to return when there is no match, instead of #N/A.
match_mode (optional)
0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcards.
search_mode (optional)
1 first to last (default), -1 last to first.

Examples

D1=XLOOKUP("Plum",A2:A4,B2:B4)
ABCD
1FruitPrice5
2Apple4
3Pear6
4Plum5

In plain words: Find Plum in the fruit in A2:A4 and return its price from B2:B4.

D1=XLOOKUP("Kiwi",A2:A4,B2:B4,"Not found")
ABCD
1FruitPriceNot found
2Apple4
3Pear6
4Plum5
E1=XLOOKUP(1,(A2:A5="North")*(B2:B5="Bags"),C2:C5)
ABCDE
1RegionProductSales150
2NorthShoes420
3SouthBags310
4NorthBags150
5EastShoes280

In plain words: Find a 1 in the rows where the region is North and the product is Bags and return the sales from C2:C5.

Common XLOOKUP mistakes

  • XLOOKUP returns #N/A for a value that is clearly in the list

    The values only look the same. A stray space or a number stored as text is the usual cause. Clean the lookup value with =XLOOKUP(TRIM(D2),A2:A20,B2:B20), or use VALUE(D2) when the list holds real numbers and your value is text.

  • XLOOKUP always finds the first row, but I want the last

    When a value appears more than once, XLOOKUP returns the first match from the top. Set search mode to -1 to search from the bottom: =XLOOKUP(E2,A2:A20,C2:C20,"Not found",0,-1). In a list sorted by date, that gives the most recent row.

  • XLOOKUP misses the discount when the amount falls between two tiers

    The default is an exact match. Set match mode to -1 to get the next smaller tier. The table does not need to be sorted: =XLOOKUP(B2,E2:E5,F2:F5,0,-1).

  • XLOOKUP gives #VALUE!

    The lookup range and the return range have different sizes, such as A2:A6 and C2:C7. Make both cover the same rows: =XLOOKUP(E2,A2:A6,C2:C6).

Practice XLOOKUP

Related functions