Fix VLOOKUP When Dates and Values Are Stored as Text in Excel
Question details
The user needs to make VLOOKUP recognize dates and lookup values that were imported and stored as text.
- Product
- Microsoft Excel or WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Data imported from another source contains text-formatted dates and lookup values, preventing a VLOOKUP formula from finding matches.
- Observed behavior
- VLOOKUP fails or returns an incorrect result because the lookup values and dates are text instead of real spreadsheet values.
Check the affected cells for left-aligned values or inconsistent date formatting, and confirm the intended date order, such as MDY. Identify the date range and the separate lookup-value range before converting them.
Convert text dates and lookup values with Text to Columns
Use this method when imported dates or lookup keys are stored as text and VLOOKUP cannot match them.
Text to Columns can convert text-formatted dates into real date serial values and normalize text lookup values without changing the visible data. Apply the correct date order only to the date column; process other lookup-value columns without selecting a date format.
Highlight the affected date cells, such as A12:A21, then open the Data tab and choose Text to Columns.
In the Text to Columns wizard, select Delimited and click Next twice. Under Column data format, choose Date and select the correct order, such as MDY, then click Finish.
Select the affected lookup values, such as B12:B21, open Data > Text to Columns, choose Delimited, click Next twice, and click Finish without selecting a date format.
Recalculate or re-enter the VLOOKUP formula and verify that the converted date and lookup values now match the corresponding entries.
Convert imported text values and repair VLOOKUP matches in WPS Spreadsheet
WPS Spreadsheet lets you convert text-formatted dates and lookup values through Data > Text to Columns, then use the repaired data with VLOOKUP in an Excel-compatible workbook.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, open the workbook, and locate the columns used by the VLOOKUP formula.
- 2. Convert the date column: Select the affected date range, choose Data > Text to Columns, select Delimited, continue through the wizard, choose the correct date order such as MDY, and click Finish.
- 3. Convert the lookup values: Select the lookup-value range, choose Data > Text to Columns, select Delimited, continue through the wizard, and click Finish without applying a date format.
- 4. Verify the formula result: Return to the VLOOKUP cell, recalculate or re-enter the formula, and confirm that the expected value is returned.
Frequently Asked Questions
How can I tell whether a date is stored as text?
Text dates are often left-aligned and may not respond correctly to date formatting. Select the cell and check whether changing its number format affects the value; you can also test it with a simple arithmetic operation such as =A1+0.
Which date format should I select in Text to Columns?
Choose the order used by the source data. For example, select MDY when a value such as 06/15/2024 means June 15, 2024. Selecting the wrong order can create incorrect dates.
Do I need to choose a date format for the lookup-value column?
No. For a non-date lookup-value column, use Data > Text to Columns, select Delimited, continue through the wizard, and click Finish without choosing a date format.
Why does VLOOKUP still fail after converting the dates?
Check whether the lookup value and the first column of the lookup range have the same data type, remove leading or trailing spaces, and confirm that the final VLOOKUP argument uses the intended exact-match setting, usually FALSE or 0.
