VLOOKUP Not Working in Excel? Show the Formula Fix

If VLOOKUP appears in a cell instead of returning an answer, first turn off Formulas > Show Formulas in Excel for Microsoft 365 on Windows. If only one cell shows the formula, change that cell’s format from Text to General and re-enter the formula; also check for an apostrophe or space before the equals sign.

First determine what “showing formula” means

There are two common situations that look similar but have different causes. If many cells across the worksheet display expressions such as =VLOOKUP(...) instead of their results, Excel is probably in Show Formulas mode. If just one cell displays its expression, that cell may be treating the entry as ordinary text rather than evaluating it.

Look at the cell and the formula bar. In Show Formulas mode, Excel displays formulas in the worksheet grid, often making columns wider so the expressions fit. The formula bar also shows the formula, as usual. In a text-entry problem, the formula may be visible in the cell while other formula cells continue to show calculated results. Identifying which pattern you have helps avoid changing a working formula unnecessarily.

Also separate this issue from an error such as #N/A, #REF!, or #VALUE!. Those messages mean Excel has evaluated the formula but encountered a lookup or reference problem. A formula displayed literally has usually not been evaluated, or the worksheet is set to show formulas.

Turn off Show Formulas

In Excel for Microsoft 365 on Windows, select Formulas > Show Formulas to switch the display back to calculated results. The command is a toggle: selecting it again turns formula display on. You can also press Ctrl+` (the grave-accent key, usually near the upper-left corner of a US keyboard). This setting affects the worksheet display, not the formula itself, so turning it off does not delete or change your VLOOKUP.

On a Mac, use Formulas > Show Formulas; the keyboard shortcut can depend on the keyboard layout. In Excel for the web, look for Formulas > Show Formulas on the ribbon when available. If you cannot find the command, the ribbon route is a better guide than assuming a Windows shortcut will work on another keyboard.

Show Formulas can be enabled for a useful reason: it lets someone inspect formulas across a sheet without selecting cells one at a time. It can also be switched on accidentally while using the shortcut. If the whole sheet suddenly seems to contain formulas, try the toggle before editing any cells.

Fix a cell formatted as Text

A cell formatted as Text tells Excel to store what you type as text. If you enter a VLOOKUP formula while the cell already has that format, Excel may display the characters instead of calculating them. Changing the format alone may not be enough: the existing entry can remain text until you make Excel parse it again.

Select the cell that displays the formula.
On the Home tab, open the Number Format list and choose General.
Click in the formula bar or edit the cell, then press Enter to re-enter the formula. You can also delete the entry and type it again after changing the format.

If a whole range was affected, select the range and change its format to General, then re-enter each formula or use Excel’s Text to Columns tool to convert text entries where appropriate. Be careful with bulk conversions: the same selection might include labels or codes that are meant to remain text. Check a few representative cells before applying a conversion broadly.

Text formatting is especially easy to overlook when a cell was copied from an imported file, a template, or a column previously used for identifiers. A number format such as Currency or Percentage generally does not prevent a formula from calculating; Text is the format to investigate when the formula itself remains visible.

Remove characters that stop formula entry

Excel recognizes a formula when it is entered as a formula, usually beginning with an equals sign. A leading apostrophe tells Excel to keep the following content as text. A space before the equals sign can also interfere with recognition, particularly when the cell is General rather than Text. Inspect the start of the entry in the formula bar, and remove any unwanted character before the equals sign.

For example, if the entry begins with an apostrophe followed by =VLOOKUP, remove the apostrophe and press Enter. If you see a blank space before the equals sign, delete it as well. Do not remove characters that are part of a legitimate text value or a formula argument; the goal is to make the first character of the formula the equals sign.

Another possibility is that the formula was pasted with a leading apostrophe from a document or exported data. A formula copied from a web page may also contain typographic quotation marks or other altered characters. Excel formulas require ordinary straight quotation marks around text values, not curly quotation marks. If the formula is meant to contain no text criteria, retyping it directly in Excel can be a quick way to rule out pasted characters.

Check the VLOOKUP structure

Once Excel evaluates the entry, verify that the formula follows VLOOKUP’s argument order: the value to find, the table range, the column number to return, and whether to use an exact or approximate match. For most everyday lookups—such as finding a product price by product code—use FALSE for an exact match. VLOOKUP searches for the lookup value in the first column of the selected table range and returns a value from a column to its right.

With product codes in A2:A4, prices in B2:B4, and the code to search for in D2, enter this in E2:

=VLOOKUP(D2,A2:B4,2,FALSE)

If D2 contains P-102 and the matching row has 18.5 in B3, the result is 18.5. The range starts in column A, where the lookup codes are, and the column index 2 asks VLOOKUP to return the second column of A2:B4—column B. If the lookup column is not the leftmost column of the selected range, VLOOKUP will not search it as intended.

In installations that use a regional list separator, Excel may display or require semicolons instead of commas between arguments. The example above uses commas, as in standard US-style formula syntax. If Excel rejects an otherwise correctly structured formula, check the separator used by other working formulas in that installation.

Diagnose errors after the formula calculates

When the cell stops showing the literal formula but returns an error or an unexpected result, the issue has moved from formula display to lookup logic. Common checks include:

#N/A: The lookup value was not found as an exact match. Check for misspellings, extra spaces, or a difference between a number stored as text and a numeric value.
Wrong result: Confirm the column index counts from the first column of the selected table range, not from the worksheet’s column A.
#REF!: The requested return-column number may be greater than the number of columns in the table range, or a referenced column may have been deleted.
Unexpected approximate match: If the final argument is omitted or set to TRUE, VLOOKUP uses approximate-match behavior. For an exact code or name lookup, use FALSE.

For instance, if a cell looks like it contains the number 102 but one copy is stored as text and the other as a number, an exact lookup may fail. Check the source and lookup value for consistent data types. Likewise, a name that appears identical can contain a trailing space. Cleaning the source data or correcting the lookup value is preferable to changing the match type when the task requires an exact result.

Check calculation settings separately

Manual calculation is a different problem from a formula being displayed as text. If calculation is set to Manual, Excel can retain an old calculated result until you recalculate; it does not normally turn the cell into a literal display of the formula. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options > Automatic. The corresponding calculation controls are also on the Formulas tab in Excel for Mac.

If a formula is already evaluating but its answer appears stale, set calculation to Automatic or recalculate the workbook. On Windows, F9 recalculates formulas in open workbooks; Shift+F9 recalculates the active worksheet. Keyboard behavior can vary on laptops and Macs, where function-key settings may require using the Fn key. These commands are useful for stale results, not for fixing a formula stored as text.

When to keep VLOOKUP or choose another function

VLOOKUP remains useful when the value to find is in the leftmost column of a compact table and the return value is to its right. It is familiar, works in many older Excel files, and is straightforward for a fixed lookup table. Its main structural limitation is that it cannot return a result from a column to the left of the lookup column using the usual table-range arrangement.

In Microsoft 365 and Excel 2021 or later, XLOOKUP is an alternative for many lookup tasks. It can search one range and return a corresponding value from a separate range, including a range to the left. It is not available in some older Excel versions, so consider who will open the workbook before replacing a VLOOKUP in a file shared with people using older software. For a formula that is showing literally, however, switching functions is not the first fix: correct the display mode, cell format, or entry first.

Keep a lookup table organized and predictable whichever function you use. Put the lookup keys in a clearly identified column, avoid duplicate keys when each key should identify one record, and keep values in consistent formats. These habits make errors easier to diagnose and help prevent an apparently valid lookup from returning the wrong row.

Frequently Asked Questions

Why are all my Excel formulas showing instead of results?
Show Formulas is likely enabled for the worksheet. Select Formulas > Show Formulas to turn it off, or use Ctrl+` on Windows. If only one cell is affected, check whether it is formatted as Text or begins with an apostrophe.

Why does VLOOKUP show the formula in one cell only?
The cell may be formatted as Text, or the entry may have a leading apostrophe or space. Change the format to General, remove any unwanted prefix, and re-enter the formula so Excel evaluates it.

Does calculation mode make Excel show formulas?
No. Manual calculation can leave a result outdated, but it is not the same as displaying the formula text. Use Show Formulas for visible expressions, and check Calculation Options when a calculated result is stale.

Why does VLOOKUP return #N/A instead of the answer?
Excel is evaluating the formula but cannot find an exact match. Check spelling, extra spaces, data types, and whether the lookup value appears in the first column of the selected table range. Confirm that the final argument is FALSE for an exact match.

Will changing the cell to General erase my formula?
No. Changing the format does not normally erase the cell contents, but a formula that was entered while the cell was Text may need to be re-entered afterward. Select the cell, choose General, then edit and confirm the formula.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.