---
title: "XLOOKUP practice: 5 exercises with answers | ExcelSavvy"
description: "Practice XLOOKUP in Excel and Google Sheets: look up a price, set a not-found value, find the latest match and use two criteria."
canonical_url: "https://excelsavvy.com/practice/xlookup"
meta:
  "og:description": "Practice XLOOKUP in Excel and Google Sheets: look up a price, set a not-found value, find the latest match and use two criteria."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBwcmFjdGljZTogNSBleGVyY2lzZXMgd2l0aCBhbnN3ZXJz%2Cdescription_~UHJhY3RpY2UgWExPT0tVUCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogbG9vayB1cCBhIHByaWNlLCBzZXQgYSBub3QtZm91bmQgdmFsdWUsIGZpbmQgdGhlIGxhdGVzdCBtYXRjaCBhbmQgdXNlIHR3byBjcml0ZXJpYS4%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJDIiwiRiJdLCJyb3dzIjpbeyJuIjoxLCJjZWxscyI6WyJTS1UiLCJQcm9kdWN0IiwiUHJpY2UiLCJQcmljZSJdfSx7Im4iOjIsImNlbGxzIjpbIlAtMTAwIiwiRGVzayIsIjE0OTkiLCIiXX0seyJuIjozLCJjZWxscyI6WyJQLTEwMSIsIkNoYWlyIiwiODk5IiwiIl19LHsibiI6NCwiY2VsbHMiOlsiUC0xMDIiLCJMYW1wIiwiMjQ5IiwiIl19LHsibiI6NSwiY2VsbHMiOlsiUC0xMDMiLCJTaGVsZiIsIjY0OSIsIiJdfSx7Im4iOjYsImNlbGxzIjpbIlAtMTA0IiwiTW9uaXRvciIsIjE3OTkiLCIiXX1dLCJ0YXJnZXQiOlsxLDNdfX0%2Cp_Ii9wcmFjdGljZS94bG9va3VwIg%2Cs_feTT-FV3z6dgoSl3.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "XLOOKUP practice: 5 exercises with answers | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBwcmFjdGljZTogNSBleGVyY2lzZXMgd2l0aCBhbnN3ZXJz%2Cdescription_~UHJhY3RpY2UgWExPT0tVUCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogbG9vayB1cCBhIHByaWNlLCBzZXQgYSBub3QtZm91bmQgdmFsdWUsIGZpbmQgdGhlIGxhdGVzdCBtYXRjaCBhbmQgdXNlIHR3byBjcml0ZXJpYS4%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJDIiwiRiJdLCJyb3dzIjpbeyJuIjoxLCJjZWxscyI6WyJTS1UiLCJQcm9kdWN0IiwiUHJpY2UiLCJQcmljZSJdfSx7Im4iOjIsImNlbGxzIjpbIlAtMTAwIiwiRGVzayIsIjE0OTkiLCIiXX0seyJuIjozLCJjZWxscyI6WyJQLTEwMSIsIkNoYWlyIiwiODk5IiwiIl19LHsibiI6NCwiY2VsbHMiOlsiUC0xMDIiLCJMYW1wIiwiMjQ5IiwiIl19LHsibiI6NSwiY2VsbHMiOlsiUC0xMDMiLCJTaGVsZiIsIjY0OSIsIiJdfSx7Im4iOjYsImNlbGxzIjpbIlAtMTA0IiwiTW9uaXRvciIsIjE3OTkiLCIiXX1dLCJ0YXJnZXQiOlsxLDNdfX0%2Cp_Ii9wcmFjdGljZS94bG9va3VwIg%2Cs_feTT-FV3z6dgoSl3.png"
---

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

# **XLOOKUP practice exercises**

XLOOKUP replaces VLOOKUP and INDEX/MATCH in Excel 365 and Google Sheets: separate lookup and return ranges, exact match by default and a built-in value for not found.

