XLOOKUP Showing Formula Instead of Result?

If XLOOKUP displays the formula itself instead of a result, first turn off Show Formulas on the Formulas tab; if that does not fix it, the cell may be formatted as Text or the formula may begin with an apostrophe. These are display or entry issues in Excel, not usually a problem with XLOOKUP’s search.

What it means when a formula appears in a cell

A working XLOOKUP formula normally calculates and shows a returned value, such as a product name or price. When the cell shows something like =XLOOKUP(E2,B2:B4,A2:A4,"No match") literally, Excel is treating the expression as text or is set to display formulas instead of calculated results. Those two causes account for most cases.

This is different from an XLOOKUP that calculates but returns an unexpected value, #N/A, or another error. In those cases, Excel has interpreted the formula and attempted to evaluate it; the issue is more likely the lookup value, the lookup range, or the formula’s arguments. Start by fixing literal formula display, then investigate the lookup only if the result is still wrong.

Turn off Show Formulas

Show Formulas is a worksheet display setting that reveals formulas in cells in place of their results. It is useful when auditing a worksheet, but it can make every formula appear broken if enabled accidentally. It applies to the active worksheet, so check other sheets separately if they show the same behavior.

In Excel for Microsoft 365 on Windows, select the Formulas tab.
In the Formula Auditing group, select Show Formulas to switch the setting off.
Check whether the cell now displays the lookup result. Repeat on any other affected worksheet.

You can also press Ctrl+` on Windows to toggle Show Formulas. The grave accent is usually the key above Tab, though its location can vary by keyboard layout. On a Mac, try Control+`; if that shortcut is unavailable on your keyboard, use the Formulas tab instead. In Excel for the web, use the Show Formulas control on the Formulas tab when it is available in your ribbon.

When Show Formulas is enabled, columns may look too narrow because formulas contain more characters than their results. Widening a column can make the displayed formulas easier to read, but it does not turn formula display off. Use the ribbon control or shortcut to restore calculated values.

Check whether the cell is formatted as Text

If only one or a few cells show their formulas, check their number format. When a cell is formatted as Text, Excel can store what you type as literal text rather than evaluate it as a formula. Changing the format afterward does not necessarily convert an existing text entry automatically; you need to re-enter it.

Select the affected cell or range.
Choose Home > Number Format and select General.
Re-enter the formula. On Windows, select the cell, press F2, then press Enter. Alternatively, click in the formula bar and press Enter.

For example, with product names in A2:A4, matching product IDs in B2:B4, and the ID to find in E2, enter the formula in another cell after setting that cell to General: =XLOOKUP(E2,B2:B4,A2:A4,"No match"). If E2 contains 104 and B2:B4 contains 102, 104, and 108, the result is the name in A3. The formula looks in the ID range and returns the value from the corresponding position in the name range.

If the formula still appears literally after you change the format and re-enter it, inspect the start of the entry. A leading apostrophe can force text treatment. Remove it, then enter the formula again. Also check for a space or other character before the equals sign: a formula must begin with = as its first character to be recognized as a formula.

Look for pasted text and import issues

Formulas copied from a webpage, email, document, or text file can arrive with an apostrophe, a leading space, or formatting that marks the destination cell as Text. A cell may also contain a formula-looking string that was imported as text from a CSV file. In those situations, toggling Show Formulas will not help because Excel has no calculated formula to reveal.

Click the cell and inspect the formula bar. If the entry begins with an apostrophe or a space before the equals sign, remove the extra character and press Enter. If many cells were imported as text, select the affected range, change its format to General, and re-enter the formulas. For a large imported range, test the process on a copy or a small sample first; avoid replacing data across a whole sheet until you know which cells are affected.

Do not confuse an apostrophe used to force text with ordinary punctuation elsewhere in a formula. For example, the quoted text "No match" is a valid text value supplied as XLOOKUP’s optional not-found result. It does not make the entire formula text. The problem is an apostrophe or other character placed before the initial equals sign.

