Exact Formula in Excel Not Working? Fix It

If an exact formula in Excel is not working, first check that the cell is not formatted as Text, that the formula begins with an equals sign, and that calculation is set to Automatic. If you mean a lookup that must find an exact match, check the lookup formula’s match setting and whether the lookup values are genuinely the same.

What “exact formula” can mean

The phrase can describe two different problems. You may have typed a formula exactly as written but Excel displays it instead of calculating it, returns an unexpected result, or shows an error. Alternatively, you may want a lookup formula to match a value exactly rather than return an approximate match. The right fix depends on which problem you see.

Start by looking at the cell itself. If it shows something like =A1+B1, Excel may be treating the entry as text or may have Show Formulas turned on. If it displays a number that seems wrong, inspect the references, calculation settings, and underlying values. If it returns an error, the error message usually narrows down the cause.

Check the quickest causes first

Before changing a complicated formula, check these common issues in the affected cell:

Does the formula start with =, with no apostrophe or other character before it?
Is the cell formatted as Text?
Is the formula separated with the argument delimiter Excel expects on your computer?
Is calculation set to Automatic?
Are the referenced cells the ones you intended?
Is Show Formulas enabled, making formulas appear across the worksheet?

To see whether Show Formulas is on, go to Formulas > Show Formulas. When enabled, Excel displays formulas in cells instead of their results; switch it off to return to the usual view. On Windows, Ctrl+` is the keyboard shortcut. The key’s position and shortcut behavior can differ by keyboard layout and Mac configuration, so the ribbon command is a reliable alternative.

When Excel displays the formula instead of the result

A cell formatted as Text can make a correctly typed formula appear literally. Select the cell, go to Home > Number Format, and choose General. Changing the format does not always make an already-entered text string calculate, so edit the cell and confirm the entry again: press F2, then Enter on Windows. On a Mac, use the equivalent edit-and-confirm keys for your keyboard, or click in the formula bar and press Return.

Also look for an apostrophe before the equals sign. An entry such as '=A1+B1 is deliberately stored as text. Remove the apostrophe and confirm the entry if you want Excel to calculate it. If several cells have this issue, select them and change their format, but check the results after re-entering the formulas; changing a number format alone does not reliably convert text entries into formulas.

Another possibility is that Show Formulas is on. This setting changes the worksheet display, not the calculation itself. If every formula is visible, turn off Formulas > Show Formulas before editing cells individually. If only one cell displays formula text, text formatting or an apostrophe is more likely.

Check calculation mode and dependencies

Excel can be set to calculate formulas automatically or wait until you request a recalculation. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options > Automatic. If the workbook is set to Manual, a formula may remain unchanged after its inputs change. Set it to Automatic, then allow Excel to recalculate. Excel for Mac has calculation options on the Formulas tab as well; Excel for the web generally recalculates automatically.

Calculation mode can affect more than the current cell. If a result appears stale, confirm that the cells it depends on have updated too. On Windows, F9 recalculates open workbooks; Shift+F9 recalculates the active worksheet. Laptop function-key settings may require using Fn with the key. These shortcuts request calculation; they do not repair a formula that contains a wrong reference or invalid syntax.

For example, with numbers 10, 15, 20, and 5 in B2:B5, the following formula in B6 returns 50 when calculation is current:

With the values 10, 15, 20, and 5 in B2:B5, use: =SUM(B2:B5)

If B6 still shows an earlier result after a value changes, check calculation mode. If it returns an error, inspect the input cells and the formula rather than assuming recalculation is the only issue.

Review syntax, separators, and cell references

Excel formulas must follow valid syntax: function names, parentheses, operators, and arguments must be in the right order. In many English-language installations, commas separate function arguments. Some regional settings use semicolons instead, depending on the list separator configured for the system. If Excel rejects a formula copied from a website, its separators may not match your installation. Excel may also adjust separators when you enter or edit a formula.

Check that every opening parenthesis has a closing one and that the formula references the intended cells. A relative reference such as A2 changes when a formula is copied to another row; an absolute reference such as $A$2 stays fixed. A misplaced dollar sign can make a formula work in its first cell but fail when filled down. Click the formula cell and inspect the colored reference outlines, or select a reference in the formula bar to see which cells it points to.

For instance, with quantity in A2 and price in B2, the following formula in C2 multiplies those cells:

With quantity in A2 and price in B2, use: =A2*B2

If copied down, that formula changes to use A3 and B3 on the next row, which is usually intended. If the price is a fixed input in B1 instead, the reference should be locked before copying. Check the formula bar after filling a formula so you can catch shifted references.

For exact-match lookups, verify the match setting

If the issue is a lookup returning the wrong record, confirm that the formula requests an exact match. In Microsoft 365, XLOOKUP uses exact match by default when its match-mode argument is omitted. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older Excel releases. With product codes in A2:A4 and prices in B2:B4, searching for the code in D2 returns the matching price or the specified fallback value:

With product codes in A2:A4, their prices in B2:B4, and the code to find in D2, use: =XLOOKUP(D2,A2:A4,B2:B4,"Not found",0)

If D2 contains a code present in A2:A4, the result is the corresponding value from B2:B4; if it is absent, the formula returns Not found. The final 0 explicitly requests exact matching. XLOOKUP also searches from the last item only when you specify a search mode; do not confuse that setting with the exact-versus-approximate match setting.

With VLOOKUP, use FALSE as the fourth argument for an exact match. With product codes in A2:A4, prices in B2:B4, and the search code in D2, the formula returns the corresponding price when a match exists:

With product codes in A2:A4, prices in B2:B4, and the code to find in D2, use: =VLOOKUP(D2,A2:B4,2,FALSE)

VLOOKUP searches the first column of its table range and returns a value from a column to its right. If the lookup column is not the first column in the selected range, VLOOKUP may not be suitable. An exact-match setting also cannot make two different values equal: a number stored as text, an extra space, or a different character can prevent a match.

Values that look identical may not be equal

Two cells can look the same while containing different underlying data. A code may have a leading or trailing space, or one cell may contain the number 123 while another contains the text 123. Dates can also be stored as text rather than as Excel date values. Imported data is a common source of these inconsistencies. Check the formula bar and cell alignment, and test a small sample before applying a cleanup to a whole column.

For a direct comparison, with the two values in A2 and B2, use:

With the values to compare in A2 and B2, use: =A2=B2

This returns TRUE if Excel considers the values equal and FALSE otherwise. The comparison is not case-sensitive, and spaces still matter. To test whether text matches exactly including letter case, with the two text values in A2 and B2, use:

With the text values to compare in A2 and B2, use: =EXACT(A2,B2)

EXACT is case-sensitive. It does not automatically remove spaces or convert numbers stored as text; those differences need to be handled deliberately. Avoid cleaning an entire source column before checking whether spaces, leading zeros, or text formatting are meaningful parts of the data.

Use the error message to narrow the diagnosis

An error is useful evidence, not just a failure. Check for the following patterns:

#NAME? often means Excel does not recognize a function or name. Check spelling and whether the function exists in your Excel version.
#VALUE! can indicate an unexpected type of value or an argument the function cannot use.
#REF! means a reference is invalid, often because a referenced row, column, or sheet was deleted.
#DIV/0! indicates division by zero or by an empty cell used as the divisor.
#N/A commonly appears when a lookup cannot find a match, though some formulas can produce it for other reasons.

For errors tied to newer functions, check compatibility. XLOOKUP, XMATCH, FILTER, SORT, UNIQUE, SEQUENCE, and LET require Excel 2021 or Microsoft 365. LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. A workbook opened in an older version may show an unsupported-function error even if the formula is valid in a newer version.

A practical troubleshooting order

Work from simple checks to more involved ones, and change one thing at a time. This makes it easier to identify the actual cause instead of masking it with a workaround.

Read the cell: is it showing formula text, a stale value, or an error?
Check Show Formulas and the cell’s number format.
Confirm the leading equals sign, function spelling, parentheses, and argument separators.
Verify calculation mode and recalculate if needed.
Inspect each referenced cell for wrong ranges, shifted references, text values, spaces, or inconsistent data types.
If it is a lookup, specify exact matching and test whether the search value actually exists in the lookup column.
Check whether the function is supported by the Excel version opening the file.

If a formula works in one row but not another, compare the formulas side by side. Differences in references, blank cells, text values, or copied formatting often reveal the cause. For a workbook received from someone else, keep an untouched copy before making broad changes to data or formulas.

Frequently Asked Questions

Why does Excel show my formula instead of the answer?

Check whether Show Formulas is enabled under Formulas > Show Formulas. If only one cell is affected, change its number format from Text to General, then edit and confirm the formula again. Also remove any apostrophe before the equals sign.

Why is my Excel formula not updating?

Check Formulas > Calculation Options > Automatic. In Manual mode, results can remain unchanged until Excel recalculates. If the value still does not update, verify that the formula references the input cell you changed.

How do I make VLOOKUP return an exact match?

Use FALSE as VLOOKUP’s fourth argument. Also confirm that the search value is in the first column of the table range and that it is the same kind of value as the entries being searched.

Why does an exact lookup return #N/A when the value appears to exist?

The values may differ because of spaces, text-versus-number storage, leading zeros, or a different character. Compare the underlying values, not just how they look, and check that the lookup range includes the expected item.

Why does Excel reject a formula copied from the internet?

Check function availability and argument separators. Your Excel version may not support a newer function, or your regional settings may require semicolons instead of commas. Entering the formula through Excel’s formula bar can help identify where the syntax is rejected.

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.