'%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 Excel VLOOKUP Errors from Incorrect Return Columns
Question details
The user needs to fix a VLOOKUP formula that returns incorrect results or errors due to an improper third argument.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Using the VLOOKUP function to find and retrieve data from a specific column in a spreadsheet table.
- Observed behavior
- The formula returns an error or an incorrect value because a cell reference was entered instead of a numeric column index for the return-column argument.
Double-check your VLOOKUP formula syntax, specifically ensuring you know the exact column number in your selected table array from which you want to return data.
Replace the Cell Reference with a Column Index Number
Correct the third argument of your VLOOKUP formula to use a hardcoded column number instead of a cell reference.
The VLOOKUP function requires four arguments: lookup_value, table_array, col_index_num, and range_lookup. The third argument must be a number representing the column position within your selected table array, not a cell reference like 'D2'.
Select the cell displaying the VLOOKUP error and click into the formula bar at the top of the spreadsheet to edit it.
Find the third part of the VLOOKUP formula. For example, if your formula is =VLOOKUP(C2,C$2:D$4,D2,FALSE), the third argument is currently set to 'D2'.
Count the columns in your selected table array starting from 1. Replace the cell reference (like D2) with the correct numeric index. For a two-column table where you want the second column's data, type '2'.
Ensure the fourth argument is set to FALSE to require an exact match. Press Enter to apply the updated formula, which should now look like =VLOOKUP(C2,C$2:D$4,2,FALSE).
Master VLOOKUP and Complex Formulas in WPS Spreadsheet
WPS Office provides a highly compatible spreadsheet application with built-in formula error checking, syntax tooltips, and easy-to-use function wizards that help you construct flawless VLOOKUP formulas.
- 1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the document containing the data you want to search.
- 2. Use the Insert Function tool: Select your target cell, click the 'fx' icon next to the formula bar, and search for the VLOOKUP function.
- 3. Input arguments step-by-step: Use the dialog box to input your lookup value and table array. Type the exact column index number (e.g., 2) directly into the 'Col_index_num' field.
- 4. Complete the formula: Enter 'FALSE' in the Range_lookup field and click OK to generate an error-free VLOOKUP formula instantly.
Frequently Asked Questions
What does the #REF! error mean in a VLOOKUP formula?
A #REF! error typically occurs when your column index number (the third argument) is greater than the total number of columns in your selected table array. Ensure the number you enter does not exceed the width of your highlighted data range.
Why is my VLOOKUP returning an #N/A error?
The #N/A error indicates that the exact lookup value cannot be found in the first column of your specified table array. Check to ensure the value actually exists and that there are no hidden trailing spaces or formatting mismatches (like text vs. numbers).
Can I use a cell reference for the column index number in VLOOKUP?
Yes, but only if that specific referenced cell contains a valid number representing the column index you wish to extract. If the referenced cell contains text or is empty, the formula will return an error. It is generally safer to use a hardcoded number or combine VLOOKUP with the MATCH function for dynamic columns.
