--- title: "XLOOKUP function in Excel: syntax and examples | PracticeExcel" description: "How to use XLOOKUP in Excel 365 and Google Sheets: lookups in any direction with a built-in not-found value." canonical_url: "https://practiceexcelonline.com/functions/xlookup" locale: en meta: "og:description": "How to use XLOOKUP in Excel 365 and Google Sheets: lookups in any direction with a built-in not-found value." "og:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBYTE9PS1VQIGluIEV4Y2VsIDM2NSBhbmQgR29vZ2xlIFNoZWV0czogbG9va3VwcyBpbiBhbnkgZGlyZWN0aW9uIHdpdGggYSBidWlsdC1pbiBub3QtZm91bmQgdmFsdWUu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9mdW5jdGlvbnMveGxvb2t1cCI%2Cs_CQ4y1K759qp4Jx1K.png" "og:image:height": "600" "og:image:type": image/png "og:image:width": "1200" "og:title": "XLOOKUP function in Excel: syntax and examples | PracticeExcel" "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBYTE9PS1VQIGluIEV4Y2VsIDM2NSBhbmQgR29vZ2xlIFNoZWV0czogbG9va3VwcyBpbiBhbnkgZGlyZWN0aW9uIHdpdGggYSBidWlsdC1pbiBub3QtZm91bmQgdmFsdWUu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9mdW5jdGlvbnMveGxvb2t1cCI%2Cs_CQ4y1K759qp4Jx1K.png" --- > Index of every page on this site: [llms.txt](https://practiceexcelonline.com/llms.txt) # **The XLOOKUP function** 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( ## **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(1"Plum",2A2:A4,3B2:B4)` | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | Fruit | Price | | **5** | | 2 | Apple | 4 | | | | 3 | Pear | 6 | | | | 4 | Plum | 5 | | | **In plain words:** Find 1Plum in 2the fruit in A2:A4 and return 3its 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 | | | ## **Practice XLOOKUP** - [**1****Look up a price with XLOOKUP**→](https://practiceexcelonline.com/practice/xlookup/xlookup) - [**2****XLOOKUP with a not-found value**→](https://practiceexcelonline.com/practice/xlookup/xlookup-if-not-found) - [**3****Find the latest match with XLOOKUP**→](https://practiceexcelonline.com/practice/xlookup/xlookup-last-match) - [**4****The smallest box that fits with XLOOKUP**→](https://practiceexcelonline.com/practice/xlookup/xlookup-next-larger) ## **Read the guide** - [**Excel for job interviews: what gets tested and how to prepare**](https://practiceexcelonline.com/learn/excel-for-job-interviews) - [**VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use**](https://practiceexcelonline.com/learn/vlookup-vs-xlookup-vs-index-match) - [**Excel for finance and accounting: the skills you actually use**](https://practiceexcelonline.com/learn/excel-for-finance) - [**Google Sheets vs Excel: what is the same, what is different, and how to switch**](https://practiceexcelonline.com/learn/google-sheets-vs-excel) - [**How to create a drop-down list in Excel, and make it update itself**](https://practiceexcelonline.com/learn/drop-down-list-excel) ## **Questions**
### Which Excel versions have XLOOKUP? Excel for Microsoft 365, Excel 2021 and later, Excel for the web and Google Sheets. Excel 2019 and older do not.
## **Related functions** - [**VLOOKUP**](https://practiceexcelonline.com/functions/vlookup) - [**INDEX**](https://practiceexcelonline.com/functions/index) - [**MATCH**](https://practiceexcelonline.com/functions/match) - [**XMATCH**](https://practiceexcelonline.com/functions/xmatch) ```json {"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.com/functions/xlookup#webpage","description":"How to use XLOOKUP in Excel 365 and Google Sheets: lookups in any direction with a built-in not-found value.","name":"XLOOKUP function in Excel: syntax and examples","url":"https://practiceexcelonline.com/functions/xlookup","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/functions/xlookup#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"mainEntity":[{"@id":"https://practiceexcelonline.com/functions/xlookup#/schema/question/1"}],"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/functions/xlookup"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/functions/xlookup#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://practiceexcelonline.com/functions","position":2},{"@type":"ListItem","name":"XLOOKUP function in Excel: syntax and examples","position":3}]},{"@id":"https://practiceexcelonline.com/functions/xlookup#article","description":"How to use XLOOKUP in Excel 365 and Google Sheets: lookups in any direction with a built-in not-found value.","headline":"XLOOKUP function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBYTE9PS1VQIGluIEV4Y2VsIDM2NSBhbmQgR29vZ2xlIFNoZWV0czogbG9va3VwcyBpbiBhbnkgZGlyZWN0aW9uIHdpdGggYSBidWlsdC1pbiBub3QtZm91bmQgdmFsdWUu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9mdW5jdGlvbnMveGxvb2t1cCI%2Cs_CQ4y1K759qp4Jx1K.png","@type":["Article","TechArticle"],"author":{"@id":"https://practiceexcelonline.com/#identity"},"image":{"@id":"https://practiceexcelonline.com/#/schema/image/1"},"isPartOf":{"@id":"https://practiceexcelonline.com/functions/xlookup#webpage"},"mainEntityOfPage":{"@id":"https://practiceexcelonline.com/functions/xlookup#webpage"},"publisher":{"@id":"https://practiceexcelonline.com/#identity"}},{"@id":"https://practiceexcelonline.com/functions/xlookup#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Which Excel versions have XLOOKUP?","acceptedAnswer":{"@type":"Answer","text":"Excel for Microsoft 365, Excel 2021 and later, Excel for the web and Google Sheets. Excel 2019 and older do not."}},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBYTE9PS1VQIGluIEV4Y2VsIDM2NSBhbmQgR29vZ2xlIFNoZWV0czogbG9va3VwcyBpbiBhbnkgZGlyZWN0aW9uIHdpdGggYSBidWlsdC1pbiBub3QtZm91bmQgdmFsdWUu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9mdW5jdGlvbnMveGxvb2t1cCI%2Cs_CQ4y1K759qp4Jx1K.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~WExPT0tVUCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBYTE9PS1VQIGluIEV4Y2VsIDM2NSBhbmQgR29vZ2xlIFNoZWV0czogbG9va3VwcyBpbiBhbnkgZGlyZWN0aW9uIHdpdGggYSBidWlsdC1pbiBub3QtZm91bmQgdmFsdWUu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~WExPT0tVUChsb29rdXBfdmFsdWUsIGxvb2t1cF9hcnJheSwgcmV0dXJuX2FycmF5LCBbaWZfbm90X2ZvdW5kXSwgW21hdGNoX21vZGVdLCBbc2VhcmNoX21vZGVdKQ%2Cp_Ii9mdW5jdGlvbnMveGxvb2t1cCI%2Cs_CQ4y1K759qp4Jx1K.png"}]} ``` ## Sitemap See the full [sitemap](https://practiceexcelonline.com/sitemap.md) for all pages.