---
title: "XLOOKUP in Google Sheets: practice exercises | ExcelSavvy"
description: "Practice XLOOKUP in Google Sheets: look up in any direction with a value for missing matches, in exercises that are checked as you type."
canonical_url: "https://excelsavvy.com/sheets/practice/xlookup"
meta:
  "og:description": "Practice XLOOKUP in Google Sheets: look up in any direction with a value for missing matches, in exercises that are checked as you type."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBpbiBHb29nbGUgU2hlZXRzOiBwcmFjdGljZSBleGVyY2lzZXM%2Cdescription_~UHJhY3RpY2UgWExPT0tVUCBpbiBHb29nbGUgU2hlZXRzOiBsb29rIHVwIGluIGFueSBkaXJlY3Rpb24gd2l0aCBhIHZhbHVlIGZvciBtaXNzaW5nIG1hdGNoZXMsIGluIGV4ZXJjaXNlcyB0aGF0IGFyZSBjaGVja2VkIGFzIHlvdSB0eXBlLg%2Ceyebrow_Google%2BSheets%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJDIiwiRiJdLCJyb3dzIjpbeyJuIjoxLCJjZWxscyI6WyJTS1UiLCJQcm9kdWN0IiwiUHJpY2UiLCJQcmljZSJdfSx7Im4iOjIsImNlbGxzIjpbIlAtMTAwIiwiRGVzayIsIjE0OTkiLCIiXX0seyJuIjozLCJjZWxscyI6WyJQLTEwMSIsIkNoYWlyIiwiODk5IiwiIl19LHsibiI6NCwiY2VsbHMiOlsiUC0xMDIiLCJMYW1wIiwiMjQ5IiwiIl19LHsibiI6NSwiY2VsbHMiOlsiUC0xMDMiLCJTaGVsZiIsIjY0OSIsIiJdfSx7Im4iOjYsImNlbGxzIjpbIlAtMTA0IiwiTW9uaXRvciIsIjE3OTkiLCIiXX1dLCJ0YXJnZXQiOlsxLDNdfX0%2Cp_Ii9zaGVldHMvcHJhY3RpY2UveGxvb2t1cCI%2Cs_qpyiKkA7LAu5sJg5.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "XLOOKUP in Google Sheets: practice exercises | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBpbiBHb29nbGUgU2hlZXRzOiBwcmFjdGljZSBleGVyY2lzZXM%2Cdescription_~UHJhY3RpY2UgWExPT0tVUCBpbiBHb29nbGUgU2hlZXRzOiBsb29rIHVwIGluIGFueSBkaXJlY3Rpb24gd2l0aCBhIHZhbHVlIGZvciBtaXNzaW5nIG1hdGNoZXMsIGluIGV4ZXJjaXNlcyB0aGF0IGFyZSBjaGVja2VkIGFzIHlvdSB0eXBlLg%2Ceyebrow_Google%2BSheets%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJDIiwiRiJdLCJyb3dzIjpbeyJuIjoxLCJjZWxscyI6WyJTS1UiLCJQcm9kdWN0IiwiUHJpY2UiLCJQcmljZSJdfSx7Im4iOjIsImNlbGxzIjpbIlAtMTAwIiwiRGVzayIsIjE0OTkiLCIiXX0seyJuIjozLCJjZWxscyI6WyJQLTEwMSIsIkNoYWlyIiwiODk5IiwiIl19LHsibiI6NCwiY2VsbHMiOlsiUC0xMDIiLCJMYW1wIiwiMjQ5IiwiIl19LHsibiI6NSwiY2VsbHMiOlsiUC0xMDMiLCJTaGVsZiIsIjY0OSIsIiJdfSx7Im4iOjYsImNlbGxzIjpbIlAtMTA0IiwiTW9uaXRvciIsIjE3OTkiLCIiXX1dLCJ0YXJnZXQiOlsxLDNdfX0%2Cp_Ii9zaGVldHMvcHJhY3RpY2UveGxvb2t1cCI%2Cs_qpyiKkA7LAu5sJg5.png"
---

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

# **XLOOKUP practice in Google Sheets**

XLOOKUP came to Google Sheets in 2022 with the same arguments as in Excel: the value, the range to search, the range to return from, and an optional value for when nothing is found. It looks left as easily as right.

