---
title: "XLOOKUP in Google Sheets: syntax and examples | ExcelSavvy"
description: "Finds a value in one range and returns the matching item from another. With examples and exercises in Google Sheets."
canonical_url: "https://excelsavvy.com/sheets/functions/xlookup"
meta:
  "og:description": "Finds a value in one range and returns the matching item from another. With examples and exercises in Google Sheets."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBpbiBHb29nbGUgU2hlZXRzOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_Finds%2Ba%2Bvalue%2Bin%2Bone%2Brange%2Band%2Breturns%2Bthe%2Bmatching%2Bitem%2Bfrom%2Banother.%2BWith%2Bexamples%2Band%2Bexercises%2Bin%2BGoogle%2BSheets.%2Ceyebrow_Google%2BSheets%2Bpractice%2Bexercises%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9zaGVldHMvZnVuY3Rpb25zL3hsb29rdXAi%2Cs_bJB2u7wucOGcr6vV.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "XLOOKUP in Google Sheets: syntax and examples | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBpbiBHb29nbGUgU2hlZXRzOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_Finds%2Ba%2Bvalue%2Bin%2Bone%2Brange%2Band%2Breturns%2Bthe%2Bmatching%2Bitem%2Bfrom%2Banother.%2BWith%2Bexamples%2Band%2Bexercises%2Bin%2BGoogle%2BSheets.%2Ceyebrow_Google%2BSheets%2Bpractice%2Bexercises%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9zaGVldHMvZnVuY3Rpb25zL3hsb29rdXAi%2Cs_bJB2u7wucOGcr6vV.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **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.

=

XLOOKUP(

## **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])
```

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

## **Examples**

D1`=XLOOKUP(<sup>1</sup>"Plum",<sup>2</sup>A2:A4,<sup>3</sup>B2:B4)`

|  | A | B | C | D |
| --- | --- | --- | --- | --- |
| 1 | Fruit | Price |  | **5** |
| 2 | Apple | 4 |  | |
| 3 | Pear | 6 |  | |
| 4 | Plum | 5 |  | |

**In plain words:** Find <sup>1</sup>Plum in <sup>2</sup>the fruit in A2:A4 and return <sup>3</sup>its price from B2:B4.

D1=XLOOKUP("Kiwi",A2:A4,B2:B4,"Not found")

|  | A | B | C | D |
| --- | --- | --- | --- | --- |
| 1 | Fruit | Price |  | **Not found** |
| 2 | Apple | 4 |  | |
| 3 | Pear | 6 |  | |
| 4 | Plum | 5 |  | |

E1`=XLOOKUP(<sup>1</sup>1,<sup>2</sup>(A2:A5="North")*(B2:B5="Bags"),<sup>3</sup>C2:C5)`

|  | A | B | C | D | E |
| --- | --- | --- | --- | --- | --- |
| 1 | Region | Product | Sales |  | **150** |
| 2 | North | Shoes | 420 |  | |
| 3 | South | Bags | 310 |  | |
| 4 | North | Bags | 150 |  | |
| 5 | East | Shoes | 280 |  | |

**In plain words:** Find <sup>1</sup>a 1 in <sup>2</sup>the rows where the region is North and the product is Bags and return <sup>3</sup>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**

- [**1****Look up a price with XLOOKUP**→](https://excelsavvy.com/sheets/practice/xlookup/xlookup)
- [**2****XLOOKUP with a not-found value**→](https://excelsavvy.com/sheets/practice/xlookup/xlookup-if-not-found)
- [**3****Find the latest match with XLOOKUP**→](https://excelsavvy.com/sheets/practice/xlookup/xlookup-last-match)
- [**4****The smallest box that fits with XLOOKUP**→](https://excelsavvy.com/sheets/practice/xlookup/xlookup-next-larger)
- [**5****XLOOKUP with two criteria**→](https://excelsavvy.com/sheets/practice/xlookup/xlookup-multiple-criteria)

## **Related functions**

- [**VLOOKUP**](https://excelsavvy.com/sheets/functions/vlookup)
- [**INDEX**](https://excelsavvy.com/sheets/functions/index)
- [**MATCH**](https://excelsavvy.com/sheets/functions/match)
- [**XMATCH**](https://excelsavvy.com/functions/xmatch)

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/sheets/functions/xlookup#webpage","@type":"WebPage","dateModified":"2026-11-16","description":"Finds a value in one range and returns the matching item from another. With examples and exercises in Google Sheets.","name":"XLOOKUP in Google Sheets: syntax and examples","url":"https://excelsavvy.com/sheets/functions/xlookup","breadcrumb":{"@id":"https://excelsavvy.com/sheets/functions/xlookup#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/sheets/functions/xlookup"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/sheets/functions/xlookup#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Google Sheets practice exercises","item":"https://excelsavvy.com/sheets","position":2},{"@type":"ListItem","name":"XLOOKUP in Google Sheets: syntax and examples","position":3}]},{"@id":"https://excelsavvy.com/sheets/functions/xlookup#article","dateModified":"2026-11-16","description":"Finds a value in one range and returns the matching item from another. With examples and exercises in Google Sheets.","headline":"XLOOKUP in Google Sheets: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBpbiBHb29nbGUgU2hlZXRzOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_Finds%2Ba%2Bvalue%2Bin%2Bone%2Brange%2Band%2Breturns%2Bthe%2Bmatching%2Bitem%2Bfrom%2Banother.%2BWith%2Bexamples%2Band%2Bexercises%2Bin%2BGoogle%2BSheets.%2Ceyebrow_Google%2BSheets%2Bpractice%2Bexercises%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9zaGVldHMvZnVuY3Rpb25zL3hsb29rdXAi%2Cs_bJB2u7wucOGcr6vV.png","@type":["Article","TechArticle"],"author":{"@id":"https://excelsavvy.com/#identity"},"image":{"@id":"https://excelsavvy.com/#/schema/image/1"},"isPartOf":{"@id":"https://excelsavvy.com/sheets/functions/xlookup#webpage"},"mainEntityOfPage":{"@id":"https://excelsavvy.com/sheets/functions/xlookup#webpage"},"publisher":{"@id":"https://excelsavvy.com/#identity"}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"},{"@id":"https://excelsavvy.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBpbiBHb29nbGUgU2hlZXRzOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_Finds%2Ba%2Bvalue%2Bin%2Bone%2Brange%2Band%2Breturns%2Bthe%2Bmatching%2Bitem%2Bfrom%2Banother.%2BWith%2Bexamples%2Band%2Bexercises%2Bin%2BGoogle%2BSheets.%2Ceyebrow_Google%2BSheets%2Bpractice%2Bexercises%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9zaGVldHMvZnVuY3Rpb25zL3hsb29rdXAi%2Cs_bJB2u7wucOGcr6vV.png","inLanguage":"en-US","url":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBpbiBHb29nbGUgU2hlZXRzOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_Finds%2Ba%2Bvalue%2Bin%2Bone%2Brange%2Band%2Breturns%2Bthe%2Bmatching%2Bitem%2Bfrom%2Banother.%2BWith%2Bexamples%2Band%2Bexercises%2Bin%2BGoogle%2BSheets.%2Ceyebrow_Google%2BSheets%2Bpractice%2Bexercises%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9zaGVldHMvZnVuY3Rpb25zL3hsb29rdXAi%2Cs_bJB2u7wucOGcr6vV.png"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
