VLOOKUP Showing Formula? Causes and Fixes in Excel

If VLOOKUP is showing as text instead of returning a result, first turn off Formulas > Show Formulas; if only that cell displays the formula, change its format from Text to General and press F2, then Enter. These are the most common causes in Excel for Microsoft 365 on Windows, and the distinction matters: a formula displayed in the grid is different from a formula that calculates but returns an error.

Start by identifying what is showing

Look at the cell and nearby cells. If many formula cells across the worksheet show expressions such as =VLOOKUP(...), Excel may be in Show Formulas view. If just one or a few cells show the formula characters, those cells may be formatted as text or may contain an apostrophe before the equals sign. If the cell instead shows a value such as #N/A, VLOOKUP is calculating; the problem is with the lookup or its data, not with formula display.

Also distinguish the worksheet cell from the formula bar. When you select a cell containing a working formula, the formula bar normally shows the formula while the cell itself shows its calculated result. That is expected. The issue is present when the formula characters appear in the cell where you expected a result.

Formulas visible in many cells: check Show Formulas.
Formula visible in one cell: check Text formatting and a leading apostrophe.
An error or unexpected value: troubleshoot the lookup formula and its data.

Turn off Show Formulas

In Excel for Microsoft 365 on Windows, go to Formulas > Show Formulas and switch the option off. The keyboard shortcut is Ctrl+`, using the grave-accent key, often located near the upper-left corner of the keyboard. Depending on the keyboard layout, the key may be less obvious; the ribbon command is a reliable alternative. In Excel for Mac, use Formulas > Show Formulas if the command is available on the ribbon; keyboard shortcuts can differ by Mac keyboard and Excel configuration.

This setting changes how formulas are displayed, not the formulas themselves. Excel may widen columns while it displays formulas, so after switching back to results, column widths can remain wider than before. If every formula is still visible, confirm that Show Formulas is off and that the formula cells are not individually stored as text.

Change a text-formatted cell back to a formula

Excel treats entries in cells formatted as Text as text. Therefore, typing a formula into a Text-formatted cell can leave =VLOOKUP(...) visible rather than calculate it. Changing the cell format afterward does not always make Excel reinterpret an entry already stored as text. Re-enter the formula after changing the format:

Select the cell or cells that show the formula.
On the Home tab, open the Number Format list and choose General.
Select the formula cell, press F2 to edit it, then press Enter to commit it again.

F2 followed by Enter prompts Excel to parse the entry as a formula. On many Mac keyboards, the function keys may require the Fn key, or you can edit the cell directly and confirm the entry. If you are correcting several cells, apply General to the range and re-enter each affected formula; changing the format alone may not convert existing text entries.

A leading apostrophe is another reason Excel preserves the formula as text. An entry like '=VLOOKUP(...) tells Excel to treat what follows as text. Remove the apostrophe, then confirm the formula again. An apostrophe may not be obvious in the cell itself, so inspect the formula bar when the format appears correct but the expression still will not calculate.

Check that the formula is entered correctly

VLOOKUP has four arguments: the lookup value, the table range, the column number to return, and whether to use an exact or approximate match. For most lookup lists, use FALSE for an exact match. In this example, product codes are in column A, descriptions are in B, prices are in C, and the code to find is in E2.

With product codes in A2:A100, descriptions in B2:B100, prices in C2:C100, and the code to find in E2, enter this formula in F2:

=VLOOKUP(E2,A2:C100,3,FALSE)

If E2 contains P-104 and the matching row in A2:C100 has 18.50 in column C, the result is 18.50. VLOOKUP searches only the first column of its table range—in this case, column A—and returns a value from the numbered column in that range. The number 3 means the third column of A2:C100, which is C.

When copying the formula down, lock the table range so it does not shift. With the same layout, enter this version in F2; for a matching P-104 row with price 18.50, it returns 18.50:

=VLOOKUP(E2,$A$2:$C$100,3,FALSE)

The dollar signs hold the table range steady as the formula is copied, while E2 remains relative and can change to E3, E4, and so on. In some regional settings, Excel uses semicolons rather than commas as formula separators. The examples here use commas; if Excel rejects the separators, use the separator configured for your system.

Understand formula display versus calculation

Calculation mode is a separate setting. If calculation is set to Manual, Excel may keep showing an old calculated value until formulas recalculate. That typically causes a stale result, not the literal formula text. To check it in Windows, open Formulas > Calculation Options and select Automatic if that is appropriate for the workbook. Press F9 to calculate open workbooks; on some laptops you may need Fn+F9. Mac shortcuts can differ, and the Calculation Options ribbon command is also available in Excel for Mac.

Manual calculation can be intentional in large workbooks because recalculating many formulas may take time. If you switch to Automatic, the workbook may recalculate more often. A workbook can also be configured to calculate automatically while a particular formula still returns an error or a wrong match; calculation mode does not repair incorrect lookup data.

If it calculates, diagnose the result instead

Once the cell shows a result or an error rather than the formula itself, check the lookup logic. A frequent VLOOKUP limitation is that the lookup value must be in the first column of the selected table range. If the key is in column C but the table range starts at A, VLOOKUP cannot search C and return a value to its left. Choose a table range whose first column contains the key, or consider a different lookup method.

#N/A: with FALSE, there may be no exact match. Check for extra spaces, different data types, or a code that is absent from the first table column.
#REF!: the requested column number may exceed the number of columns in the table range, or a referenced column may have been deleted.
Wrong value: verify the column number and confirm the fourth argument is FALSE when you need an exact match. Omitting that argument uses approximate matching, which can return an unexpected result if the first column is not sorted as required.
Number and text mismatch: a number stored as text may not match the same digits stored as a number. Check both the lookup cell and the table key column.

These are lookup-result problems, not reasons that Excel prints the formula characters in the cell. Fixing them will not help if Show Formulas is on or the cell is still storing the formula as text.

Prevent the problem when importing or pasting data

Text formatting is often carried into a worksheet when cells are prepared for codes, imported from a text file, or copied from another workbook. Before entering formulas in a preformatted range, check the Number Format list and use General. If you paste a formula and it appears literally, inspect the destination format and the formula bar before rewriting the whole sheet.

It is also useful to keep lookup keys consistent. For example, store product codes as text in both the lookup cell and the table, especially if codes include leading zeros such as 0042. Excel can remove leading zeros from numeric entries, making two values that look related behave differently. Consistent formatting and a clearly defined first lookup column make VLOOKUP easier to maintain.

Frequently Asked Questions

Why is Excel showing all my formulas instead of answers?
Show Formulas is likely enabled. Turn it off with Formulas > Show Formulas or press Ctrl+` on Windows. If only one cell is affected, check its format and whether it has a leading apostrophe.

Why does VLOOKUP still show as text after I choose General?
Changing the format does not necessarily reprocess text already in the cell. After choosing General, select the cell, press F2, and press Enter. Also remove any apostrophe before the equals sign.

Does Manual calculation make VLOOKUP display its formula?
Usually not. Manual calculation can leave a calculated result out of date, but literal formula characters generally point to Show Formulas or text storage. Check those first, then review Calculation Options if results are stale.

Why does my VLOOKUP show #N/A instead of the answer?
With an exact match, #N/A means Excel did not find a matching value in the first column of the table range. Check spelling, spaces, number-versus-text differences, and whether the lookup key is actually in that first column.

Is it normal to see the formula in the formula bar?
Yes. Selecting a formula cell normally shows its formula in the formula bar while the cell displays the calculated result. It is a problem only when the worksheet cell itself displays the formula where you expect a value.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.