VLookup does not calculate, nor throw an error - just displays formula

Hello,
I am working in Excel 2010 on a 97-03 workbook which was sent to me after being exported from SalesForce.  I added a column in the original sheet to look up values I entered in a second sheet (added by me).

My formula appears to be correct when checking it with the function builder tool.  

=VLOOKUP(A2,'sheet 2'!A2:B150,2,FALSE)

I checked that the "show formulas" command was not on by toggling it on and off.  No matter what I do, I can't see to make the formula actually calculate.  

Both the "search for" values and the 1st column of the array to search are formatted the same - as text.  The 1st column of the array to search is sorted A-Z.

What would make the formula simply display in the cell rather than calculate?  I saved the workbook as a .xlsx and no difference.

Thank you!
txfanAsked:
Who is Participating?
 
redmondbCommented:
txfan,

Change the formula's cell format from Text to General. It will still display - you need the cell's contents to be rewritten (simply add a space to the end of it).

If there are lots of cells like then then do a Find and Replace with both values being the equal sign.

Regards,
Brian.
0
 
etech0Commented:
Try pressing control and ` (it's on the top left corner of the keyboard, under esc)
0
 
txfanAuthor Commented:
etech0,

That toggles the "Show Formulas" command.  I had tried it previously via the ribbon, but following your suggestion I tried via the keyboard shortcut you suggested.  When I activate the "Show Formulas" command, it shows the whole formula on one line.  When I turn the show formulas command off, it wraps as two lines, given the cell width.  But the same text/formula is visible.  Not the value retrieved by the formula.

Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kgerbChief EngineerCommented:
Check to make sure your cell is not formatted as text.  This will make the formulas show instead of the values.
0
 
etech0Commented:
When editing the formula, check if there is a single quote before the = sign.
Alternatively, try retyping the formula.
You can also try typing a different formula (eg: =1+2) to see if it a specific problem or a general one.
0
 
txfanAuthor Commented:
Wow, I am making a large mental note right now to check that next time I run into this problem... can't believe I checked everything but that.  Thanks so much!
0
 
txfanAuthor Commented:
The formula being formatted as text was the problem.  I changed formatting to "general", clicked in the cell, hit the ENTER key, and all was well!  Thanks!
0
 
redmondbCommented:
Thanks, txfan!

...can't believe I checked everything but that...
I strongly suspect that most of us here have been bitten by this.

Regards,
Brian.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.