XLOOKUP in Excel: How to Build and Use the Formula

Formulas & functions · Updated November 20, 2026

To look up a product price by ID, put the ID to find in E2, the product IDs in A2:A6, and prices in C2:C6. With P104 in E2 and $18.50 in the matching price row, enter =XLOOKUP(E2,A2:A6,C2:C6,"Not found") in F2; Excel returns $18.50. XLOOKUP searches one range and returns the corresponding value from another, including values located to the left of the search range.

Before starting, make sure your Excel version supports the function and that the lookup values and return values are arranged in matching rows or columns. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. Excel 2019 and earlier do not include it. The directions below are for Excel for Microsoft 365 on Windows; the formula works on Mac and in the web app too, though ribbon layouts can differ.

What You’ll Need

  • Excel for Microsoft 365, Excel 2021 or later, or Excel for the web.
  • A range containing the values you want Excel to search, such as product IDs in A2:A6.
  • A corresponding range containing the results you want returned, such as prices in C2:C6.
  • A cell where you will enter the value to look up, and a separate cell for the formula result.

Step-by-Step

  1. Arrange the source data. Put each record on its own row, with a consistent identifier in one column and the information to return in another. For example, use columns A, B and C for Product ID, Product Name and Price. The sample rows might include P101, Notebook, 4.50; P102, Pen Set, 7.25; P103, Desk Lamp, 24.00; P104, Travel Mug, 18.50; and P105, Mouse Pad, 6.00. Put headers in row 1 and data in rows 2 through 6. Avoid blank rows within the list because they can make the range harder to select and maintain.
  2. Choose the lookup and result cells. In E1, enter a heading such as Lookup ID, and in E2 enter the ID you want to find, for example P104. In F1, enter a heading such as Price. This separates the search input from the answer and makes it easy to change the ID without editing the formula. Confirm that the value in E2 is the same type as the IDs in column A: text IDs should be text in both places, and numeric IDs should be numbers in both places.
  3. Select the cell for the answer. Click F2, where the returned price should appear. You can type the formula directly into the cell or into the formula bar above the worksheet. Start with an equals sign so Excel recognizes the entry as a formula, not as ordinary text.
  4. Enter the XLOOKUP formula. For this example, the lookup value is E2, the IDs are A2:A6, and the prices are C2:C6. Enter =XLOOKUP(E2,A2:A6,C2:C6,"Not found"). The arguments are in this order: value to find, range to search, range to return a result from, and optional message to show if there is no match. Because P104 appears on the same row as 18.50, F2 should display 18.50 (or $18.50 if the cell uses currency formatting).
  5. Confirm the formula. Press Enter on Windows or Return on Mac. Check that Excel displays the expected result rather than the formula text. If you see the formula itself, the cell may be formatted as Text; change it to General, then re-enter the formula. A leading apostrophe can also make Excel treat an entry as text.
  6. Test another lookup value. Change E2 to P102. The result should change to 7.25, because that is the price on the row with ID P102. Change E2 to an ID that is not listed, such as P999; the cell should show Not found. This quick test checks both the successful-match and missing-value behavior before you rely on the formula.
  7. Copy the formula only if you need more results. For a list of lookup IDs in E2:E10, copy F2 down using the fill handle at the lower-right corner of the selected cell. The references E2, A2:A6 and C2:C6 will adjust as you copy, so the source ranges may move. To keep those ranges fixed, edit the formula to use dollar signs: =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found"). With P102 in E3, this copied formula returns 7.25 because the source ranges remain fixed while the lookup cell adjusts to E3.

The dollar signs are useful when filling a formula down a list; they are not required for a single lookup. If you will regularly add new records, consider converting the source list into an Excel table with Insert > Table. Table-based ranges can expand as rows are added, reducing the chance that a new record falls outside a fixed range.

Common Mistakes

  • Using ranges of different sizes. The lookup range and return range need corresponding positions. If the search range contains five rows, the return range should contain five rows in the same order. Otherwise, you can get an error or an incorrect result. Check both the first and last row references.
  • Looking in the wrong direction. XLOOKUP searches the lookup range and returns from the return range; it does not require the return column to be on the right. Make sure the second argument points to the IDs and the third points to the result you want, not the other way around.
  • Assuming a visibly similar value is an exact match. A number stored as text may not match a numeric value. Extra spaces before or after text can also prevent a match. Compare the source cell and lookup input, and standardize their data types and spacing if needed.
  • Forgetting that the first duplicate wins. By default, XLOOKUP searches from the first item to the last and returns the first matching row. If IDs are duplicated, later records will not be returned by the basic formula. Remove unintended duplicates or use a deliberate last-to-first search when the last matching record is the one you need.
  • Expecting an approximate match without asking for one. XLOOKUP uses exact matching by default. Approximate matching is an optional setting and is useful for ranges such as grading bands or discount thresholds, but it must be chosen intentionally. Do not use it for unique IDs where an exact match is required.
  • Using a function that the Excel version does not have. In Excel 2019 or older, XLOOKUP may show a #NAME? error because that version does not support the function. Opening the workbook in a newer version does not change the capabilities of an older Excel installation.

