vLOOKUP returning #NA until I double click in the lookup value cell

[Riehl]

Example of formula:
=VLOOKUP(B1644,Sheet2!$A$2:$A$3147,1,FALSE)

The vlookup formula in cell A1644 is returning #NA.

However, when I double click on cell B1644 (the lookup value from the formula) the result is shown correctly (not #NA).

Does anyone know what would make this happen?

[Jonmo1]

B1644 is probably a "number stored as text"

Try copying a blank cell
Highlight B1644
right click - paste special - values - add

You can do the whole column at once too..

[Tony Valko]

Sounds like a data type mis-match and you tried to format the cell to fix it.

The format doesn't change until you edit the cell by double clicking on it then the data types match.

[Riehl]

That was it! Thanks!

[Jonmo1]

You're welcome.

[Dotreena]

Hi

I have this same problem and I've tried the above and it doesn't seem to make any difference?

I import the data for my Lookup table from another sheet, I have tried pasting it all as values but it makes no difference.

This is my formula:

=VLOOKUP(F12,'Customer Database'!A2:F5000,2,FALSE)

If F12 is a number (e..g 101000) it returns n/a. However if F12 begins with a letter (e.g. c123) it returns the correct information. Both the lookup table & the return document are set as general.

Any ideas?

Thanks

[JohnTopley]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.