[**Start the first exercise**](https://excelsavvy.com/sheets/practice/xlookup/xlookup)

XLOOKUP(

## **How XLOOKUP works**

XLOOKUP looks up a value in one range and returns the value in the same position from another range. Unlike VLOOKUP, the two ranges are separate, so the answer column can sit to the left of the lookup column, and inserting a column never breaks the formula. It matches exactly by default.

`**=XLOOKUP(A2,$E$2:$E$50,$G$2:$G$50,"Not found")**` finds the ID in A2 in E2:E50 and returns the value next to it in G2:G50, or the text Not found when the ID isn't there.

XLOOKUP is in Excel 365, Excel 2021 and later and in Google Sheets. If you share files with people on older versions, learn VLOOKUP or INDEX and MATCH too. The exercises below cover a basic lookup, your own text when nothing is found, the last match instead of the first, the next larger value, and a lookup on two criteria.

1. [**1****Look up a price with XLOOKUP**In F2, use XLOOKUP to return the Price for the SKU in E2: look for E2 in A2:A6 and return from C2:C6.](https://excelsavvy.com/sheets/practice/xlookup/xlookup)
2. [**2****XLOOKUP with a not-found value****Premium**P-109 is not in the list. In F2, use XLOOKUP to return the price for E2, and use its fourth argument to show "Not found" instead of #N/A.](https://excelsavvy.com/sheets/practice/xlookup/xlookup-if-not-found)
3. [**3****Find the latest match with XLOOKUP****Premium**The price list grows at the bottom each time a price changes. In F2, find the current price of the product in F1, the last match in the list. Search from the bottom with search\_mode -1, the sixth argument: XLOOKUP(F1,B2:B8,C2:C8,,0,-1).](https://excelsavvy.com/sheets/practice/xlookup/xlookup-last-match)
4. [**4****The smallest box that fits with XLOOKUP****Premium**In C2, pick the smallest box that holds the volume in B2. If there is no exact match, XLOOKUP should take the next larger size: match\_mode 1, the fifth argument, XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,1).](https://excelsavvy.com/sheets/practice/xlookup/xlookup-next-larger)
5. [**5****XLOOKUP with two criteria****Premium**In F3, return the sales for the region in F1 and the product in F2. Look up 1 in the product of the two tests: XLOOKUP(1,(A2:A6=F1)\*(B2:B6=F2),C2:C6).](https://excelsavvy.com/sheets/practice/xlookup/xlookup-multiple-criteria)

## **Functions in this topic**

- [**XLOOKUP**](https://excelsavvy.com/sheets/functions/xlookup)

## **Questions**

<details>

<summary>

### Does Google Sheets have XLOOKUP?

</summary>

Yes. Google Sheets added XLOOKUP in 2022, with the same arguments in the same order as Excel, so a formula you write in one works in the other.

</details>

<details>

<summary>

### How do I get the last match with XLOOKUP in Google Sheets?

</summary>

Set search\_mode, the sixth argument, to -1: =XLOOKUP(F1,B2:B8,C2:C8,,0,-1) searches from the bottom, so you get the last match instead of the first. The empty fourth argument keeps the default #N/A when nothing is found. The same formula works in Excel.

</details>

```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/practice/xlookup#webpage","description":"Practice XLOOKUP in Google Sheets: look up in any direction with a value for missing matches, in exercises that are checked as you type.","name":"XLOOKUP in Google Sheets: practice exercises","url":"https://excelsavvy.com/sheets/practice/xlookup","@type":["WebPage","CollectionPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/sheets/practice/xlookup#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/sheets/practice/xlookup#/schema/question/1"},{"@id":"https://excelsavvy.com/sheets/practice/xlookup#/schema/question/2"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/sheets/practice/xlookup"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/sheets/practice/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: practice exercises","position":3}]},{"@id":"https://excelsavvy.com/sheets/practice/xlookup#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Does Google Sheets have XLOOKUP?","acceptedAnswer":{"@type":"Answer","text":"Yes. Google Sheets added XLOOKUP in 2022, with the same arguments in the same order as Excel, so a formula you write in one works in the other."}},{"@id":"https://excelsavvy.com/sheets/practice/xlookup#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I get the last match with XLOOKUP in Google Sheets?","acceptedAnswer":{"@type":"Answer","text":"Set search_mode, the sixth argument, to -1: =XLOOKUP(F1,B2:B8,C2:C8,,0,-1) searches from the bottom, so you get the last match instead of the first. The empty fourth argument keeps the default #N/A when nothing is found. The same formula works in Excel."}},{"@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"}]}
```

## Sitemap

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