txfan
asked on
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!
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!
etech0
Try pressing control and ` (it's on the top left corner of the keyboard, under esc)
ASKER
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.
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.
Check to make sure your cell is not formatted as text. This will make the formulas show instead of the values.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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!
ASKER
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!
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.
...can't believe I checked everything but that...
I strongly suspect that most of us here have been bitten by this.
Regards,
Brian.