'%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 and HLOOKUP Errors Caused by Date Formatting
Question details
VLOOKUP or HLOOKUP returns an #N/A error when trying to match dates that look identical on screen but have different underlying serial values.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A user is looking up data based on a date formatted as mmm-yy (e.g., Jan 22). The actual date is not the first of the month, but the source data is on external restricted sheets and cannot be manually altered.
- Observed behavior
- The lookup function returns an #N/A error because the underlying date serial numbers do not match, despite the visual formatting being identical.
Select the cells containing your lookup dates and check the formula bar to verify their true underlying values (e.g., 22/1/2022) before adjusting your formulas.
Normalize Lookup Dates Using the DATE Function
Use the DATE, YEAR, and MONTH functions within your formula to dynamically convert both comparison dates to the first day of the month.
Excel stores dates as sequential serial numbers. Formatting a date to show only the month and year (e.g., mmm-yy) changes its appearance but leaves the exact day intact. When a lookup function compares 22-Jan-22 to 1-Jan-22, it reads them as entirely different numbers and fails to find a match.
Since you cannot alter the restricted external source data, you must dynamically normalize the lookup date inside your formula so the dates align perfectly.
Click on the cell containing your specific lookup date (for example, cell A2).
Construct a normalized date value inside your formula using DATE(YEAR(A2), MONTH(A2), 1).
Instead of a standard VLOOKUP, use an array formula to normalize the restricted source column as well. Enter: =INDEX(return_range, MATCH(DATE(YEAR(A2),MONTH(A2),1), DATE(YEAR(lookup_range),MONTH(lookup_range),1), 0))
Convert Dates to Text Strings Using the TEXT Function
Convert the dates into identical text strings (like "Jan-22") during the lookup process to bypass serial number mismatch issues.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced date calculations and array formulas like VLOOKUP, HLOOKUP, and INDEX/MATCH. It handles complex data seamlessly while offering full Microsoft Excel compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your #N/A lookup errors.
- 2. Select the target cell: Click on the cell where you want to output the corrected lookup result.
- 3. Use the Insert Function tool: Navigate to the Formulas tab on the ribbon and click on 'Insert Function' (fx).
- 4. Build your normalized formula: Select INDEX or MATCH from the list and use the interactive wizard to safely nest your DATE functions without risking syntax errors.
Frequently Asked Questions
Why does VLOOKUP return #N/A when my dates look visually identical?
VLOOKUP compares the underlying cell values, not the formatted display. If one cell holds 'Jan 22, 2022' and another holds 'Jan 1, 2022', but both are formatted to display as 'Jan-22', the spreadsheet software reads them as different serial numbers and fails to find a match.
Can I fix this by changing the cell format to 'Text'?
No. Simply changing a cell's number format to 'Text' via the formatting menu does not alter the underlying serial number of an existing date. You must use a function like TEXT() or re-enter the data manually as text.
What is the primary difference between VLOOKUP and HLOOKUP?
VLOOKUP (Vertical Lookup) searches for a match in the first column of a dataset and returns a value from a specified column in the same row. HLOOKUP (Horizontal Lookup) searches for a match in the first row of a dataset and returns a value from a specified row in the same column.