[**Start the first exercise**](https://excelsavvy.com/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/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/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/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/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/practice/xlookup/xlookup-multiple-criteria)

## **Functions in this topic**

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

## **Read the guide**

- [**Excel test for a job interview: what it looks like and how to practice**](https://excelsavvy.com/learn/excel-for-job-interviews)
- [**VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use**](https://excelsavvy.com/learn/vlookup-vs-xlookup-vs-index-match)
- [**Excel for finance and accounting: the skills you actually use**](https://excelsavvy.com/learn/excel-for-finance)
- [**Excel inventory data: free practice file with tasks and answers**](https://excelsavvy.com/learn/excel-inventory-data)
- [**Best advanced Excel courses in 2026: what to learn and where**](https://excelsavvy.com/learn/best-advanced-excel-courses)
- [**Udemy vs Coursera vs LinkedIn Learning for Excel: which to pick**](https://excelsavvy.com/learn/udemy-vs-coursera-vs-linkedin-learning-excel)

## **Questions**

<details>

<summary>

### Why does XLOOKUP show #NAME? in Excel?

</summary>

XLOOKUP only exists in Excel 365, Excel 2021 and later. In Excel 2019, 2016 and older it shows #NAME?, and in a file saved by a newer version you may see \_xlfn.XLOOKUP in the formula. Use VLOOKUP or INDEX and MATCH in files that must work on older versions.

</details>

<details>

<summary>

### How do I use XLOOKUP with multiple criteria?

</summary>

Multiply the tests and look up 1: =XLOOKUP(1,(A2:A6=F1)\*(B2:B6=F2),C2:C6). The product is 1 only on rows where both tests are true, so XLOOKUP finds the first row that matches both the region and the product. You can also join the columns with &amp;, as in =XLOOKUP(F1&amp;F2,A2:A6&amp;B2:B6,C2:C6).

</details>

<details>

<summary>

### Can XLOOKUP return several columns at once?

</summary>

Yes. Give it a return range with more than one column, such as =XLOOKUP(A2,E2:E50,F2:H50), and the matching row spills into the cells to the right. Make sure those cells are empty, or Excel shows an error instead of the result.

</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/practice/xlookup#webpage","description":"Practice XLOOKUP in Excel and Google Sheets: look up a price, set a not-found value, find the latest match and use two criteria.","name":"XLOOKUP practice: 5 exercises with answers","url":"https://excelsavvy.com/practice/xlookup","@type":["WebPage","CollectionPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/practice/xlookup#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/practice/xlookup#/schema/question/1"},{"@id":"https://excelsavvy.com/practice/xlookup#/schema/question/2"},{"@id":"https://excelsavvy.com/practice/xlookup#/schema/question/3"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/practice/xlookup"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/practice/xlookup#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel practice exercises","item":"https://excelsavvy.com/practice","position":2},{"@type":"ListItem","name":"XLOOKUP practice: 5 exercises with answers","position":3}]},{"@id":"https://excelsavvy.com/practice/xlookup#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Why does XLOOKUP show #NAME? in Excel?","acceptedAnswer":{"@type":"Answer","text":"XLOOKUP only exists in Excel 365, Excel 2021 and later. In Excel 2019, 2016 and older it shows #NAME?, and in a file saved by a newer version you may see _xlfn.XLOOKUP in the formula. Use VLOOKUP or INDEX and MATCH in files that must work on older versions."}},{"@id":"https://excelsavvy.com/practice/xlookup#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I use XLOOKUP with multiple criteria?","acceptedAnswer":{"@type":"Answer","text":"Multiply the tests and look up 1: =XLOOKUP(1,(A2:A6=F1)*(B2:B6=F2),C2:C6). The product is 1 only on rows where both tests are true, so XLOOKUP finds the first row that matches both the region and the product. You can also join the columns with &, as in =XLOOKUP(F1&F2,A2:A6&B2:B6,C2:C6)."}},{"@id":"https://excelsavvy.com/practice/xlookup#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"Can XLOOKUP return several columns at once?","acceptedAnswer":{"@type":"Answer","text":"Yes. Give it a return range with more than one column, such as =XLOOKUP(A2,E2:E50,F2:H50), and the matching row spills into the cells to the right. Make sure those cells are empty, or Excel shows an error instead of the result."}},{"@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.
