'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix VLOOKUP Returning #N/A Error When Copied to Next Row in Excel & WPS
Question details
The user's VLOOKUP formula works for the first few rows but returns an #N/A or value error when dragged or copied down to subsequent rows.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Copying a VLOOKUP formula down a column to populate multiple rows of data.
- Observed behavior
- The formula successfully retrieves data for the initial 13 rows but unexpectedly outputs an #N/A error for the 14th row and beyond.
Before modifying your formula, check if the lookup value actually exists in the source data range, as an #N/A error often simply means the exact match could not be found.
Lock the Table Array Range Using Absolute References
When copying formulas down, relative references shift. Locking the lookup range prevents the table array from moving down.
By default, spreadsheet formulas use relative references. If your formula is =VLOOKUP(A2, E2:F100, 2, FALSE) and you copy it down one row, it becomes =VLOOKUP(A3, E3:F101, 2, FALSE). Notice how the search range shifted down to E3:F101. This causes the formula to miss data at the top of your list, resulting in #N/A errors for subsequent rows.
Click on the first cell containing the VLOOKUP formula that works correctly.
In the formula bar at the top, highlight the table_array part of your formula (for example, E2:F100).
Press the F4 key on your keyboard. This will add dollar signs to the range, changing it to $E$2:$F$100. The dollar signs lock the rows and columns so they will not shift.
Press Enter to save the formula. Click the small square at the bottom-right corner of the cell (the fill handle) and drag it down to apply the locked formula to the rest of the rows.
Clean Formatting and Data Types in the Lookup Range
Mismatching data types (like text vs. numbers) or invisible trailing spaces can prevent VLOOKUP from finding a match, even if the values look identical.
Easily Troubleshoot Formulas with WPS Spreadsheet
WPS Office provides powerful built-in tools like error checking and formula evaluation to help you quickly identify and fix #N/A errors in your VLOOKUP functions.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing the VLOOKUP errors.
- 2. Select the Error Cell: Click on the cell displaying the #N/A error to select it.
- 3. Run Error Checking: Navigate to the 'Formulas' tab and click on 'Error Checking' to see a detailed explanation of why the value isn't resolving.
- 4. Evaluate the Formula: Click the 'Evaluate Formula' tool in the same tab to step through the calculation one piece at a time and pinpoint exactly where the reference fails.
Frequently Asked Questions
Why does my VLOOKUP return #N/A even when the value is clearly there?
This usually happens due to formatting differences, such as one cell being formatted as text and the other as a number. It can also be caused by invisible leading or trailing spaces in the cells, preventing an exact match.
How do I hide the #N/A error in my VLOOKUP results?
You can wrap your VLOOKUP formula in the IFERROR function to display a blank cell or a custom message instead of the error. For example: =IFERROR(VLOOKUP(A2, $B$2:$C$10, 2, FALSE), "Not Found").
What is the difference between TRUE and FALSE in the VLOOKUP range_lookup argument?
FALSE requires an exact match for your lookup value, which is recommended in most cases. TRUE looks for an approximate match and requires your source data to be sorted in ascending order; otherwise, it may return incorrect results.
