Excel lookup not working usually comes down to one of four things: the value is not a clean match, the formula is using the wrong match type, the lookup range moved, or Excel has not recalculated the workbook. Start with the checks below in order, from the fastest #N/A fixes to deeper Excel and workbook repairs.
1. Confirm the lookup value exists
Start here when the cell shows #N/A: Excel returns #N/A when the lookup formula cannot find a valid match.
- 1.Click the cell used as the lookup value and copy the exact value.
- 2.Go to the source range and use Home > Find & Select > Find to search for that value.
- 3.Check that the value sits inside the lookup column or lookup array referenced by the formula.
- 4.For XLOOKUP, add a not-found message:
=XLOOKUP(lookup_value,lookup_array,return_array,"not found").
That message does not create a match. It tells you cleanly when the source range does not contain one.
2. Clean the values that look identical
Two entries can look the same and still fail. Numbers stored as text, extra spaces, and nonprinting characters break exact matches.
- On Windows, select the lookup cells and source cells, right-click, choose Format Cells, select Number, then choose the correct format.
- For a full text-formatted column, apply the format, select the column, go to Data > Text to Columns, then select Finish.
- Use
=TRIM(A2)to remove extra ASCII spaces. - Use
=CLEAN(A2)to remove the common nonprinting characters. TRIM and CLEAN do not remove nonbreaking spaces or other higher-value Unicode characters, which are common in web and imported data, so wrap them in SUBSTITUTE first, such as=TRIM(SUBSTITUTE(A2,CHAR(160)," ")). - For a lookup formula, use TRIM inside the lookup, such as
=VLOOKUP(D2,TRIM(A2:B7),2,FALSE). In current dynamic-array Excel, press Enter; in older Excel, confirm it as a legacy array formula with Ctrl + Shift + Enter, or add a helper column with TRIM and look up against the cleaned column.
3. Switch to exact matching
- For VLOOKUP or HLOOKUP, set the fourth argument to FALSE.
- For MATCH, set the match type to 0.
- For XLOOKUP, use the default exact match or set match mode to 0.
- For approximate matching, sort the lookup data: for VLOOKUP sort the first column ascending, for HLOOKUP sort the first row ascending (left to right), and for LOOKUP sort the lookup vector ascending. For MATCH, sort ascending for match type 1 and descending for match type -1.
Wrong lookup results come from approximate matching on data that is not sorted. When you do not need an approximate match, exact match is the correct repair.
4. Rebuild the VLOOKUP range
VLOOKUP is strict about where it looks: the first column of table_array must contain the lookup value, the return column must be inside table_array, and col_index_num must be a positive number counted from the left-most column of that range.
- 1.Edit the formula and highlight the
table_array. - 2.Make sure the first column in that highlighted range contains the lookup value.
- 3.Count from the left-most column of the highlighted range to the return column.
- 4.Replace
col_index_numwith that positive column number. - 5.Lock the range with absolute references such as
$A$2:$D$100, a named range, or an Excel Table reference before filling the formula down or across.
If the return column is to the left of the lookup column, use =XLOOKUP(lookup_value,lookup_array,return_array). XLOOKUP searches left, right, up, or down and returns exact matches by default. XLOOKUP is available in Excel for Microsoft 365, Excel 2021, and Excel 2024; in Excel 2016 or Excel 2019, use INDEX and MATCH instead. INDEX and MATCH also handle left-side returns with =INDEX(return_range,MATCH(lookup_value,lookup_range,0)).
5. Decode the error message
Use the exact error to choose the fix.
- #VALUE!
- In VLOOKUP, one of the arguments needs correction. If
lookup_valueis over 255 characters, shorten it or use INDEX and MATCH. Ifcol_index_numcontains text or a number less than 1, replace it with a valid positive column number. - #SPILL!
- Excel cannot place the full dynamic-array result, and the fix depends on the alert Excel shows. When the spill range is blocked, clear or move the cells inside the dashed spill range. Other causes need their own fix: reference a specific range instead of a whole column when the result runs off the sheet, move the formula out of an Excel table or convert the table to a range, unmerge cells in the spill range, or remove volatile functions that keep resizing the result.
- Array lookup formulas with mismatched ranges
- Edit the formula so the referenced ranges have the same number of rows or columns. In current dynamic-array Excel, type the formula in the top-left output cell and press Enter.
6. Force Excel to calculate again
- On Windows, go to File > Options > Formulas. Under Calculation options or Workbook Calculation, select Automatic.
- On Mac, go to Excel > Preferences > Calculation. Under Calculation Options, select Automatic.
- To recalculate right now in desktop Excel, select Formulas > Calculation > Calculate Now, or Calculate Sheet for the active worksheet.
- On Windows, press F9 to calculate all worksheets in all open workbooks, or Shift + F9 to calculate the active worksheet.
- In Excel for the web, select Formulas > Calculation Options > Calculate Workbook, or press F9.
When the formula is correct but the result will not update, automatic calculation is the setting to restore.
7. Repair formulas links and source data
If Excel shows the formula instead of the result, select Formulas > Show Formulas. You can also press Ctrl + ` to switch between formulas and results.
Next, check the formula itself. Make sure it starts with =, includes the required arguments, and has matching opening and closing parentheses. Then run Formulas > Formula Auditing > Error Checking.
For lookups that pull from another workbook, go to Data > Queries and Connections > Workbook Links. Choose Refresh All, or select More Commands (…) on Windows or Link options (…) on the web, then choose Change source and browse to the correct source file.
For external data, Power Query, PivotTable, or Data Model sources, use Data > Refresh All. On Windows, Ctrl + Alt + F5 refreshes all workbook data.
Date lookups need matching date systems. On Windows, go to File > Options > Advanced, then under When calculating this workbook, select or clear Use 1904 date system. On Mac, go to Excel > Preferences > Calculation, then select or clear Use 1904 date system.
8. Test Excel and repair the workbook
- 1.If the same lookup keeps failing after the formula and data are fixed, test Excel itself. On Windows, hold Ctrl and double-click the Excel shortcut, then select Yes to open Safe Mode. You can also press Windows logo key + R, type
excel /safe, and select OK. - 2.If Safe Mode fixes the lookup, disable add-ins. Go to File > Options > Add-ins. Next to Manage, choose Excel Add-ins or COM Add-ins, select Go, then clear the add-in checkbox.
- 3.Update Excel on Windows from any Microsoft 365 app through File > Account or Office Account > Update Options > Update Now. On Mac with Microsoft 365 or Office 2021, update macOS first if prompted, then open Excel and choose Help > Check for Updates and select Update or Update All. Office 2019 for Mac reached end of support on October 10, 2023 and cannot update this way, so move to a supported Microsoft 365 version or use Excel for the web.
- 4.For a damaged workbook, select File > Open, choose the workbook, select the arrow next to Open, then choose Open and Repair > Repair. If repair fails, choose Extract Data.
- 5.On a work or school computer, ask your organization’s IT admin to keep Microsoft 365 Apps on a current update channel.
Do not use older Lookup Wizard instructions. Microsoft says the Lookup Wizard is no longer available in Excel, so use XLOOKUP, VLOOKUP, HLOOKUP, or INDEX and MATCH instead.
Frequently Asked Questions
Why does XLOOKUP return #N/A?
XLOOKUP returns #N/A when it cannot find a valid match. Check that the lookup value exists in the lookup array, then add the optional if_not_found argument, such as "not found", to make missing matches easier to read.
How do I fix numbers stored as text in Excel lookups?
Select the lookup and source cells, use Format Cells to apply the correct number format, then use Data > Text to Columns > Finish on a full text-formatted column.
Why does my copied lookup formula stop working?
The lookup range moved while you filled the formula down or across. Lock the range with absolute references like $A$2:$D$100, use a named range, or use an Excel Table reference.
What replaced the Excel Lookup Wizard?
The Lookup Wizard is no longer available. Use XLOOKUP, VLOOKUP, HLOOKUP, or INDEX and MATCH for current Excel lookup formulas.
