To put an XLOOKUP formula in Excel, select the cell where you want the answer, then enter a formula that identifies the value to find, the range to search, and the range containing the result. With product IDs in A2:A6, prices in C2:C6, and the ID to find in E2, enter =XLOOKUP(E2,A2:A6,C2:C6,"Not found") to return the matching price or display “Not found” if there is no match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older versions such as Excel 2019 and 2016 do not include it.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows, or Excel 2021 or later. The formula works in supported Mac versions and Excel for the web, too, though menus and keyboard shortcuts can differ.
- A range containing the values you want to search, and a corresponding range containing the answers you want returned. Keep the two ranges the same length.
- A known value to look up, such as a product ID, employee name, or invoice number. Check that its spelling and data type match the values in your search range.
Step-by-Step
- Arrange the source data. For this example, place product IDs in A2:A6, product names in B2:B6, and prices in C2:C6. Use these sample rows: P101, Notebook, 3.50; P102, Pen, 1.25; P103, Folder, 2.75; P104, Stapler, 8.40; and P105, Marker, 1.80. Put the ID you want to search for in E2; enter P104 there. The lookup range and result range do not have to be next to each other, but they should have corresponding entries in the same row order.
- Choose the result cell. Click an empty cell where you want the answer to appear, such as F2. Do not select a cell inside the source data if the result could overwrite a value you need. If you are building a reusable lookup area, add a label such as “Price” above or beside the result so it is clear what the returned number means.
- Start the formula and identify the lookup value. Type an equals sign, then XLOOKUP and an opening parenthesis. The first argument is the value Excel should search for. In this example, that is E2, the cell containing P104. Referencing the input cell instead of typing P104 directly makes the formula reusable: change E2 to another product ID and the result updates.
- Give Excel the search range and return range. After the lookup value, type a comma and select or type A2:A6 as the lookup range. Type another comma and select or type C2:C6 as the return range. The search range is where Excel looks for the ID; the return range is where it gets the price from the matching row. These ranges must align: the first ID corresponds to the first price, the second ID to the second price, and so on.
- Add a message for missing matches and finish the formula. Type another comma, then enter
"Not found"as the optional message to display when the ID is absent. Close the parenthesis and press Enter. With P104 in E2, IDs in A2:A6, and prices in C2:C6, use=XLOOKUP(E2,A2:A6,C2:C6,"Not found"). The result is 8.4, which is the price in C5 on the same row as P104. Excel may display it as 8.40 if the result cell is formatted to show two decimal places. - Check the result against the source. Confirm that the returned value is in the same row as the matching ID. If you see “Not found,” check whether the lookup value appears exactly in A2:A6 and whether the formula points to the intended ranges. If the result is a number, apply a suitable number format using Home > Number if you need a currency sign or fixed decimal places. Formatting changes the display, not the underlying value.
- Make the formula reusable, if needed. If you want to copy the formula down a list of lookup values in E2:E10, keep the source ranges fixed with dollar signs. With product IDs in A2:A6, prices in C2:C6, and the first lookup ID in E2, enter
=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found")in F2. Copy it down using the fill handle at the lower-right corner of F2. The E2 reference changes to E3, E4, and so on, while the source ranges stay in place. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V.
The basic argument order is lookup value, lookup range, return range, then optional settings. XLOOKUP searches for an exact match by default, so you normally do not need to add a match setting for IDs, names, or other exact keys. Unlike older lookup formulas, the return range can be to the left or right of the lookup range, and you do not need to count a column number.
Common Mistakes
- Using ranges with different sizes. A2:A6 has five cells, so the corresponding return range must also have five cells. If one range starts on row 2 and the other starts on row 3, the results can be misaligned even if Excel accepts the formula.
- Searching the wrong column. The lookup range must contain the value in your lookup cell. If E2 contains a product ID but you point XLOOKUP at the product-name column, Excel will not find the ID. The return range should contain the information you want displayed, not another copy of the search value unless that is intentional.
- Forgetting dollar signs when filling down. Relative ranges shift as a formula is copied. If a copied formula starts searching A3:A7 instead of A2:A6, it may miss records. Lock the source ranges with dollar signs before copying, while leaving the input reference such as E2 relative.
- Confusing text and numbers. A numeric ID stored as text may not match the same digits stored as a number. Leading or trailing spaces can also prevent a text match. Check the source and input cells, and make their values consistent rather than changing the lookup formula at random.
- Expecting an approximate match by default. XLOOKUP uses exact matching by default. That is usually right for IDs and names. For a threshold table or ranges of values, you must deliberately select an appropriate match mode and make sure your table is arranged for that use.
Tips
- Use the optional not-found argument to show a clear message rather than an #N/A error. Choose wording that makes sense in your sheet, such as “Check ID” or “No record.” If you leave this argument out, a missing match returns #N/A, which can be useful when you want missing data to stand out.
- Keep lookup values unique when each result should identify one record. If the lookup range contains duplicates, XLOOKUP returns the first matching result it encounters, searching from the top by default. It will not warn you that there are additional matches.
- For large or frequently updated data sets, consider turning the source range into an Excel table with Insert > Table. Tables expand as rows are added, reducing the chance that a formula misses new records. Check the formula references after creating the table, especially if you are not familiar with structured references.
- Use clear labels and consistent formatting. For example, label E1 “Product ID” and F1 “Price,” then format prices as currency. This makes it easier for someone else to enter a lookup value and understand the result.
- When selecting ranges with the mouse, start at the first data row and stop at the last data row, excluding headers unless your lookup is intended to search them. If you expect the source data to grow, use an Excel table or update the ranges when adding rows.
Troubleshooting
- You see #NAME? The Excel version may not support XLOOKUP, or the function name may be misspelled. Use Microsoft 365 or Excel 2021 and later, and check that the formula starts with XLOOKUP. If you are working in Excel 2019 or 2016, use a compatible alternative such as INDEX and MATCH or update to a version that supports XLOOKUP.
- You see #N/A or your not-found message. Confirm that the lookup value exists in the specified lookup range. Look for extra spaces, different punctuation, or values stored as text in one place and numbers in another. Also verify that the formula references the right input cell and the full search range. Text matches are not case-sensitive by default, but differences in the actual characters still matter.
- You get a value, but it is the wrong one. Check the return range first: it may point to a neighboring column. Then verify that the lookup range and return range begin and end on corresponding rows. If there are duplicate lookup values, the first match may not be the record you expected; remove duplicates or use a more specific key.
- The formula appears in the cell instead of calculating. Make sure the cell is not formatted as Text. Change its format to General using Home > Number, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. On some Mac keyboards you may need Fn+F2, depending on keyboard settings; you can also click in the formula bar and press Return.
- Excel rejects commas in the formula. Some regional settings use semicolons as argument separators. If Excel displays a formula error as you enter it, follow the separator used by your Excel installation and its existing formulas. The examples here use commas, as requested for standard English-language Excel syntax.
- The result does not update when the input changes. Check that calculation is set to Automatic under Formulas > Calculation Options. If calculation is Manual, choose Automatic or recalculate the workbook. Also make sure the formula refers to the input cell you are actually editing, rather than a typed-in value or a different cell.
Frequently Asked Questions
What is the simplest XLOOKUP formula?
A basic XLOOKUP needs a value to find, a range to search, and a range to return from. For a more helpful result when there is no match, add the optional not-found message. Exact matching is the default, so no additional match setting is required for a typical ID lookup.
Can XLOOKUP return a value from a column to the left?
Yes. XLOOKUP can search one range and return a value from a separate range to its left or right. Select the lookup range and return range independently, and ensure they cover corresponding rows. This flexibility is one advantage over VLOOKUP, which normally requires the return column to be to the right.
Does XLOOKUP work in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or Excel 2016. If you need to share a workbook with someone using an older version, use a compatible formula or confirm that they can open it in a supported version.
How do I make XLOOKUP return a blank when there is no match?
Use an empty text string as the optional not-found result. With lookup values in A2:A6, results in C2:C6, and the value to find in E2, the formula is =XLOOKUP(E2,A2:A6,C2:C6,""). This displays a blank for a missing match, though it can make an unmatched value less obvious than a message such as “Not found.”
Can XLOOKUP find part of a text value?
Yes, with wildcard matching, but the match mode must be set for wildcards. For ordinary exact lookups, leave the default settings in place. Wildcards are useful for searches such as names that begin with certain letters, but take care: a partial match can return the first result that fits rather than a uniquely identified record.
Related
- How to Print an Excel Sheet on One Page Without Clipping
- Print in Excel Without Cutting Off: Page Setup Tips
- Print Labels in Word from Excel: Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Text Left of a Character: Formulas and ExamplesTo return the text before a character in Excel, combine LEFT with FIND: if A2 contains INV-2048, enter…
- C++ String Functions: How std::string Works in PracticeIn C++, string functions are operations for creating, inspecting, searching, and changing text. For most C++ programs, use…
- Excel IF Formula Returns 0? Show a Blank Cell InsteadIf an Excel formula returns 0 and you want the cell to look blank, wrap the calculation in…
- Excel Filter Shortcut: Turn Filters On and Use ThemIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for the current…
- COUNTIF Duplicate Formula in Excel: Find DuplicatesTo flag every value that appears more than once in Excel, enter =COUNTIF($A$2:$A$100,A2)>1 beside the first value and…
- Convert Dates to Text in Excel: Formulas and MethodsIf the dates are real Excel dates in A2:A1000, enter =TEXT(A2,"mmmm d, yyyy") in B2 and fill it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.