How to Use XLOOKUP in Excel: Syntax, Examples and VLOOKUP Differences

shape
shape
shape
shape
shape
shape
shape
shape
How to Use XLOOKUP in Excel: Syntax, Examples and VLOOKUP Differences - featured image

XLOOKUP is the modern lookup function in Excel. It finds a value in one column or row and returns the matching value from another, and it fixes many of the limits of VLOOKUP: it can look to the left, it defaults to an exact match, it does not break when you insert a column, and it has a built-in message for values that are not found. This guide explains the syntax, shows examples with a small employee table that you can check by hand, covers approximate matches for tiered pricing, wildcards, returning several columns, searching from the bottom, and how to move from VLOOKUP to XLOOKUP. It also lists the errors you may meet and how to fix them.

Updated November 2026

In this guide

  1. XLOOKUP syntax
  2. The sample data
  3. Example 1: basic exact match
  4. Example 2: look up to the left
  5. Example 3: handle values that are not found
  6. Example 4: return several columns at once
  7. Example 5: approximate match for tiers
  8. Example 6: wildcards and partial matches
  9. Example 7: search from the bottom
  10. Two-way lookup
  11. XLOOKUP compared with VLOOKUP
  12. Convert a VLOOKUP to XLOOKUP
  13. Common errors and fixes
  14. Practice exercise
  15. Build an invoice lookup with XLOOKUP
  16. Quick recap

XLOOKUP syntax

The syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Lookup_value is what you are searching for. Lookup_array is the single row or column where Excel searches. Return_array is the row or column that holds the answer, and it must be the same size as the lookup array. If_not_found is the text or value to show when there is no match. Match_mode controls exact or approximate matching, and search_mode sets the search direction. XLOOKUP is available in Microsoft 365, Excel 2021 and Excel 2024, but not in Excel 2019 or earlier, so check your version before sharing a file.

The sample data

The examples use an employee table in A1:C6.

IDNameSalary
101Ali4500
102Sara5200
103Omar3800
104Hina4100
105Bilal4500

Example 1: basic exact match

To find the name for ID 103, use =XLOOKUP(103,A2:A6,B2:B6). The result is Omar. To make it flexible, put the ID in a cell such as E1 and use =XLOOKUP(E1,A2:A6,B2:B6). To return the salary instead, change the return array to C2:C6, and the result for ID 103 is 3800. XLOOKUP uses an exact match by default, so you do not need the extra argument that VLOOKUP needs to avoid approximate results.

Example 2: look up to the left

VLOOKUP can only return values to the right of the lookup column. XLOOKUP has no such limit. To find the ID of Hina, use =XLOOKUP("Hina",B2:B6,A2:A6), which returns 104, even though the ID column is to the left of the names. This removes the need for tricks such as INDEX and MATCH or moving columns around. It is the biggest everyday advantage of XLOOKUP over VLOOKUP, as covered in our guide to fixing VLOOKUP problems.

Example 3: handle values that are not found

If the lookup value does not exist, XLOOKUP returns #N/A. Use the fourth argument to show a friendly message: =XLOOKUP(110,A2:A6,B2:B6,"Not found") returns Not found, since ID 110 is not in the table. You do not need to wrap the formula in IFERROR, and the message appears only when there is no match, so other errors are not hidden. Use a blank text, written as two quotation marks, if you prefer an empty cell. Showing a clear message helps clients who use your template to see what went wrong.

Example 4: return several columns at once

The return array can have more than one column, and the result spills into the cells to the right. =XLOOKUP(102,A2:A6,B2:C6) returns Sara and 5200 in two adjacent cells. You can build a lookup card for an employee with one formula. If the cells to the right are not empty, you will see a #SPILL! error, so clear them first. This feature is available because XLOOKUP is a dynamic array function, similar to the functions described in our FILTER function guide.

Example 5: approximate match for tiers

XLOOKUP can look up a value in a table of ranges, such as discount tiers. Suppose the tier table has minimum quantities 0, 10, 50 and 100 in E2:E5, with discounts 0, 5 percent, 10 percent and 15 percent in F2:F5. For an order of 60 units, use =XLOOKUP(60,E2:E5,F2:F5,,-1). The match mode of -1 means exact match or the next smaller item, so the result is the 10 percent discount for the 50 tier. For a quantity of 100, the result is 15 percent, and for 9 it is 0, because 0 is the next smaller tier. The two commas in a row skip the if_not_found argument. Unlike the approximate match in VLOOKUP, the lookup column does not need to be sorted.

Example 6: wildcards and partial matches

Set the match mode to 2 to use wildcards. =XLOOKUP("Hi*",B2:B6,C2:C6,"None",2) finds the first name that starts with Hi, which is Hina, and returns 4100. The asterisk stands for any number of characters, and a question mark stands for one character. You can also combine text, such as ="*"&E1&"*", to find a name that contains the word in cell E1. Wildcards are helpful with messy data, such as product names that include extra words.