Confirm Excel recognizes XLOOKUP

XLOOKUP is available in Microsoft 365 and in Excel 2021 and later versions, including Excel 2024. It is not included in Excel 2019 or Excel 2016. If a workbook containing XLOOKUP is opened in an unsupported older version, Excel may show #NAME? rather than calculate the formula. That is a compatibility problem, not the same as a cell displaying the complete formula as text.

In a supported version, an XLOOKUP formula has a lookup value, a lookup array, and a return array; it can also include optional arguments such as a value to return when no match exists. The lookup and return arrays should be compatible in size. If XLOOKUP is recognized but returns an error or an unexpected result, check that the lookup value is present in the lookup range and that the return range points to the intended values.

For example, a lookup can fail to find two values that look identical if one is stored as a number and the other as text, or if a value contains an unnoticed space. Those conditions generally produce a calculated result such as #N/A or the specified not-found message, rather than showing the formula itself. Resolve display and text-entry problems first so you can see what XLOOKUP is actually returning.

Separate formula display from calculation problems

Manual calculation is another Excel setting people sometimes suspect, but it usually does not cause a formula to appear as text. With calculation set to Manual, Excel can show an old result because formulas have not recalculated; it still displays the result rather than the formula string when Show Formulas is off and the cell contains a valid formula.

If a cell shows an old value, check Formulas > Calculation Options and whether calculation is set to Automatic. You can request a recalculation with F9 on Windows. On a Mac, the shortcut can depend on the keyboard and function-key settings, so use the calculation controls in Excel if F9 changes a system setting instead. Recalculation is worth checking only after confirming that the cell contains a formula and Show Formulas is off.

Likewise, a formula that returns #N/A is not displaying its formula. It is an evaluated formula reporting that it did not find a match, unless the optional not-found argument supplies a different result. Read the cell’s actual displayed output before changing the lookup ranges; otherwise, it is easy to troubleshoot the wrong problem.

Prevent the problem when entering formulas

A few habits make literal formula display less likely, especially in workbooks that receive pasted or imported data:

Set destination cells to General before entering formulas, particularly if the cells previously held imported text.
Make sure the first character is an equals sign, with no apostrophe or space before it.
After pasting formulas, check a sample cell and confirm that it shows a result rather than the typed expression.
If formulas across a worksheet suddenly become visible, check Show Formulas before editing each cell.
Keep a copy of important workbook data before making bulk changes to formatting or imported ranges.

There is no need to rebuild a valid XLOOKUP merely because its characters are visible. First determine whether Excel is displaying formulas across the sheet or treating the affected entry as text. That distinction usually identifies the fix quickly and avoids changing a lookup that was already correct.

Frequently Asked Questions

Why is Excel showing all my formulas instead of answers?
Show Formulas is probably enabled for that worksheet. Turn it off from Formulas > Formula Auditing > Show Formulas, or use Ctrl+` on Windows. Check other worksheets separately because the setting can differ by sheet.

Why does only one XLOOKUP cell show the formula?
That cell may be formatted as Text, or its entry may have an apostrophe or space before the equals sign. Change the cell to General, remove any leading character, and re-enter the formula.

Will changing Text to General fix a formula without re-entering it?
Not always. Excel may keep an entry that was stored as text as text even after its format changes. Select the cell, press F2 and Enter on Windows, or edit the formula bar and press Enter, so Excel evaluates the entry.

Why does XLOOKUP show #NAME? instead of a result?
One possibility is that the workbook is open in Excel 2019 or Excel 2016, which do not include XLOOKUP. Use a version that supports the function, such as Microsoft 365, Excel 2021, or Excel 2024. If you are already in a supported version, check the formula spelling and syntax.

Why does XLOOKUP return #N/A when the formula is visible correctly?
Excel is evaluating the formula but has not found a match. Check that the lookup value and lookup range contain the same value and compatible data types, and look for extra spaces. A not-found argument can display a clearer message, but it does not correct a mismatched lookup 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.