• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

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!
0
txfan
Asked:
txfan
  • 3
  • 2
  • 2
  • +1
1 Solution
 
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
 
kgerbChief EngineerCommented:
Check to make sure your cell is not formatted as text.  This will make the formulas show instead of the values.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now