Does anyone know why a vlookup formula would not calculate until I retype the
lookup value in it's cell? The formatting in the lookup value cell is
exactily the same as in the table array, but until I manually retype the
lookup value cell, the formula just gives me the old #NA.

Help.
Don

Hi Don,

My standard reply
================================================== ===============
Your Numbers don't behave (like numbers)
Niek Otten, May 11, 2006

Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc.
In short:

Your Numbers look like Numbers, but they really are Text.
Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text!

Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's
ISNUMBER() function to check your cells; maybe you solved your problem in the first step!

· Format an empty cell as Number. Enter the number 1 in it. EditCopy.
Select your "numbers". EditPaste Special, check Multiply. Hopefully your cells are "real" Numbers now
· If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number
of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM()
function to remove them
· If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function
to remove most of them
· If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use
David McRitchie's TRIMALL() function to remove them. It can be downloaded he
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Please mail me any comments, additions or corrections:

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


================================================== ===============

"Don" wrote in message ...
| Does anyone know why a vlookup formula would not calculate until I retype the
| lookup value in it's cell? The formatting in the lookup value cell is
| exactily the same as in the table array, but until I manually retype the
| lookup value cell, the formula just gives me the old #NA.
|
| Help.
| Don

I am having a similar (?) problem: when I change anything in a Vlookup
function/formula then it stops working and then the whole function appears in
the cell as text. I have had some success by deleting the "=IF(" , then
clicking out of the cell then going back to it and re-typing the "=IF(". I
haven't been able to find anything else about this in this discussion group.
Please let me know if you do )
--
Capnmike


"Don" wrote:

Does anyone know why a vlookup formula would not calculate until I retype the
lookup value in it's cell? The formatting in the lookup value cell is
exactily the same as in the table array, but until I manually retype the
lookup value cell, the formula just gives me the old #NA.

Help.
Don

Hi

It sounds as though the cells may have been pre-formatted as Text.
Mark the range of cellsFormatCellsNumberGeneral

Then try entering your formulae.

--

Regards
Roger Govier

"capnmike" wrote in message
...
I am having a similar (?) problem: when I change anything in a Vlookup
function/formula then it stops working and then the whole function appears
in
the cell as text. I have had some success by deleting the "=IF(" , then
clicking out of the cell then going back to it and re-typing the "=IF(".
I
haven't been able to find anything else about this in this discussion
group.
Please let me know if you do )
--
Capnmike


"Don" wrote:

Does anyone know why a vlookup formula would not calculate until I retype
the
lookup value in it's cell? The formatting in the lookup value cell is
exactily the same as in the table array, but until I manually retype the
lookup value cell, the formula just gives me the old #NA.

Help.
Don