Function Problems

Fix a VLOOKUP Formula That Changes or Displays as Text in Excel

Phi Hung VoPhi Hung Vo Nov 22, 2026 868 views

Question details

Fix a VLOOKUP formula that changes unexpectedly when copied or appears as text instead of calculating.

Fix a VLOOKUP Formula That Changes or Displays as Text in Excel
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.
Before you start

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.

Solution 1Recommended

Lock the VLOOKUP range before copying the formula

Use absolute references to keep the lookup table fixed while the lookup value changes by row.

1
Edit the formula

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.

2
Copy the formula down

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.

3
Optionally handle missing matches

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").

Lock the VLOOKUP range before copying the formula
Check the formula references: The single quotes are needed around worksheet names containing spaces. IFERROR also hides errors other than missing matches, so check the original formula if the message appears unexpectedly.
Work with VLOOKUP in WPS

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. 1. Open the workbook: In WPS Spreadsheets, select Open and choose your Excel workbook.
  2. 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. 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. 4. Fill down and save: Drag the cell's fill handle down to copy the formula, then select Save to keep the changes.
Keep VLOOKUP lookup ranges fixed when copying formulasOpen and edit Microsoft Excel XLSX and XLS workbooksUse a familiar spreadsheet interface to edit formulas and cell formats
QA img-9

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.