XLOOKUP in Excel: 10 Powerful Examples with Step-by-Step Formulas
Learn XLOOKUP in Excel through ten practical examples covering employee records, product lookups, multiple matching conditions, dynamic arrays, approximate matches, invoice transactions and business reporting calculations.
XLOOKUP is one of the most flexible lookup functions available in modern Microsoft Excel. It allows you to search for a value in one range and return related information from another range. It can search vertically or horizontally, retrieve data from either side of the lookup column and handle missing values more conveniently than traditional lookup formulas.
In this comprehensive tutorial, Bikash Sarangi, founder of TechBikash28 and Kalpavriksh Software, explains how XLOOKUP works through realistic business scenarios. Every formula is accompanied by sample data, expected results and a detailed explanation.
What You Will Learn in This Tutorial
- Understand the six XLOOKUP arguments
- Retrieve employee details using Employee ID
- Compare XLOOKUP and VLOOKUP
- Return several columns from one formula
- Search multiple employee IDs together
- Apply two or three lookup conditions
- Retrieve the last matching transaction
- Search product descriptions using wildcards
- Apply discounts using approximate matching
- Perform two-way and SUM calculations
Recommended audience: Excel beginners, data analysts, MIS executives, accountants, HR teams, finance professionals, inventory managers and students.
What Is XLOOKUP in Excel?
XLOOKUP is a lookup and reference function that searches a specified row or column for a lookup value. When the value is found, it returns information from the corresponding position in another selected range.
For example, an HR executive may maintain employee IDs, names, departments and salaries in one worksheet. By entering only an Employee ID, XLOOKUP can retrieve the corresponding name, department or salary. This helps avoid manually searching through hundreds of records.
XLOOKUP Formula Syntax
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
| Argument | Meaning | Required |
|---|---|---|
| lookup_value | The value you want to find | Yes |
| lookup_array | The search range | Yes |
| return_array | The values to return | Yes |
| if_not_found | Optional message if no match exists | No |
| match_mode | Exact, approximate or wildcard matching | No |
| search_mode | Search direction or binary search method | No |
Match Mode and Search Mode
Exact matching is the default. Match mode -1 allows an exact match or the next smaller value, while match mode 1 allows an exact match or the next larger value. Match mode 2 enables wildcard searches involving asterisks and question marks.
Search mode -1 tells Excel to search from the last record toward the first. This is useful when a list contains repeated customer or product values and you need the last matching entry.
Software compatibility: XLOOKUP is supported in Microsoft 365, Excel 2021 and Excel 2024. It is not available as a native function in Excel 2016 or 2019. Some examples in this article also require modern dynamic-array functionality.
Sample Excel Data for XLOOKUP Examples
Before learning advanced formulas, we will prepare two fictional datasets. The first contains employee details, while the second contains product information. These tables will be used throughout the first four examples.
Employee Master
Create a worksheet with the following columns in A1:E1. Enter the employee records in rows 2 to 7.
| Employee ID | Name | Department | Designation | Salary (₹) |
|---|---|---|---|---|
| 1001 | Asha | Sales | Analyst | 48,000 |
| 1002 | Ravi | Finance | Executive | 52,000 |
| 1003 | Neha | Operations | Manager | 65,000 |
| 1004 | Kabir | HR | Specialist | 45,000 |
| 1005 | Meera | Sales | Manager | 72,000 |
| 1006 | Ishan | IT | Developer | 60,000 |
Product Master
Create a separate worksheet containing the following product information in cells A1:D6.
| Product Code | Product Name | Category | Price (₹) |
|---|---|---|---|
| P101 | Laptop | Computers | 58,000 |
| P102 | Wireless Mouse | Accessories | 850 |
| P103 | Keyboard | Accessories | 1,200 |
| P104 | Monitor | Displays | 12,500 |
| P105 | USB Hub | Accessories | 1,600 |
Practice arrangement: Each example below should be treated as a separate worksheet. Place the specified source data in columns A onward and the lookup input cells in column H. This prevents example-specific formulas from interfering with one another.
Basic XLOOKUP to Find Employee Details
The most common use of XLOOKUP is retrieving information using a unique identifier. In an employee database, Employee ID is usually a better lookup field than Employee Name because different employees may share the same name.
Use the Employee Master in A1:E7 and enter Employee ID 1005 in H2. We will retrieve the employee name and salary separately.
Find Employee Name
=XLOOKUP(H2,$A$2:$A$7,$B$2:$B$7,"Employee not found")
Excel searches column A for the ID entered in H2. When 1005 is found, the corresponding name from column B is returned.
Find Employee Salary
=XLOOKUP(H2,$A$2:$A$7,$E$2:$E$7,"Employee not found")
The lookup range remains the same, but the return range changes from column B to column E. This allows a single employee identifier to retrieve different information fields.
If you enter a missing Employee ID, the formula displays "Employee not found". This is easier for users to understand than a raw #N/A error.
XLOOKUP vs VLOOKUP: Left and Right Lookup
VLOOKUP has been widely used for Excel reporting for many years. However, it searches the first column of its selected table and returns data using a column index.
XLOOKUP uses separate search and return ranges. This makes formulas easier to read and allows information to be retrieved from either side of the lookup column.
Use the Product Master in A1:D6 and enter Product Code P103 in H2.
VLOOKUP Formula
=VLOOKUP(H2,$A$2:$D$6,4,FALSE)
Equivalent XLOOKUP Formula
=XLOOKUP(H2,$A$2:$A$6,$D$2:$D$6,"Not Found")
Both formulas return the price of Keyboard. VLOOKUP uses column index 4, while XLOOKUP directly identifies the price column.
Retrieve a Value from the Left
Enter Wireless Mouse in H3. The task is to retrieve its Product Code from column A, which is located to the left of Product Name in column B.
=XLOOKUP(H3,$B$2:$B$6,$A$2:$A$6,"Not Found")
This ability is particularly useful when a business worksheet cannot be reorganized simply to accommodate a lookup formula. The original column order can remain unchanged.
XLOOKUP to Return Multiple Columns
Sometimes one lookup must return several pieces of information. An HR report may require Employee Name, Department, Designation and Salary for one selected Employee ID.
You do not always need to write four different formulas. XLOOKUP can return multiple adjacent columns from a matching record.
Use the Employee Master. Enter 1003 in H2 and place the following formula in I2.
=XLOOKUP(H2,$A$2:$A$7,$B$2:$E$7,"Not Found")
| Name | Department | Designation | Salary |
|---|---|---|---|
| Neha | Operations | Manager | 65,000 |
Because the return range includes four columns, Excel returns four corresponding values from the matching row. The results spill horizontally from I2 through L2.
Important: The spill destination cells must be empty. If any cell in the required output range already contains information, Excel may display #SPILL!.
XLOOKUP with Multiple Lookup Values
Business reports frequently require matching several IDs at once. For example, an HR manager may receive a list of employees selected for a training program. The manager needs to retrieve their names from the master employee database.
Enter the Employee IDs 1002, 1005, 1001 and 1006 into H2:H5. Then enter the formula below in I2.
=XLOOKUP(H2:H5,$A$2:$A$7,$B$2:$B$7,"Not Found")
| Employee ID | Employee Name |
|---|---|
| 1002 | Ravi |
| 1005 | Meera |
| 1001 | Asha |
| 1006 | Ishan |
A dynamic-array-enabled Excel version processes the list of lookup values and returns the matching employee names vertically. This reduces the need to create separate formulas for every ID.
This technique is useful for employee allocation, customer verification, inventory identification and bulk record matching. It is especially convenient when the number of search values changes regularly.
XLOOKUP with Multiple Criteria
A product may have different prices for different customers or geographical regions. Searching only for Product Code might return the wrong price because the product appears several times in the price master.
In this example, we will match three conditions: Customer, Product Code and Region.
| Customer | Product | Region | Price (₹) |
|---|---|---|---|
| Orion | P101 | West | 56,500 |
| Orion | P101 | East | 57,500 |
| Orion | P102 | West | 790 |
| Apex | P101 | West | 56,200 |
| Apex | P102 | East | 820 |
| Zenith | P104 | West | 11,900 |
Enter the dataset into A1:D7. Set H2 to Orion, H3 to P101 and H4 to West.
=XLOOKUP(1,($A$2:$A$7=$H$2)*($B$2:$B$7=$H$3)*($C$2:$C$7=$H$4),$D$2:$D$7,"No matching price")
The formula performs three separate comparisons for every record. Each comparison generates TRUE or FALSE. Multiplication converts these logical results into 1 and 0.
A row returns 1 only when every condition is satisfied. XLOOKUP searches for that 1 and retrieves the corresponding price. This is useful for price lists, rate contracts, procurement schedules and multi-condition master data.
When several rows satisfy the same combination of conditions, ordinary XLOOKUP returns the first matching row. Ensure that the price master is unique or add further conditions where necessary.
Find the Last Matching Record Using XLOOKUP
By default, XLOOKUP searches from the beginning of the lookup array and returns the first match. However, operational reports sometimes require the last recorded transaction for a customer or product.
The reverse search option allows Excel to begin from the bottom of the selected range. This can help retrieve the latest recorded status when the transaction data is correctly ordered by date.
| Date | Invoice | Customer | Status | Amount (₹) |
|---|---|---|---|---|
| 01-Oct-2026 | INV001 | Orion | Sent | 25,000 |
| 03-Oct-2026 | INV002 | Apex | Paid | 18,000 |
| 08-Oct-2026 | INV003 | Orion | Overdue | 35,000 |
| 12-Oct-2026 | INV004 | Zenith | Paid | 22,000 |
| 18-Oct-2026 | INV005 | Orion | Paid | 12,000 |
| 21-Oct-2026 | INV006 | Apex | Sent | 26,000 |
Enter the table in A1:E7 and enter Orion in H2. Use the following formula.
=XLOOKUP(H2,$C$2:$C$7,$D$2:$D$7,"Not Found",0,-1)
Search mode -1 makes Excel search from the last row toward the first. The last Orion record in the displayed dataset has the status Paid.
The last matching row is only the most recent record if the data is correctly ordered. If the table is unsorted, use a date-based approach or sort the transaction list first.
XLOOKUP with Wildcards and Partial Text
Users do not always remember a complete product name. Someone working in a warehouse may know only that an item contains the word Mouse, Monitor or Keyboard.
XLOOKUP supports wildcard matching for text searches. The asterisk represents any number of characters, while the question mark represents one character.
Use the Product Master in A1:D6 and enter mouse in H2.
=XLOOKUP("*"&H2&"*",$B$2:$B$6,$A$2:$A$6,"Not Found",2)
The formula creates the search pattern *mouse*. Match mode 2 enables wildcard matching, allowing the formula to locate Wireless Mouse even though the user entered only part of the name.
This technique is practical for inventory descriptions, item masters, supplier lists and product catalogues. However, XLOOKUP still returns the first qualifying match. Use FILTER when you need all matching products.
XLOOKUP Approximate Match for Discounts
Approximate matching is useful when a result depends on a numerical band rather than a precise value. Common examples include discount structures, incentives, commission percentages and performance grades.
Suppose a company offers different discount rates depending on the customer's total purchase value. The discount table contains minimum sales thresholds.
| Minimum Sales (₹) | Discount Rate |
|---|---|
| 0 | 0% |
| 50,000 | 2% |
| 1,00,000 | 5% |
| 2,00,000 | 8% |
| 3,00,000 | 10% |
Enter the table in A1:B6 with actual percentage values in column B. Enter sales of ₹1,75,000 in H2.
=XLOOKUP(H2,$A$2:$A$6,$B$2:$B$6,"No Band",-1)
Match mode -1 searches for an exact value or the next smaller threshold. Because ₹1,75,000 falls between ₹1,00,000 and ₹2,00,000, the rate for ₹1,00,000 is selected.
Calculate Discount Amount
If the returned discount percentage is stored in H3, enter this formula in H4.
=H2*H3
The formula calculates 5% of ₹1,75,000. This approach can be extended to other tier-based business calculations, provided the thresholds reflect the applicable policy.
Two-Way XLOOKUP with Nested Formulas
A two-way lookup retrieves a value using both a row heading and a column heading. For example, management may want to view February sales for one selected product from a monthly sales report.
Nested XLOOKUP formulas allow Excel to identify the correct month column and then the corresponding product row.
| Product | Jan | Feb | Mar |
|---|---|---|---|
| P101 | 1,50,000 | 1,70,000 | 1,60,000 |
| P102 | 25,000 | 31,000 | 28,000 |
| P103 | 38,000 | 42,000 | 49,000 |
| P104 | 67,000 | 71,000 | 73,000 |
Enter the monthly sales matrix in A1:D5. In H2 enter Product P103 and in H3 enter Feb.
=XLOOKUP(H2,$A$2:$A$5,XLOOKUP(H3,$B$1:$D$1,$B$2:$D$5),"Not Found")
The inner XLOOKUP searches the month headings and returns the February column. The outer XLOOKUP searches for P103 and retrieves its corresponding value from that selected column.
Two-way lookups are useful for monthly revenue, production performance, budget comparisons, departmental targets and other matrix-based reporting systems.
SUM with XLOOKUP: Multiple Values and Records
A common question is whether XLOOKUP can calculate totals. The answer depends on whether you want to add several values from one matching record or total multiple transaction rows.
Method A: SUM with XLOOKUP
Use the monthly sales matrix from Example 09. We want the combined January, February and March sales for Product P101.
Enter P101 in H2.
=SUM(XLOOKUP(H2,$A$2:$A$5,$B$2:$D$5,0))
XLOOKUP returns the three monthly sales values for P101: ₹1,50,000, ₹1,70,000 and ₹1,60,000. SUM adds these returned values to calculate ₹4,80,000.
Method B: Sum All Matching Transaction Rows
Consider a customer who has several invoices. Searching for the customer through ordinary XLOOKUP returns one matching record, not the sum of every invoice.
| Customer | Invoice | Amount (₹) |
|---|---|---|
| Orion | INV101 | 12,500 |
| Apex | INV102 | 18,000 |
| Orion | INV103 | 7,300 |
| Zenith | INV104 | 9,000 |
| Orion | INV105 | 5,400 |
Enter this transaction table in A1:C6. Enter Orion in H3.
=SUMIFS($C$2:$C$6,$A$2:$A$6,$H$3)
SUMIFS checks every transaction row that matches Orion and adds ₹12,500, ₹7,300 and ₹5,400. The result is ₹25,200.
Key learning: Use SUM with XLOOKUP to add several values returned from one matching record. Use SUMIFS when you need to total all transaction rows satisfying the specified criteria.
Common XLOOKUP Errors and Their Solutions
Even correctly written formulas can produce unexpected results when source data contains missing IDs, inconsistent data types, duplicate keys or blocked output cells. The most reliable approach is to identify the cause instead of simply hiding every formula error.
#N/A — No Matching Value
Check whether the lookup value exists in the source. Review leading spaces, different data types and incorrect identifiers. Use if_not_found for an appropriate missing-value message.
#SPILL! — Blocked Array Output
Dynamic-array formulas need empty output cells. Remove any information blocking the spill range. Enter the spilling formula in the normal worksheet grid rather than inside an Excel Table calculated column.
#VALUE! — Incorrect Dimensions
Check that the lookup array and return array have compatible dimensions. For a vertical lookup, their row counts must correspond correctly.
#NAME? — Unrecognized Function
Check the spelling of XLOOKUP and verify the Excel version. Older versions such as Excel 2016 and 2019 do not provide native XLOOKUP.
Incorrect Result from Duplicate IDs
XLOOKUP returns the first match by default. Consider using a unique identifier, additional matching conditions or reverse search depending on the requirement.
Incorrect Approximate Match
Confirm whether the business calculation needs the next smaller or next larger value. Avoid binary search modes unless the source range is sorted correctly.
Best Practices for XLOOKUP Formulas
- Use unique IDs whenever possible
- Keep lookup columns free from unnecessary spaces
- Apply absolute references to fixed data ranges
- Use consistent number and text formats
- Check duplicate values before relying on the first match
- Use appropriate error handling for missing records
- Choose SUMIFS when every qualifying row must be totaled
- Verify formulas using small datasets before deployment
For additional technical documentation, refer to Microsoft Support — XLOOKUP Function .
XLOOKUP in Excel: Frequently Asked Questions
1. What is XLOOKUP used for?
XLOOKUP searches a specified range and returns information from the corresponding position in another range. It is commonly used in employee, product, customer and transaction databases.
2. Is XLOOKUP better than VLOOKUP?
XLOOKUP is generally more flexible because it supports left and right lookups, exact matching by default, custom missing-value results and multiple return columns.
3. Can XLOOKUP use multiple criteria?
Yes. Combine Boolean comparisons for several columns and use the resulting array to find the row where all conditions are satisfied.
4. Can XLOOKUP return multiple columns?
Yes. Select a multi-column return range to retrieve adjacent values from the same matching record. The formula may spill into several cells.
5. Can XLOOKUP find the last match?
Yes. Use search mode -1. It searches the source range from the bottom upward and returns the last matching entry.
6. Can XLOOKUP calculate a total?
SUM can aggregate numeric values returned by XLOOKUP. However, use SUMIFS when you need to total amounts across every matching transaction row.
7. Can XLOOKUP find partial text?
Yes. Enable wildcard matching using match mode 2 and the appropriate wildcard symbols.
8. Is XLOOKUP available in Excel 2019?
No. Native XLOOKUP is not available in Excel 2016 or 2019. Use a supported modern version of Excel.
9. Why does XLOOKUP return #SPILL!?
A formula returning multiple values cannot place its results when destination cells are blocked. Clear the required spill range.
10. Can XLOOKUP replace INDEX and MATCH?
Many traditional INDEX and MATCH lookup tasks can be handled using XLOOKUP. However, the appropriate choice depends on workbook compatibility and the calculation being performed.
Watch the Excel XLOOKUP Video Tutorial
Reading formulas provides a useful foundation, but watching the actual Excel workflow can make the concepts easier to understand. The original TechBikash28 XLOOKUP article linked to the video below.
Excel XLOOKUP Learning Video
A new companion video can demonstrate all ten examples from this article using the same practice workbook. The workbook will help viewers follow each formula, modify inputs and compare the calculated results against expected values.
Free XLOOKUP Excel Practice Workbook
The ten examples in this tutorial are designed to become a reusable Excel exercise workbook. Each worksheet will contain its own source data, input fields, formulas and expected results.
Planned Workbook Contents
- Employee and Product Master tables
- Ten independently prepared XLOOKUP exercises
- Formula explanations and expected outputs
- Editable lookup input cells
- Practice questions for learners
- TechBikash28 learning notes and video references
Workbook: TechBikash28_XLOOKUP_10_Practical_Examples.xlsx
Status: The Excel practice workbook will be linked here once it has been prepared and uploaded to the website.
Explore XLOOKUP and Excel Web Stories
TechBikash28 also provides short visual learning guides. These Web Stories introduce useful Excel functions, reporting techniques and practical methods through illustrated learning sequences.
