VLookup Won't Work unless Pressing Enter in Cell

[bdunk]

I am trying to setup a simple VLookup between two sheets. The VLookup code is as follows:

=VLOOKUP(A2,TopDown!$A$5:$E$62,5,FALSE)

After writing the formula and copying it down the column I get the #N/A error in all my cells but one. This cell's content is "V17811". This is the only cell in my table array that starts with an Alpha character. All cells are set to 'Text'. To get any other cell to work correctly I need to select the cell in my Table Array. In the Formula Bar above I place my cursor at the end of the number and press the Enter key. This then puts the Green corner in the upper left hand corner of the cell and shows the Exclamation point notification that something is not right.

Why does this make it work?
What is happening to the cell when I press enter that makes it work?

Any help is much appreciated.

Brian

 

[Aladin Akyurek]

Well, all that seems ok!...

 

[Jukebox]

I had the same problem. It disappeared every time I clicked on the save button. Hope it helps for you as well

 

[Victor96]

Hi there!


Stumbled upon this thread as I struggled with the issue myself..

After a bunch of testing, I found that inserting a "text" formula works.

For example:
=VLOOKUP(TEXT(A2;0);Sheet1$A$5:$E$62;1;FALSE)

Hopefully it can save someone time even though the thread is old.

Regards,
Victor