VLOOKUP Not Working? Fix Formulas Showing in Excel

If Excel displays the VLOOKUP formula itself instead of a result, first turn off Formulas > Formula Auditing > Show Formulas; if only one cell is affected, change that cell’s format from Text to General, then edit and re-enter the formula. A formula displayed as text is different from a VLOOKUP that calculates and returns an error such as #N/A, so identify which problem you have before changing the lookup.

What it means when a formula appears

A working formula normally shows its calculated result in the cell, while the formula appears in the formula bar when you select that cell. If the cell itself shows something like =VLOOKUP(E2,A2:C100,3,FALSE), Excel may be displaying formulas across the worksheet, or it may be treating that entry as ordinary text rather than calculating it.

Start by checking how widespread the issue is. If many formula cells show their expressions, a worksheet display setting is the likely cause. If one cell—or a group of recently pasted cells—shows the expression, check its number format and the way the formula was entered. If the cell shows a value, an error, or a blank instead, Excel is probably calculating the formula and the lookup logic needs investigation.

These distinctions matter because changing a lookup range will not fix a cell stored as text, and changing a cell format will not fix an incorrect lookup column number. Diagnose the display first, then check the formula’s result and inputs.

Check the Show Formulas setting

Excel can switch a worksheet into a mode that displays formulas in cells instead of their results. In Excel for Microsoft 365 on Windows, go to Formulas > Formula Auditing > Show Formulas. If the command is active, click it to turn the display off. The cells should return to showing calculated values, although column widths may remain unusually wide because Excel expanded them to fit the formulas.

You can also toggle this setting with Ctrl+` on Windows; the backtick is usually on the key shared with the tilde, near the upper-left corner of the keyboard. On a Mac, the corresponding shortcut is generally Control+`, though the key’s location can vary by keyboard layout. If the shortcut is inconvenient or does not work with your layout, use the Show Formulas command on the Formulas tab.

This setting applies to the worksheet, not just one VLOOKUP cell. It can make a sheet look as if every formula has stopped working, even though the calculations are still present. Toggling it back does not alter the formulas or their underlying results; it changes how Excel displays them.

Check whether the cell is formatted as Text

If just one formula appears, select the cell and look at Home > Number Format. A cell set to Text treats an entry beginning with an equals sign as text, so Excel displays the characters instead of evaluating the expression. Pasting a formula from a text file or a system that labels the destination column as text can cause this.

To correct it, select the affected cell, choose General from the Number Format list, then make Excel parse the entry again. One dependable method is to click in the formula bar, place the cursor in the formula, and press Enter. You can also press F2 and then Enter on Windows. Merely changing the format from Text to General does not reliably convert an already-entered text string into a live formula; re-entering it is the important step.

Look for other text markers too. An apostrophe typed before the equals sign tells Excel to store the entry as text; the apostrophe may not appear in the cell, but can be visible in the formula bar. A leading space before the equals sign can also prevent Excel from recognizing the entry as a formula. Remove the marker or space, keep the equals sign at the start, and re-enter the formula.

If a whole imported column is affected, select the cells and change their format to General, then re-enter the formulas. For a large range, test the approach on a copy or a few cells first, since a pasted column can contain a mix of formulas and literal text that should not all be converted.

Re-enter the formula and check calculation

After correcting the display or text format, verify that the entry begins with = and contains balanced parentheses. In Excel’s default settings, function arguments are separated by commas. Some regional settings use semicolons as the list separator, so if Excel rejects a correctly structured formula, check the separator expected by your installation rather than changing the lookup logic.

Calculation mode is another setting worth checking, but it usually causes stale results rather than formulas visibly appearing in cells. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and select Automatic if the workbook is set to Manual. In Manual mode, formulas may not update when source data changes until calculation is triggered. Changing to Automatic will not turn a text entry into a formula; fix the cell format and re-enter the formula separately.

Once Excel recognizes the entry as a formula, select the cell and inspect the formula bar. If the formula bar contains the intended VLOOKUP but the cell shows an error, you have moved past the display problem. The error or unexpected result now provides a clue about the lookup value, table range, match setting, or source data.

Confirm the VLOOKUP structure

VLOOKUP searches for a value in the leftmost column of a table range and returns a value from another column in the same row. Its arguments are the value to find, the table range, the number of the return column counted from the left edge of that range, and the match mode. For an exact match, specify FALSE as the fourth argument; leaving that argument out can select approximate matching instead.

Suppose the lookup IDs are in A2:A100, names are in B2:B100, departments are in C2:C100, and the ID to find is in E2. With those columns and rows, this formula returns the department for an exact ID match: =VLOOKUP(E2,$A$2:$C$100,3,FALSE). If E2 contains an ID whose row has “North” in column C, the result is North. The 3 means the third column in the specified range A:C, not worksheet column C in every possible range.

The dollar signs keep the table range fixed when you copy the formula down, while E2 changes to E3, E4, and so on. Without fixed references, copying a formula can shift the lookup table and produce wrong answers or errors. To add them, select a range reference in the formula and press F4 on Windows; on a Mac, the equivalent key may depend on keyboard settings, and you can type the dollar signs directly.

VLOOKUP can return data only from a column to the right of the column it searches. If the lookup key is in column C but the value you need is in column A, rearrange the data, use a different lookup approach, or use XLOOKUP where available. Also check that the return-column number does not exceed the number of columns in the selected range.

When VLOOKUP calculates but still gives the wrong result

A formula that displays a value or an error is being evaluated, so focus on these common causes rather than Show Formulas:

#N/A: With exact matching, Excel did not find a matching lookup value. Check for leading or trailing spaces, different spellings, and values stored as different types, such as the number 123 in one place and text “123” in another.
#REF!: The return-column number points outside the table range, or a referenced column was deleted. Recheck the third argument and the range’s width.
A plausible but incorrect result: Confirm that the fourth argument is FALSE for an exact match. Approximate matching can return a result based on the closest lower value and generally requires the first column to be sorted in ascending order.
A blank or unexpected column value: Check that the range starts with the lookup column and that the column index counts from that range’s first column. The index is not necessarily the letter of the worksheet column.

VLOOKUP’s exact matching is not case-sensitive, so it will not distinguish codes such as “ab12” and “AB12.” It can also match wildcard characters in exact-match searches: an asterisk represents any sequence of characters and a question mark represents one character. If a lookup key contains those characters literally, wildcard interpretation can affect the match. These are lookup-behavior issues, not signs that Excel is displaying the formula incorrectly.

When XLOOKUP is a useful alternative

If the lookup value is not in the leftmost column, or you want to avoid counting a return-column index, XLOOKUP can be simpler. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older Excel versions. It does not solve a cell formatted as Text or a worksheet in Show Formulas mode, so fix those display problems first.

With lookup IDs in C2:C100, departments in A2:A100, and the ID to find in E2, this formula returns the matching department or “Not found” if there is no match: =XLOOKUP(E2,C2:C100,A2:A100,"Not found"). Unlike VLOOKUP, the return range can be to the left of the lookup range. If you share a workbook with people using older Excel versions, check compatibility before replacing VLOOKUP with a newer function.

Prevent formula-display problems in future

Before entering or pasting lookup formulas, check that destination cells use General rather than Text, and paste formulas into the intended range rather than into a column imported as text. Use an exact-match argument when the task requires an identical key, and lock the table range before filling a formula down. Keep the lookup column as the first column of the VLOOKUP range, and verify that keys have consistent types and formatting.

When a workbook is shared, remember that Show Formulas is a worksheet view setting that can make the sheet look broken to another reader. If formulas suddenly appear throughout a sheet, check that setting before editing dozens of cells. If only a few cells are affected, inspect their formats and formula-bar contents instead. Keeping a backup before making broad changes to imported data is sensible, particularly when cells contain a mixture of formulas and text.

Frequently Asked Questions

Why does Excel show the formula instead of the answer?
If many cells show formulas, turn off Show Formulas on the Formulas tab. If one cell does, check for Text formatting, a leading apostrophe, or a space before the equals sign, then re-enter the formula.

Does changing Text to General fix a formula immediately?
Not always. Changing the format affects future entries, but an existing text string may remain text. After selecting General, edit the entry and press Enter so Excel evaluates it.

Why is VLOOKUP showing #N/A instead of the formula?
That means Excel is calculating the formula but cannot find an exact match, if the formula uses FALSE. Compare the lookup value with the first column of the range and check for spaces or number-versus-text differences.

Can Show Formulas be turned on accidentally?
Yes. The keyboard toggle is easy to press, especially on a keyboard where the backtick key is near the top-left. Use Formulas > Formula Auditing > Show Formulas to check and switch the display.

Why does VLOOKUP return the wrong row?
Check the match mode first. For an exact match, use FALSE; approximate matching can return a nearby result and relies on sorted lookup data. Also confirm that the table range begins with the lookup column.

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.