VLOOKUP displays the formula instead of the result

[shelley]

My VLOOKUP formula is displaying in the cell instead of the result. The formula is VLOOKUP(A1, income_codes, 2, FALSE) and in the formula editor the result (00017) is calculated correctly.
However the cell displays =VLOOKUP(A1, income_codes, 2, FALSE) instead of the answer 00017.
My view options are not set to display formulas. The codes are formatted as text in the range income_codes as they start with zeros.

Any ideas?

 

[shelley]

Yes I did invoke edit mode because that's something the end user will probably be doing.

I think Brian is right, I'll have to look at doing this another way.

thanks everyone

 

[pattrain]

SOLUTION:
Have to make sure the cell that has the formula and the cell from the lookup table is formatted as GENERAL. If one or both of the cells are formatted as Text, then the formula itself will display in the cell. Also, after you change the format for the cell that holds the formula and the cell from the lookup table, then go back to the cell that holds the formula delete the = sign and type the = sign again. This should solve the problem.

powtrain

 

[Alex Rose]

I very much doubt the problem is that the user has the columns formatted as text, most would already check that. The most common cause of this problem is that the user at some point has turned on formula auditing. Formula auditing mode in Excel always displays the cell formula in each cell, even when you can see the result of the formula in the formula check box.

This is a very easy fix. Open the spreadsheet with the vlookup function that is only displaying formulas. Do the previous fixes mentioned in this discussion (switch off formula display under options, format all data columns used in the vlookup as General and not text), then, do this :

Menu - Tools > Formula Auditing

the second list item in the sub menu displayed should be Formula Auditing Mode. If the icon to the left is orange it means its active, click on this sub menu item to deactivate. All formulas should now correctly display results.

 

[morris13]

SOLUTION:
Have to make sure the cell that has the formula and the cell from the lookup table is formatted as GENERAL. If one or both of the cells are formatted as Text, then the formula itself will display in the cell. Also, after you change the format for the cell that holds the formula and the cell from the lookup table, then go back to the cell that holds the formula delete the = sign and type the = sign again. This should solve the problem.

powtrain

I had same problem and did the following to resolve:
1. Cell was Text so changed to General
2. Restarted Excel 2010
3. Deleted the "=" sign before VLOOKUP and pressed Enter key
4. Added "=" back before VLOOKUP and pressed Enter key
5. VLOOKUP worked correctly and provided value

 

[SZQVCU]

You'll need to reformat that cell back to General. It's formatted as text. I'm guessing that the Vlookup returns a text value and thus Excel formats it as text. Then the next time you go in a enter a formula, Excel thinks it's still a text value.

This worked perfectly for me. Thanks for the help!

 

[baha17]

Thank you Yogi for this answer.it worked very well for me