Fix a VLOOKUP Formula That Changes or Displays as Text in Excel
Question details
Fix a VLOOKUP formula that changes unexpectedly when copied or appears as text instead of calculating.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to copy a VLOOKUP formula down a column while keeping its lookup range fixed and displaying calculated results.
- Observed behavior
- The lookup range may shift when the formula is copied, or the formula may appear as text if the cell is formatted as Text or starts with an apostrophe.
Check the lookup value cell, the worksheet name, and the first and last columns of the lookup range. VLOOKUP searches for the value in the first column of that range.
Lock the VLOOKUP range before copying the formula
Use absolute references to keep the lookup table fixed while the lookup value changes by row.
Select the formula cell and enter =VLOOKUP($B5,'RZ List'!$E$2:$F$44,2,FALSE) in the formula bar. Adjust the cell references and sheet name to match your workbook.
Press Enter, then drag the cell's fill handle down the column. The $ signs keep E2:F44 fixed, while $B5 keeps column B fixed and allows the row number to change.
To show a message instead of an error, edit the formula to =IFERROR(VLOOKUP($B5,'RZ List'!$E$2:$F$44,2,FALSE),"Not found").

Make a VLOOKUP formula calculate instead of displaying as text
Use this fix when the cell shows the formula itself rather than its result.
Fix and copy VLOOKUP formulas in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, lock the lookup range, and change text-formatted formula cells to General so the formulas can calculate.
- 1. Open the workbook: In WPS Spreadsheets, select Open and choose your Excel workbook.
- 2. Enter the formula: Select the result cell and enter =VLOOKUP($B5,'RZ List'!$E$2:$F$44,2,FALSE), adjusting the references for your sheet.
- 3. Fix text formatting if needed: If the formula displays as text, select the cell, choose Home > Number Format > General, remove any leading apostrophe in the formula bar, and re-enter the formula.
- 4. Fill down and save: Drag the cell's fill handle down to copy the formula, then select Save to keep the changes.

Frequently Asked Questions
Why does my VLOOKUP range change when I copy the formula?
Relative references move with the copied formula. Add dollar signs to the lookup range, such as $E$2:$F$44, to keep it fixed.
Why does changing the cell format to General not calculate my formula immediately?
After choosing Home > Number Format > General, edit the cell and press Enter to re-enter the formula. Also remove any apostrophe before the equals sign.
Why does the worksheet name have single quotes around it?
Worksheet names containing spaces need single quotes in a formula, as in 'RZ List'!$E$2:$F$44.
How can I show a message when VLOOKUP finds no match?
Wrap the formula in IFERROR, for example =IFERROR(VLOOKUP($B5,'RZ List'!$E$2:$F$44,2,FALSE),"Not found"). Note that IFERROR catches other formula errors too.
What does FALSE do in VLOOKUP?
FALSE requests an exact match. VLOOKUP searches for the lookup value in the first column of the specified range.




