XLOOKUP Function in Excel: Syntax, Examples and Free Practice
XLOOKUP is an Excel Lookup & Reference function. The modern replacement for VLOOKUP — searches any column and returns from any other, no column counting needed.
XLOOKUP syntax
Arguments
lookup_value: The value you are searching forlookup_array: The range to search withinreturn_array: The range to return matching values fromif_not_found: What to return if no match is found (optional) (optional)
When to use XLOOKUP
XLOOKUP is used to pull matching data from one part of a workbook into another.
Practice XLOOKUP online, free
Open the live Excel-style spreadsheet and try XLOOKUP yourself. No Excel install or sign-up needed to start.
Practice XLOOKUP now →Learn more
Read the tutorial: XLOOKUP vs VLOOKUP.
XLOOKUP practice questions
- Using XLOOKUP, find the price of the SKU typed in E2 and put it in F2.
- Find the name of the employee whose ID is typed in E2 and put it in F2.
- Using XLOOKUP, find the roll number of the student named in E2 and put it in F2. The…
- The code in D2 is not in the list. Make the formula show Not listed instead of an error…
- Using XLOOKUP, find the latest (last) amount paid by the customer typed in D2 and put it…
- Using XLOOKUP with an approximate match, find the commission rate for each rep from the…
- What share of total sales does the region typed in D2 have? Give it as a decimal and put…
- Using XLOOKUP, find the total cost of the quantity in F2 of the product typed in E2 and…
- Find the name of the student with the highest score and put it in D2.
- Using XLOOKUP with a wildcard, find the price of the first product whose name contains…
- Customers are in A2:B6. IDs to look up are in D2:D5. Show the city in F2:F5, or Unknown…
More Lookup & Reference functions
Frequently asked questions
What does the XLOOKUP function do in Excel?
The modern replacement for VLOOKUP — searches any column and returns from any other, no column counting needed.
What is the syntax of XLOOKUP in Excel?
The syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]). lookup_value: The value you are searching for; lookup_array: The range to search within; return_array: The range to return matching values from; if_not_found: What to return if no match is found (optional) (optional).
Where can I practice XLOOKUP online for free?
Open the free live spreadsheet on Cellgrids and try XLOOKUP yourself. No Excel install is needed and you can start without signing up.
All Excel formulas · Cheat sheet · Excel exercises · Lookup and Reference functions · Excel case studies · Excel tutorials · Excel shortcuts