Fix WPS Spreadsheets VLOOKUP Dates Showing as Numbers and #N/A
Question details
The user needs to correct a VLOOKUP formula that returns dates as numbers or produces #N/A after being filled down.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A VLOOKUP formula is copied to multiple rows and retrieves date values from a product or orders table.
- Observed behavior
- Returned dates appear as serial numbers, and valid records may return #N/A because the relative lookup range shifts when the formula is copied.
Identify the lookup-value cell, the table range, and the column containing the date results. Select one affected formula cell in WPS Spreadsheets so you can edit the references before applying the fix to the full result column.
Lock the VLOOKUP range and format the returned dates
Use this solution when the formula works in one row but returns #N/A after copying or displays dates as serial numbers.
The lookup value should remain relative so it changes by row, while the table range must be absolute so it stays fixed. Date values are stored as serial numbers internally, so the result cells must use a date format to display them as calendar dates.
Click an affected result cell, then click the formula bar and change the formula to use dollar signs around the lookup range. For example, use =VLOOKUP(B2,Orders!$A$2:$D$5010,2,FALSE). Keep B2 relative so it becomes B3, B4, and so on when copied.
While the lookup range is selected inside the formula bar, press F4 to cycle through reference types until the range appears as $A$2:$D$5010. Confirm that the sheet name and range cover all source records.
Press Enter, select the corrected formula cell, and drag its fill handle down the result column or use Copy and Paste. Check that only the lookup-value row changes while the absolute range remains unchanged.
Select the entire result column, open the Home tab, choose the Number Format menu, and select Short Date. The serial values should now display as dates.
Use WPS Spreadsheets to repair VLOOKUP date results
WPS Spreadsheets provides familiar formula editing, absolute-reference controls, fill tools, and date formatting so you can correct VLOOKUP formulas and display returned dates properly.
- 1. Open the spreadsheet: Open the workbook in WPS Spreadsheets and locate the VLOOKUP result column.
- 2. Fix the formula: Select a result cell and update the formula so the lookup range uses absolute references, such as =VLOOKUP(B2,Orders!$A$2:$D$5010,2,FALSE).
- 3. Fill the corrected formula: Copy the corrected cell down the result column and verify that the lookup value changes by row while the table range remains fixed.
- 4. Format the dates: Select the results, open Home > Number Format, and choose Short Date.
Frequently Asked Questions
Why does VLOOKUP display a date as a number in WPS Spreadsheets?
Spreadsheet dates are stored as serial numbers. Select the VLOOKUP result cells, open Home > Number Format, and choose Short Date or another date format.
Why does my VLOOKUP return #N/A only after I copy it down?
The lookup range is probably relative and moves with each copied row. Edit the formula so the table range is absolute, such as $A$2:$D$5010, while leaving the lookup cell relative.
How do I quickly make a VLOOKUP range absolute?
Click the formula bar, select the lookup range, and press F4 until dollar signs appear before both the column letters and row numbers, for example $A$2:$D$5010.
Should the VLOOKUP lookup value also be absolute?
Usually no. Keep the lookup value relative, such as B2, when filling the formula down so it changes to B3, B4, and later rows. Lock it only when every row must use the same lookup cell.
What should I check if #N/A remains after locking the range?
Confirm that the lookup value exactly matches a value in the first column of the selected table range, check for extra spaces or mismatched text and numbers, and make sure FALSE is used for an exact match.
