'%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)
How to Fix VLOOKUP #NAME? Error and Skipped Rows in Excel
Question details
The user needs to fix a VLOOKUP formula that returns a #NAME? error or skips expected rows.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Retrieving data from a table or named range using the VLOOKUP function.
- Observed behavior
- The formula fails to return the correct value, instead returning a #NAME? error or pulling data from an incorrect or skipped row.
Verify that your spreadsheet's calculation mode is set to Automatic and check that there are no extra spaces in the text of your lookup values.
Correct Formula Spelling and Set to Exact Match
Resolve the #NAME? error by verifying the spelling of named ranges and avoid skipped rows by enforcing an exact match.
A #NAME? error typically indicates that the spreadsheet cannot recognize a text string within the formula. This is most commonly caused by a misspelled function name or a misspelled named range.
Go to the Formulas tab and click on Name Manager. Verify that your intended named range (such as MinDistDZ) actually exists and is spelled exactly as you typed it in the formula.
To prevent VLOOKUP from skipping rows or returning inaccurate data, add FALSE or 0 as the fourth argument in your formula. For example, edit your formula to read: =VLOOKUP(A23, MinDistDZ, 4, FALSE).
Check the third argument in your VLOOKUP formula. Ensure it corresponds to the correct column number within your named range where the return value is located.
Fix Skipped Rows by Sorting and Matching Data Types
If forcing an exact match is not applicable, correct skipped rows by properly formatting and sorting the lookup array.
Troubleshoot Formulas Easily with WPS Spreadsheet
WPS Spreadsheet provides a highly intuitive interface with built-in Error Checking and an easy-to-use Name Manager, allowing you to identify and fix VLOOKUP issues in seconds.
- 1. Open the document in WPS: Launch WPS Office and open your spreadsheet file.
- 2. Access the Name Manager: Navigate to the Formulas tab on the ribbon and click Name Manager to quickly verify or edit your data ranges.
- 3. Use the Function Wizard: Click the fx (Insert Function) icon next to the formula bar, search for VLOOKUP, and use the prompt boxes to enter your exact arguments safely.
Frequently Asked Questions
What exactly does a #NAME? error mean in Excel or WPS?
The #NAME? error means the application cannot understand a text value in the formula. This happens if the function name is typed incorrectly (e.g., VLOKUP instead of VLOOKUP), a named range does not exist, or text strings inside the formula are missing quotation marks.
Why does VLOOKUP return a value from the wrong row?
This usually occurs when the fourth argument of the VLOOKUP formula is omitted or set to TRUE (approximate match), but the first column of the lookup table is not sorted in ascending order. To fix this, either sort the first column alphabetically/numerically, or change the last argument to FALSE for an exact match.
How do I fix a data type mismatch in VLOOKUP?
If your lookup value is a number formatted as text, but your table contains actual numbers, VLOOKUP will fail to find a match. You can fix this by selecting the cells, navigating to the Home tab, and changing the cell format to General or Number. Alternatively, use the 'Text to Columns' feature under the Data tab to quickly convert text numbers into actual numerical values.
