'%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 Returning the Wrong Value in Excel
Question details
The VLOOKUP formula is returning incorrect results, such as returning 'April' when 'January' is the lookup value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using a VLOOKUP formula combined with IFERROR to retrieve specific data from a table range.
- Observed behavior
- The formula =IFERROR(VLOOKUP(E10,A:B,2),"Not Found") retrieves mismatched or approximate data instead of the exact target.
Verify that your lookup values do not contain hidden spaces and that the source data column is free of formatting inconsistencies.
Use the Exact Match Parameter in Your Formula
By default, VLOOKUP uses an approximate match. Adding 'FALSE' or '0' as the fourth argument forces it to only return exact matches.
When the fourth argument (range_lookup) is omitted, Excel assumes it is TRUE, meaning it will look for an approximate match. If the data is not sorted in ascending order, this behavior often returns completely incorrect rows.
Click on the cell containing your VLOOKUP formula to activate it.
Click inside the Formula Bar at the top of the worksheet to edit the formula.
Modify the formula to include 'FALSE' or '0' at the end of the VLOOKUP arguments. Change it to: =IFERROR(VLOOKUP(E10,A:B,2,FALSE),"Not Found").
Press Enter on your keyboard. The cell should now display the correct exact match or 'Not Found' if the value does not exist.
Match Data Types and Formatting
Mismatching data types between the lookup value and the source array (such as text versus dates) can cause VLOOKUP to fail.
Write Accurate Formulas Effortlessly with WPS Office
WPS Spreadsheet is a powerful, free alternative that is highly compatible with Microsoft Excel. It offers intelligent formula syntax prompts and error-checking features to help you write flawless VLOOKUP functions and troubleshoot data effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Trigger the formula assistant: Type =VLOOKUP( into an empty cell to activate the intelligent syntax guide.
- 3. Input lookup arguments: Follow the on-screen tooltip to select your lookup_value, table_array, and col_index_num.
- 4. Select exact match: When prompted for the range_lookup argument, select 'FALSE' from the dropdown suggestions and press Enter to guarantee an exact match.
Frequently Asked Questions
Why does VLOOKUP work for some rows but return wrong values for others?
This commonly happens when you drag the formula down without using absolute cell references for your table array. Ensure your table array is locked using dollar signs (e.g., $A$1:$B$100), and verify that you included 'FALSE' as the fourth argument for exact matching.
What happens if I use TRUE instead of FALSE in VLOOKUP?
Using TRUE (or 1) tells VLOOKUP to look for an approximate match. If an exact match is not found, it returns the next largest value that is less than your lookup value. However, this requires the first column of your table array to be sorted in ascending order; otherwise, the results will be completely unpredictable.
How can I fix data type mismatches if numbers are stored as text?
Select the column with the numbers, click the small warning icon that appears next to the selection, and choose 'Convert to Number'. Alternatively, you can use the 'Text to Columns' feature under the Data tab and click 'Finish' to standardize the formatting instantly.
