To find a value in one Excel column and return its match from another, put the lookup codes in A2:A6, prices in C2:C6, and the code to search for in E2. Enter =XLOOKUP(E2,A2:A6,C2:C6) in F2. If E2 contains P-104 and that code’s price is $18.50, F2 returns 18.50. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; the same formula method works in supported versions of Excel for Mac and Excel for the web.
What You’ll Need
- A version of Excel that includes XLOOKUP: Microsoft 365, Excel 2021, or Excel 2024. Excel 2019 and earlier do not include this function.
- A lookup value, such as a product code, that you want Excel to find.
- A lookup range containing that value and a return range containing the result you want. The ranges should be the same size and point in the same direction.
- A blank cell for the formula, plus a small sample of your data to check that the result is right.
The examples below use a product list with codes in A2:A6, product names in B2:B6, and prices in C2:C6. Cell E2 holds the code to search for. The rows are P-101, Notebook, 4.50; P-102, Pen, 1.25; P-103, Folder, 2.75; P-104, Desk lamp, 18.50; and P-105, Stapler, 6.00.
Step-by-Step
-
Set up the data and identify the result you want. Decide which column contains the value Excel should search and which column contains the answer. For the sample product list, you search the codes in A2:A6 and return either a name from B2:B6 or a price from C2:C6. Put the search code in E2; using a separate input cell makes the formula easy to reuse with different codes.
-
Select the result cell and enter an exact-match formula. With codes in A2:A6, prices in C2:C6, and the code to find in E2, select F2 and type
=XLOOKUP(E2,A2:A6,C2:C6). Press Enter. If E2 contains P-104, the result is 18.50. XLOOKUP uses exact matching by default, so this is generally the right starting point for IDs, names, and product codes.The arguments appear in this order: the value to look for, the range to search, and the range to return a result from. Unlike older lookup formulas, XLOOKUP does not require the lookup column to sit to the left of the return column.
-
Add a message for a missing value. With codes in A2:A6, prices in C2:C6, and the search code in E2, replace the formula in F2 with
=XLOOKUP(E2,A2:A6,C2:C6,"Code not found"). If E2 contains P-104, F2 still returns 18.50. If E2 contains P-999, it displays Code not found instead of the default #N/A error. This optional fourth argument only controls what appears when no match is found; it does not hide other formula problems. -
Return a different field by changing the return range. With the same product list and search code in E2, enter
=XLOOKUP(E2,A2:A6,B2:B6,"Code not found")in G2 to return the product name. For P-104, the result is Desk lamp. Keep the lookup value and lookup range the same; change only the return range to retrieve another field, such as a department, date, or status. -
Make the ranges fixed if you will copy the formula. With codes in A2:A6, prices in C2:C6, and different search codes entered down E2:E10, put this formula in F2 and fill it down:
=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Code not found"). For P-104 in E2, the result is 18.50. The dollar signs lock the data ranges as the formula is copied, while E2 changes to E3, E4, and so on. You can add the dollar signs by selecting a reference in the formula and pressing F4 on Windows; on many Mac keyboards, use Command+T, or add the dollar signs manually if that shortcut is unavailable. -
Choose an approximate match only when your data calls for it. Approximate matching is useful for thresholds, commission bands, or grade cutoffs, rather than ordinary IDs. Suppose G2:G5 contains ascending minimum amounts 0, 50, 100, and 200, H2:H5 contains Basic, Standard, Plus, and Premium, and E2 contains 75. Enter
=XLOOKUP(E2,G2:G5,H2:H5,"No tier",-1). The result is Standard because 50 is the largest threshold that does not exceed 75. The -1 match mode means exact match or next smaller value. Use 1 for exact match or next larger value, and make sure thresholds are sorted in the direction your method requires. -
Use the optional search direction only when needed. If codes in A2:A6 can repeat and you want the last matching row’s price from C2:C6, with the code to find in E2, use
=XLOOKUP(E2,A2:A6,C2:C6,"Code not found",0,-1). For P-104, it returns 18.50, and if duplicate P-104 entries exist, it searches from the bottom upward and returns the last one. The final argument -1 controls reverse search; it is different from the -1 match mode in the fifth argument. -
Check the result and copy the formula carefully. Test the formula with a value you can verify in the source data, then try a value that is absent to confirm the not-found message. Check that the returned result comes from the intended row. If you fill the formula down, confirm the lookup ranges stay fixed and the input-cell reference moves as intended. Save the workbook after verifying the results.
Common Mistakes
- Using ranges of different sizes. If the lookup range is A2:A20, the return range should cover the corresponding rows, such as C2:C20. A mismatch can produce an error or make the formula inconsistent with the intended records.
- Expecting an approximate match from the default. XLOOKUP defaults to exact matching. For a nearest threshold, specify the appropriate match mode instead of assuming Excel will choose the closest number.
- Confusing the fifth and sixth arguments. The fifth argument controls how values match; the sixth controls search direction. A -1 in the fifth position means exact or next smaller, while a -1 in the sixth position searches from last to first.
- Mixing text and numbers. A code stored as the text “104” may not match the number 104. This often happens after importing data or adding leading zeros. Make the lookup value and lookup column use a consistent data type.
- Copying formulas with shifting ranges. If you want every row to search the same list, use absolute references such as $A$2:$A$6. Otherwise, the range may move as you fill down.
- Using XLOOKUP in an unsupported version. Excel 2019 and earlier may show #NAME? because they do not recognize the function. Use a supported version or an older alternative such as INDEX and MATCH.
Tips
For most lookup tasks, start with exact matching and add the not-found message. It keeps the formula readable and makes missing records obvious. A message such as “Check code” can also prompt someone to review an input rather than mistaking a missing result for a valid blank.
When your data is an Excel table, structured references can make formulas easier to maintain as rows are added. For example, if a table is named Products and its columns are SKU and Price, with the lookup code in E2, use =XLOOKUP(E2,Products[SKU],Products[Price],"Code not found"). For a matching row where SKU is P-104 and Price is 18.50, the result is 18.50. Replace Products, SKU, and Price with the actual table and column names in your workbook.
XLOOKUP can also return values from a column to the left of the lookup column, and it can search horizontally when both arrays run across a row. If the return range contains multiple columns, a supported version of Excel can spill the matching row of results into neighboring cells. Keep those spill cells empty or Excel may display #SPILL!. Wildcards are available with match mode 2: an asterisk represents any number of characters and a question mark represents one character. Use wildcards only when partial text matching is intentional.
Keep source data clean. Remove accidental leading or trailing spaces, use consistent spellings, and avoid duplicate keys unless returning the first or last duplicate is an intentional choice. For approximate matching, sort the threshold list appropriately and test values just below, exactly at, and just above each cutoff.
Troubleshooting
- #N/A appears: The lookup value was not found by an exact match, or it differs from the source value. Check for extra spaces, misspellings, different number-versus-text storage, and leading zeros. Confirm the lookup range includes the expected row. You can add the fourth argument to show a clear message, but first verify whether the missing match is expected.
- #VALUE! or an unexpected error appears: Check that lookup and return ranges have compatible dimensions and orientation. A vertical lookup range should normally pair with a vertical return range covering the same records. Also make sure the formula arguments are separated correctly for your Excel regional settings. The examples use commas; some settings require semicolons when entering formulas.
- #NAME? appears: Check that the function name is spelled XLOOKUP and that your Excel version supports it. If the formula is opened in an older Excel edition, it may not calculate there even if it was created in Microsoft 365.
- The formula returns the wrong duplicate: By default, XLOOKUP returns the first match from the top. If you need the last match, set the sixth argument to -1. If duplicate records should not exist, investigate and remove or correct them rather than relying on search direction to conceal them.
- The answer looks wrong despite a match: Confirm that the return range points to the correct column and starts on the same row as the lookup range. For numeric results, check cell formatting: a displayed date or rounded amount may be a formatting issue, not a lookup error. For a spilled result, clear obstructing cells next to the formula.
Frequently Asked Questions
Does XLOOKUP require the lookup column to be first?
No. The lookup range and return range are separate arguments, so the return range can be on either side of the lookup range. They still need to refer to corresponding rows or columns.
What does XLOOKUP return if it cannot find a match?
By default, it returns #N/A. Add a message as the fourth argument to show a more helpful result for a missing value. That setting does not replace other errors, such as an invalid range.
Can XLOOKUP return more than one column?
Yes. In Microsoft 365 and other supported dynamic-array versions, choose a return range spanning multiple columns. Excel spills the matching row of results into adjacent cells, which must be clear for the spill to work.
Can I use XLOOKUP to find partial text?
Yes. Set the match mode to 2 to enable wildcard matching with * and ?. Use this for deliberately partial text searches, not for numeric thresholds. Exact matching remains the default when you omit the match mode.
Why does my colleague see an error when opening the workbook?
They may be using Excel 2019 or an earlier version, which does not support XLOOKUP. Ask them to open the file in Microsoft 365, Excel 2021 or later, or Excel for the web, or replace the formula with a compatible older lookup method.
Related
- How ROUNDUP Works in Excel: Syntax and Examples
- How Replace Works in Excel: Find and Replace
- How to Share Work in Excel and Collaborate in Real Time
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF in Excel: Syntax, Examples, and Common FixesIn Excel, use COUNTIF to count cells in one range that meet one condition. If the values you…
- Excel Formula for Cylinder Volume: A Clear ExampleFor a cylinder with its radius in cell A2 and its height in B2, enter =PI()*A2^2*B2 in another…
- Excel Dependent Dropdown List Using INDIRECT in 365To make an Excel dependent dropdown list using INDIRECT, create a named range for each set of choices,…
- Sort Excel Data by Date: Steps and TroubleshootingTo sort Excel data by date, select the entire table or the date column within it, then choose…
- Excel INDEX Errors: Common Causes, Meanings, and FixesAn Excel INDEX error usually means the requested row or column falls outside the range (#REF!), an index…
- Excel Lookup Function Examples: XLOOKUP, VLOOKUP and MoreExcel lookup functions find a value in one range and return a related value from another. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.