How to Use XLOOKUP in Excel

Use XLOOKUP when you need to find a value in one range and return the related value from another range. The basic formula is:

=XLOOKUP(lookup_value,lookup_array,return_array)

For example, if G3 contains a product code and product codes are in A5:A9, this formula returns the matching price from D5:D9:

=XLOOKUP(G3,$A$5:$A$9,$D$5:$D$9)

With G3 set to P-203, the result is $58.00. XLOOKUP uses an exact match by default, so you do not need to add FALSE as you would with many VLOOKUP examples.

When XLOOKUP is the right formula

XLOOKUP is useful when you have a key, such as a product code or employee ID, and need to retrieve information from the same row. It can return a value from either side of the lookup range.

Build a basic XLOOKUP formula

Use this small product list. The product code is the value you will search for.

Assume the data is in A5:E9, and the code to find is in G3.

1. Select the cell where you want the price to appear.
2. Enter this formula:

=XLOOKUP(G3,$A$5:$A$9,$D$5:$D$9)

3. Press Enter. If G3 contains P-203, Excel returns $58.00.

Read the formula as: find the value in G3, search for it in A5:A9, and return the corresponding value from D5:D9.

Understand the XLOOKUP arguments

The full syntax includes three optional arguments:

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

The dollar signs keep the product-code and price ranges fixed when you copy the formula down. The lookup value remains relative, so a formula copied from row 3 to row 4 can use the code in G4. If absolute and relative references are new to you, see Excel Cell References: Relative, Absolute, and Mixed.

Show a message when no match is found

If XLOOKUP cannot find the lookup value and you leave out the fourth argument, Excel returns #N/A. Add a message as the if_not_found argument when a friendly result is more useful:

=XLOOKUP(G3,$A$5:$A$9,$D$5:$D$9,"Product not found")

With G3 set to P-999, this formula returns Product not found. The message handles a missing match. It does not repair a misspelled range, a mismatched range size, or an unsupported Excel version.

Return a value from the left

XLOOKUP does not require the return range to be on the right. For example, if G4 contains Laptop Stand, this formula searches the Product column and returns the corresponding Product Code:

=XLOOKUP(G4,$B$5:$B$9,$A$5:$A$9,"Product not found")

The result is P-203. This works because you choose the lookup range and return range separately. You do not need to count columns as you do with VLOOKUP.

Common XLOOKUP problems

The formula returns #N/A

Excel did not find an exact match. Check for extra spaces, different punctuation, or a text number versus a numeric value. If a missing item is expected, add the if_not_found message shown above. For a broader explanation of formula errors, see Excel Formula Errors Explained.

The formula returns #VALUE!

Check that the lookup array and return array describe corresponding rows or columns. For this vertical example, $A$5:$A$9 and $D$5:$D$9 both contain five cells. Do not pair a five-cell lookup range with a four-cell return range.

Excel does not recognize XLOOKUP

Microsoft Support states that XLOOKUP is not available in Excel 2016 or Excel 2019. If your version does not recognize the function, use a compatible alternative such as VLOOKUP or an INDEX and MATCH combination, or open the workbook in a version that supports XLOOKUP.

The result is not the record you expected

Check for duplicate lookup values. XLOOKUP returns the first match it finds by default. If each product code should be unique, clean the source list rather than relying on the first duplicate.

Key points to remember

• Use XLOOKUP to find a value in one range and return a related value from another.
• The three required arguments are the value to find, the range to search, and the range to return.
• Exact match is the default, and the fourth argument can replace a missing-match #N/A with a clear message.
• Keep lookup and return ranges fixed when copying the formula, and keep the lookup cell relative when each row has a different code.
• Check your Excel version and source data before changing the formula to hide an error.

Source and further reading

Microsoft documents the XLOOKUP syntax, arguments, exact-match default, first-match behavior, and version note. For a comparison with older lookup functions, see Microsoft’s guide to looking up values with VLOOKUP, INDEX, and MATCH.

For The Excel Guide’s approach to researching and checking tutorials, read our Editorial Policy.