How to Fix VLOOKUP Showing Formula Instead of Result in Excel?

Sharing is caring!

Are you trying to use the VLOOKUP function in Microsoft Excel, but instead of getting the result you expect, you see the VLOOKUP formula itself in the cell? This is a common issue that can be frustrating, but don’t worry – there are a few simple solutions to fix VLOOKUP showing formula instead of result. In this article, we’ll walk through the causes of this problem and provide step-by-step solutions to get your VLOOKUP formulas working correctly.

Reasons VLOOKUP Shows Formula Instead of Result

There are a few potential causes for VLOOKUP displaying the formula text instead of calculating a result:

1. Formula Entered as Text

The most common reason is that the VLOOKUP formula was accidentally entered as text instead of as an actual formula. This happens if you type an equals sign (=) in a cell but then click away or press Enter without actually entering a formula. Excel will interpret this as you wanting to literally type “=VLOOKUP(…)” as text in the cell.

2. Incorrect Reference Syntax

Another potential cause is using the wrong syntax or cell references in your VLOOKUP formula. If any part of the formula contains an invalid reference, Excel may display the formula text because it doesn’t know how to calculate a result.

3. Numbers Stored as Text

Sometimes the issue isn’t with the VLOOKUP formula itself, but with the source data being looked up. If the value you are looking up or the values in your lookup table are numbers that were entered as text (e.g. by preceding the number with an apostrophe), VLOOKUP won’t be able to match them properly. Numbers stored as text may look fine but are treated differently by formulas.

4. Formula Incompatible with Other Cell Contents

Finally, mixing formulas and text together in a single cell can cause strange results. If you combine a working VLOOKUP formula with other text (besides a simple label), Excel may get confused and just display the whole mishmash as text.

How to Fix VLOOKUP Showing Formula Instead of Result

Now that we know the common causes, let’s look at how to resolve each issue and get VLOOKUP working properly:

Solution 1: Re-Enter the Formula Correctly

If your VLOOKUP is showing as text because it was accidentally entered that way, the solution is simple: delete the equals sign and formula text, then re-type it from scratch as a real formula. Make sure to hit Enter after typing the closing parenthesis to complete the formula.

Here’s how:

1. Click on the cell containing the VLOOKUP text
2. Press F2 to enter edit mode
3. Delete the “=VLOOKUP(…)” text
4. Type = and enter your VLOOKUP formula correctly
5. Press Enter to calculate the formula

Solution 2: Fix Formula Syntax and References

Carefully proofread your VLOOKUP formula for typos, incorrect punctuation, and invalid cell references. Make sure the lookup_value matches the lookup column, the table_array covers the correct data range, and col_index_num points to the right column.

Here are a few things to check:

• Lookup_value is a single cell reference or value, not a range
• Table_array is an absolute reference (with $ before column and row) and includes the lookup column
• Col_index_num is a valid integer
• Parentheses are placed correctly and match
• Function is spelled “VLOOKUP” correctly

For example, this VLOOKUP contains an error:

=VLOOKUP(A2, B2:D10 3, FALSE)

The comma is missing between the table_array and col_index_num. The correct formula would be:

=VLOOKUP(A2, B2:D10, 3, FALSE)

Solution 3: Convert Numbers Stored as Text to Real Numbers

If your VLOOKUP can’t match because the lookup_value or table_array contains numbers stored as text, you need to convert those text values to actual numbers. There are a few ways to do this:

• Highlight the cells, click in the formula bar, and press Enter
• Use Paste Special > Values to overwrite text with numeric values
• Use a formula like =VALUE(A1) to convert text to number

For example, if you have numbers stored as text in A1:A10, you can convert them with an array formula. Select A1:A10, type this formula, and press Ctrl+Shift+Enter to array enter it:

=VALUE(A1:A10)

The VALUE function will convert any text that looks like a number into a real number. #VALUE errors mean the text couldn’t be converted.

Solution 4: Separate Formulas from Other Text

If you want to label or annotate a cell that contains a VLOOKUP, put the label in a separate cell rather than combining it with the formula. Mixing formulas and text in one cell rarely works well.

For instance, instead of this:

Product XYZ Quantity: =VLOOKUP(A1, InventoryTable,2,FALSE)

Split it into two cells like:

Product XYZ Quantity:
=VLOOKUP(A1, InventoryTable,2,FALSE)

Comparing VLOOKUP to Other Lookup Methods

While VLOOKUP is popular, it’s not always the best function for looking up data in Excel. Some other useful functions to consider are:

Each of these functions works a bit differently than VLOOKUP, so if you run into frequent issues with VLOOKUP it’s worth learning one of these alternatives. However, they can also display formulas instead of results if entered incorrectly.

Troubleshooting Other VLOOKUP Issues

Besides showing formulas instead of results, there are a few other common problems you may encounter with VLOOKUP:

#N/A Errors

A #N/A error means VLOOKUP couldn’t find the lookup_value in the first column of the table_array. This usually happens if the lookup_value is misspelled, has extra spaces, or is a different data type (e.g. number vs. text) than the values in the lookup column.

#REF! Errors

A #REF! error occurs if the col_index_num exceeds the number of columns in the table_array range. Double-check that your table_array includes enough columns for the col_index_num you specified.

Incorrect Results

If VLOOKUP returns a value but it’s not the result you expected, the most likely cause is using approximate match (range_lookup = TRUE or omitted) instead of exact match (range_lookup = FALSE). Approximate match will return the next smallest value if no exact match is found, which can cause confusion. Use FALSE if you only want exact matches.

Summary

VLOOKUP is an essential tool for Excel users to master, but it can sometimes misbehave and show formulas instead of calculated results. The key issues to watch out for are:

• Accidentally entering formulas as text
• Syntax errors and incorrect cell references
• Numbers stored as text instead of actual numeric values
• Incompatibility between formulas and other text in a cell

By following the troubleshooting steps in this guide, you should be able to resolve cases of VLOOKUP showing formula instead of result. If you still have trouble, consider an alternative lookup function like INDEX/MATCH, XLOOKUP or HLOOKUP. With practice, you’ll be able to use the VLOOKUP and other lookup functions efficiently to analyze your data in Excel.

People Also Ask

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.