VLOOKUP Not Working? Fix Excel Displaying the Formula

If Excel displays =VLOOKUP(...) in a cell instead of a lookup result, first turn off Formulas > Formula Auditing > Show Formulas, or press Ctrl+` in Excel for Microsoft 365 on Windows. If the formula appears in just one cell, that cell may be formatted as text or may contain a leading apostrophe; set it to General and re-enter the formula.

What it means when Excel displays a formula

A visible formula and a failed lookup are two different problems. When Excel displays the actual characters =VLOOKUP(...) in the grid, rather than the value the formula should return, Excel is usually showing formulas for the worksheet or treating that cell’s contents as text. In either case, it has not displayed the usual calculated result in that cell.

By contrast, a VLOOKUP that calculates but cannot find a matching value commonly returns #N/A. Other formula mistakes can return errors such as #REF! or #VALUE!. Those errors indicate that Excel has evaluated a formula and encountered a problem; they are not the same as seeing the formula characters themselves.

Start by checking whether the issue affects every formula in the sheet or only one cell. That quick distinction usually points to the right fix: a worksheet-wide display setting for many cells, or cell formatting and entry details for an isolated formula.

Turn off Show Formulas

Excel has a display option that replaces calculated results with their formulas throughout the active worksheet. It is useful when reviewing or auditing formulas, but it can make VLOOKUP seem broken if it was switched on accidentally. When enabled, formula cells may also take up more space because the formula text is longer than the result.

Open the worksheet where the formulas are visible.
Select Formulas > Formula Auditing > Show Formulas to switch the display back to calculated results. If the option is already selected, clicking it turns it off.
Alternatively, press Ctrl+` on Windows. The grave accent key is often near the top-left of the keyboard, below Esc; its location can vary by keyboard layout.

The shortcut toggles the setting, so pressing it again can turn formula display back on. On a Mac, use Control+` where that key combination is available, or use Formulas > Show Formulas on the ribbon. Depending on the keyboard, the accent key may require a different key combination. This setting changes how formulas are displayed, not the formulas themselves, so turning it off should reveal results without requiring you to rebuild the lookups.

Check whether the formula is stored as text

If only one cell shows the formula, check whether Excel is treating its contents as text. A cell formatted as Text before a formula is entered may keep something like =VLOOKUP(E2,$A$2:$C$100,3,FALSE) as literal characters rather than evaluating it. A leading apostrophe has a similar effect: when you type '=VLOOKUP(...), Excel uses the apostrophe to mark the entry as text, and the apostrophe itself may not appear in the cell.

To check the number format, select the cell and look at the number-format box on the Home tab. If it says Text, choose Home > Number Format > General. Changing the format alone may not convert a formula that Excel has already stored as text. After changing it to General, activate the cell, press F2, then press Enter to make Excel read the entry again. You can also delete the cell contents and type the formula again after setting the format.

If you suspect a leading apostrophe, click the cell and inspect the formula bar. Remove the apostrophe, then confirm the entry. A visible formula that starts with an apostrophe or that remains unchanged after you press Enter is a clue that Excel has stored text, not a calculated formula.

Re-enter the formula correctly

Once the cell is set to General and worksheet formula display is off, re-enter the lookup if needed. For a Windows desktop workflow, select the cell, type the formula beginning with an equals sign, and press Enter. If Excel continues to show the text, look for an apostrophe before the equals sign and confirm that the entry does not contain an unintended character at its start.

For example, with product IDs in A2:A4, product names in B2:B4, prices in C2:C4, and the ID to find in E2, enter this in F2:

With IDs in A2:A4, names in B2:B4, prices in C2:C4, and the lookup ID in E2, the formula in F2 is =VLOOKUP(E2,$A$2:$C$4,3,FALSE).

If E2 contains P-102 and row 3 contains that ID with a price of 18.50 in C3, the formula returns 18.50. The dollar signs keep the table range fixed if you copy the formula to another row. The final argument, FALSE, asks Excel for an exact match, which is generally the safer choice for product codes, names, and other identifiers.

How VLOOKUP is supposed to work

VLOOKUP searches down the first column of a table range for a value, then returns a value from another column in the same row. Its fourth argument controls the match type. With FALSE, Excel looks for an exact match; with TRUE or an omitted argument, it can use an approximate match, which expects the first column to be sorted appropriately.

In the example, the table range starts in column A because the product ID is in A. The column index of 3 tells Excel to return a value from the third column of that selected range, which is column C. The index counts columns within the range, not worksheet column letters. If the range began at B instead, a column index of 3 would point to worksheet column D.

VLOOKUP also searches only from left to right: its lookup value must be in the first column of the chosen range, and the return column must be to its right. If a formula is being evaluated but returning an unexpected value, check the range and column index before assuming that the display setting is at fault.

When the formula shows an error instead

If the cell displays an error rather than formula text, Excel has evaluated the formula. The correction depends on the error and the lookup data:

#N/A: Excel did not find an exact match, or the lookup value and table value differ. Check for extra spaces, different spellings, or a number stored as text on one side and a number on the other.
#REF!: The column index may ask for a column beyond the right edge of the selected range, or a referenced cell or range may have been deleted.
#VALUE!: An argument may be invalid, or a value supplied to the formula may not have the expected type.
#NAME?: Excel may not recognize the function name or another name used in the formula. Check the spelling and any named ranges.

A common misconception is that changing FALSE to TRUE fixes a missing match. It can instead produce an unintended approximate result, particularly when the lookup column is not sorted. For codes and exact identifiers, retain FALSE and investigate why the values differ.

Other checks after formula display is fixed

After the formula text disappears, confirm that the returned value is the one you intended. A correct lookup can still return an unexpected result if the lookup table contains duplicate IDs: VLOOKUP returns the first matching row it finds, so later duplicate records may be ignored. Also verify that the range includes the row containing the answer and that the selected return column contains the data you want.

Calculation mode is another possible source of confusion, but it causes a different symptom. If Excel is set to manual calculation, a formula may show an outdated result until recalculated; it does not normally make a formula appear as text. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options. If Manual is selected, choose Automatic or recalculate the workbook. F9 recalculates open workbooks; Shift+F9 recalculates the active worksheet. On some Mac keyboards, you may need to hold Fn to use a function key.

If formulas in every cell show text, check Show Formulas first. If one cell remains affected after switching that off, check its number format and entry. If the cell then shows an error or a wrong answer, troubleshoot the lookup itself. Keeping those symptoms separate prevents unnecessary changes to a formula that may be structurally correct.

Frequently Asked Questions

Why does Excel show the formula instead of the answer?
Usually, Show Formulas is enabled for the worksheet, or the individual cell is storing the entry as text. Turn off Show Formulas first; if just one cell is affected, set it to General and re-enter the formula.

How do I turn off formula view in Excel?
On Windows, select Formulas > Formula Auditing > Show Formulas, or press Ctrl+`. On a Mac, use the Show Formulas control on the Formulas tab or try Control+` if your keyboard supports it.

Why does changing a cell from Text to General not fix it?
Changing the format does not always make Excel reinterpret a formula already stored as text. After setting General, press F2 and then Enter, or clear the cell and type the formula again.

Why does only one VLOOKUP cell display its formula?
That cell may have been formatted as Text before the formula was entered, or the entry may begin with an apostrophe. Check the number format and formula bar, then re-enter the formula without the apostrophe.

Does #N/A mean Excel is displaying the formula?
No. #N/A means Excel evaluated the lookup but did not find a match under the formula’s match rules. Check the lookup value, table’s first column, and whether the values have matching types and characters.

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.