Example 7: search from the bottom

By default XLOOKUP returns the first match from the top. If your list has repeated values, and you want the last one, set the search mode to -1. In our table, 4500 appears for both Ali and Bilal. =XLOOKUP(4500,C2:C6,B2:B6) returns Ali, the first match, while =XLOOKUP(4500,C2:C6,B2:B6,,0,-1) returns Bilal, the last match. This is useful for finding the latest entry in a log, where new records are added at the bottom, such as the last price paid for a product.

Two-way lookup

You can nest two XLOOKUP functions to look up both a row and a column. Suppose a table has products down column A and months across row 1. =XLOOKUP(E1,A2:A10,XLOOKUP(F1,B1:M1,B2:M10)) finds the product in E1 and the month in F1, and returns the value at their crossing. The inner XLOOKUP returns the column of the chosen month, and the outer one picks the row. This replaces the older INDEX and MATCH combination and is easier to read once you know the pattern.

XLOOKUP compared with VLOOKUP

The table summarizes the main differences.

FeatureVLOOKUPXLOOKUP
Look to the leftNoYes
Default matchApproximateExact
Insert a columnCan break the formulaSafe
Not found messageNeeds IFERRORBuilt in
Return many columnsNoYes
Search from bottomNoYes
Older Excel versionsAllMicrosoft 365, 2021 and 2024

If the file will be opened in Excel 2019 or earlier, use VLOOKUP or INDEX and MATCH, since XLOOKUP will show a #NAME? error there.

Convert a VLOOKUP to XLOOKUP

A VLOOKUP such as =VLOOKUP(E1,A2:C6,3,FALSE) becomes =XLOOKUP(E1,A2:A6,C2:C6). You now point to the exact column to search and the exact column to return, instead of counting columns. If you later insert a column between them, the references move automatically. Convert your older templates step by step, test each result against the old formula, and keep the old formula in a hidden column until you are sure that the new one matches.

Common errors and fixes

The #N/A error means no match was found, so check for spaces and the data type. A number stored as text will not match a real number, so convert it, as shown in our guide to converting text to numbers in Excel. The #VALUE! error appears when the lookup and return arrays have different sizes. The #SPILL! error means the result has no room to spill. The #NAME? error means that your Excel version does not support XLOOKUP. Hidden trailing spaces are a very common cause, so use TRIM on the lookup value or follow our guide to removing spaces in Excel.

Practice exercise

Using the sample table, try these. Find the salary for ID 104, which is 4100. Find the ID of Bilal, which is 105. Look up ID 200 with the message Missing. Return the name and salary together for ID 101, which gives Ali and 4500. Find the last person with a salary of 4500, which is Bilal. Then build the discount tier table and test quantities 5, 10, 49, 50 and 120, which should give 0, 5 percent, 5 percent, 10 percent and 15 percent. Check every answer by hand before you trust the formula.

Build an invoice lookup with XLOOKUP

A common client job is an invoice sheet where the user types a product code and the price and description appear. Keep a Products sheet with the code in column A, the description in column B and the unit price in column C. On the invoice, put the code in A5, and in B5 use =XLOOKUP(A5,Products!A:A,Products!B:B,"Unknown code"). In C5 use the same formula with Products!C:C to return the price. Multiply by the quantity in D5 with =C5*D5, and add a drop-down list for the code, as shown in our drop-down list guide, so that users cannot type a wrong code. Because XLOOKUP points to whole columns, new products added at the bottom of the Products sheet are found automatically, and the message Unknown code tells the user at once when a code is missing. Test the sheet with a valid code, an invalid code and a blank cell, so that all three cases show a clear result.

Quick recap

XLOOKUP searches one column and returns from another, defaults to an exact match, can look left, returns several columns, offers a not-found message and supports wildcards and reverse search. Use match mode -1 for tiers, and search mode -1 for the latest match. It needs Microsoft 365, Excel 2021 or Excel 2024, so use VLOOKUP or INDEX and MATCH if the file must open in older versions.

Frequently asked questions

What is XLOOKUP in Excel?

XLOOKUP finds a value in a row or column and returns a matching value from another row or column.

Is XLOOKUP better than VLOOKUP?

In most cases yes. It looks left, defaults to exact match and does not break when columns are inserted.

Which versions of Excel have XLOOKUP?

Microsoft 365, Excel 2021 and Excel 2024.

How do I avoid #N/A in XLOOKUP?

Use the fourth argument, if_not_found, such as “Not found”.

Can XLOOKUP return more than one column?

Yes. Use a return array with several columns and the result spills.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *