VLOOKUP Formula Not Working? Fix Show Formula

If Excel displays =VLOOKUP(...) in a cell instead of a result, first turn off Show Formulas with Ctrl+` on Windows. If only one cell shows the formula, check whether that cell is formatted as Text or the formula begins with an apostrophe; if it shows an error, troubleshoot the lookup separately.

Why Excel shows a VLOOKUP formula

There are two different situations that can look alike. Excel may be displaying formulas across the worksheet instead of their calculated results, or a particular cell may be treating the formula as ordinary text. A third possibility is that the formula is calculating but returning an error such as #N/A. The right fix depends on which one you see.

Look at nearby cells to narrow it down. If many cells show expressions beginning with an equals sign, Show Formulas is probably on. If other formulas show results but one VLOOKUP is visible as text, check that cell’s format and how the formula was entered. If the cell shows an error rather than the formula itself, go to the error diagnosis steps below.

Turn off Show Formulas

In Excel for Microsoft 365 on Windows, press Ctrl+` to switch between displaying formulas and displaying their results. The grave accent key is commonly near the upper-left corner of the keyboard, but its location can vary by keyboard layout. You can also use the ribbon: choose Formulas > Formula Auditing > Show Formulas. If the option is selected, click it to turn it off.

When Show Formulas is enabled, Excel displays formulas throughout the worksheet instead of their answers. The cells still contain formulas; the display setting has changed. Columns may expand to make the expressions visible, so their widths can also look different. Turning the setting off returns the normal result display.

In Excel for Mac, use the Show Formulas command on the Formulas tab if the keyboard shortcut differs on your keyboard or layout. In Excel for the web, use the Formulas tab’s Show Formulas option when available. The ribbon command is useful when you are unsure which key combination applies to your version.

Fix a formula displayed in just one cell

If the VLOOKUP cell alone shows the expression, select it and look at the Number Format box on the Home tab. A cell formatted as Text treats an entry such as =VLOOKUP(...) as a text string rather than calculating it. Change the format to General, then re-enter the formula. Merely changing the format may not convert an existing text entry, so click in the formula bar and press Enter after changing the format.

Also check for an apostrophe before the equals sign. An entry like '=VLOOKUP(...) is deliberately stored as text; remove the apostrophe and re-enter the formula if you want Excel to calculate it. Another simple check is to confirm that the first character is =, not a space followed by an equals sign. A leading space can prevent Excel from recognizing the entry as a formula.

If formulas are entered but results do not update, check calculation settings. In Excel for Windows, choose Formulas > Calculation Options > Automatic. Manual calculation can leave results unchanged until calculation is triggered, although it does not normally make Excel display the formula text in place of the answer.

How VLOOKUP is supposed to work

VLOOKUP searches for a value in the first column of a selected table range, then returns a value from another column on the same row. Its arguments specify what to search for, where to search, which column to return, and whether to use an exact or approximate match. The lookup column must be the leftmost column in the range.

For example, with product codes in A2:A4, prices in B2:B4, and the code P-104 in E2, enter this formula in F2:

With product codes in A2:A4, prices in B2:B4, and the code to find in E2, the formula in F2 is =VLOOKUP(E2,$A$2:$B$4,2,FALSE). If P-104 is in A3 and its price in B3 is 18.95, the result is 18.95.

Here, E2 is the lookup value, A2:B4 is the table, 2 tells Excel to return the second column of that table, and FALSE requests an exact match. The dollar signs keep the table range fixed if the formula is copied to other rows. They do not change the lookup result.

Diagnose VLOOKUP errors

If the formula calculates but does not return the expected answer, first read the displayed error or result. These checks address common causes:

#N/A: An exact match was not found. Confirm that the lookup value exists in the first column of the table range, and check for extra spaces or differences in spelling.
#REF!: The requested column number is larger than the number of columns in the selected range, or a referenced range was deleted. Count columns from the left edge of the table range, not from column A of the worksheet.
Wrong result: Check whether the last argument is FALSE. If it is omitted, VLOOKUP uses approximate matching; the first column generally needs to be sorted in ascending order for that mode to return a reliable match.
Unexpected mismatch: The lookup value and lookup column may use different data types. For example, a number stored as text may not match a numeric value, even when both appear identical on screen.
Wrong return column: The column index counts from the first column of the table range. If the range starts in D, then D is column 1 for this formula, not worksheet column 4.

Exact-match lookup is usually the safer choice for codes, names, and IDs. In the example, FALSE prevents Excel from returning a nearby product’s price just because the code is not an exact match. Approximate matching is useful for tasks such as assigning a rate based on a threshold, but it depends on correctly sorted lookup data.

Check the lookup data and range

Visually identical values can differ underneath. One product code may contain a trailing space, or one ID may be stored as text while another is numeric. Compare the source values carefully, and check whether the lookup table’s first column contains the exact value being sought. If leading zeroes matter in codes such as 0042, keep those codes consistently stored and formatted as text; Excel can otherwise interpret them as the number 42.

Make sure the table range includes both the lookup column and the column you want returned. If you copy a formula down, an unanchored range can shift—for example, a range that starts at A2 may become A3 in the next row. The dollar signs in $A$2:$B$4 lock both ends of the example range. For a growing table, an Excel Table can make references easier to manage, but the lookup column still needs to be first within the range used by VLOOKUP.

Duplicate lookup values are another source of surprises. VLOOKUP returns the first matching row it encounters from the top of the selected range. If a code appears more than once, check the table for duplicates and decide which record should be returned rather than assuming Excel will choose the newest or most relevant one.

Choose a reliable formula pattern

For a one-off lookup, use an exact match and verify the table and return-column number before copying the formula. For a large worksheet, test a few known values first: one near the top of the table, one near the bottom, and one value that should not exist. That helps distinguish a display issue from a problem in the lookup logic.

Some people wrap lookups in an error-handling function to show a friendlier message when there is no match. That can make a report easier to read, but it can also conceal an incorrect range or a misspelled code. During troubleshooting, inspect the underlying VLOOKUP result before masking errors. Do not treat a blank or custom message as proof that the lookup table is correct.

Alternatives when VLOOKUP is limiting

VLOOKUP cannot return a value from a column to the left of its lookup column, because the lookup column must be first in its table range. If your worksheet layout requires a leftward lookup, or you want a formula less dependent on a numbered return column, consider XLOOKUP or INDEX with MATCH.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2016 or Excel 2019. It can search in either direction and lets you specify what to return when there is no match. INDEX and MATCH can also support flexible lookup arrangements and are available in older Excel versions. Changing functions will not fix Show Formulas mode or a cell formatted as Text, so resolve those display and entry issues first.

Frequently Asked Questions

Why does my VLOOKUP formula show instead of the answer?
Check whether Show Formulas is enabled. If only one cell is affected, change its format from Text to General and re-enter the formula; also remove any apostrophe or leading space before the equals sign.

How do I turn off Show Formulas in Excel?
On Windows, press Ctrl+`, or choose Formulas > Formula Auditing > Show Formulas. On a Mac or in Excel for the web, use the Formulas tab command if the shortcut differs or is unavailable.

Why does VLOOKUP return #N/A?
With an exact match, #N/A means Excel did not find the lookup value in the first column of the selected range. Check for spaces, spelling differences, mismatched text and number types, and an incorrect range.

Why does VLOOKUP return the wrong row?
Confirm that the final argument is FALSE for an exact match, that the return-column number is counted from the range’s first column, and that the lookup value is unique. With duplicate values, VLOOKUP returns the first match.

Does VLOOKUP work if the return column is to the left?
No. VLOOKUP searches the first column of its table range and returns values from columns to its right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or consider INDEX with MATCH for a leftward lookup.

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.