Tips

  • Use a helpful missing-result message. The fourth argument in the example displays Not found instead of #N/A when there is no match. Choose a message that makes sense for your sheet, such as No matching ID, but do not use it to hide errors caused by incorrect ranges or data.
  • Keep lookup values unique when possible. A unique product ID, employee number or order number gives a clear result. Names are often less reliable because two people or products can share the same name.
  • Return more than one column when appropriate. The return range can include adjacent columns, not just one. If you return multiple columns in a supported dynamic-array version, Excel can spill the results into neighboring empty cells. Leave those cells clear and verify that the returned columns line up with the source records.
  • Use the optional settings for a specific reason. XLOOKUP can search from the bottom upward to find the last match, and it can perform exact, approximate or wildcard matches. Approximate modes are useful for ordered thresholds; wildcard mode is useful when you intentionally want to match text patterns. For ordinary IDs, the default exact match is usually the safest choice.
  • Format the result separately from the lookup. XLOOKUP returns the underlying value; it does not decide how that value should look. Apply currency, date or percentage formatting to the result cell as needed. If a returned date displays as a serial number, set the cell’s number format to Date.

Troubleshooting

The formula returns #N/A. That usually means the value was not found, especially if you removed the custom missing-result argument. Check for spelling differences, extra spaces, a wrong lookup range, or text-versus-number mismatches. Confirm the lookup value actually appears in the selected range. If it may be absent, add a clear fourth argument such as the message in the example.

The formula returns #NAME? Check that XLOOKUP is spelled correctly and that your Excel version supports it. Excel 2019 and earlier do not include XLOOKUP; use a supported Excel version or a compatible alternative such as INDEX and MATCH. If other formulas work but this one does not, version support is a likely cause.

The formula returns the wrong row’s value. Verify that the lookup range and return range start and end on corresponding rows. Then check for duplicate lookup values: the default search returns the first match. If the intended record is the last duplicate, use XLOOKUP’s reverse search option, or correct the underlying duplicates if they should not exist.

Excel displays the formula instead of calculating it. Set the cell’s number format to General, click into the formula bar, and press Enter again. Also check that the formula does not start with an apostrophe and that Show Formulas has not been turned on. On Windows, Ctrl+` toggles formula display; on Mac, the equivalent is Control+` in many keyboard layouts, though the key may differ by keyboard.

You see #SPILL! or a blocked result. This can happen when XLOOKUP is returning multiple columns or rows and cells needed for the output are not empty. Clear the obstructing cells, or change the return range to just the one column you need. A single-cell return such as the price range C2:C6 should not need to spill into adjacent cells.

Excel rejects the formula as typed. Check that commas separate the arguments and that the straight double quotes around Not found are paired. Some regional settings use semicolons rather than commas as formula separators; if Excel expects semicolons, replace the commas with semicolons in the formula. Do not change the range references or the order of the arguments.

Frequently Asked Questions

What does XLOOKUP do in Excel?

It searches a range for a value and returns the corresponding item from another range. It can return results from columns on either side of the lookup range and uses an exact match by default.

Is XLOOKUP better than VLOOKUP?

For many newer Excel workbooks, XLOOKUP is more flexible: the lookup and return ranges are separate, the return range can be to the left, and a missing-result message can be built into the function. VLOOKUP may still be needed when a workbook must work in older Excel versions.

Can XLOOKUP return a value to the left?

Yes. Set the lookup range to the column containing the value to search for, then set the return range to the column containing the answer. The return column does not have to be to the right of the lookup column.

How do I return the last matching result?

Use XLOOKUP’s optional search-mode setting to search from last to first. This is useful when records are ordered by date and you want the most recent matching row. Check that the records are actually arranged in the order your task assumes.

Why does XLOOKUP show #N/A when I can see the value?

The displayed values may look identical but differ because of extra spaces, hidden characters, or different data types. Compare the actual source and lookup entries, then clean or standardize them. Also confirm that the formula searches the column containing the value you can see.

Can I use XLOOKUP in older Excel versions?

XLOOKUP is not available in Excel 2019 or earlier. If the workbook must support those versions, use an older compatible lookup approach, such as INDEX and MATCH, or open it in Microsoft 365 or Excel 2021 or later.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.