Microsoft Excel - Vlookup works only when i double click the looked up cell

Asked By simplify _ on 22-Oct-09 11:12 AM

Hi guys,

This is a tricky one...

I have a sheet with zip code numbers - these have been converted to text and Vlook up was used as next step. Formulae works great for 2 months of data, no issues.

I have started 3rd month sheet of data, and the vlook up doesnt work anymore- N/A error with the exclamation saying number stored as text. I have formatted all cells as Text, I have no idea what is causing this, out of 4000 zip codes I have 1000 that are with ! and N/A.

But here is the fun part - As soon as I double click on that zipcode cell, the vlook up works?

I have:

1, formatted all cells as text

2, Set calculation to Automatic

What else is there am missing?


Jonathan VH replied to simplify _ on 22-Oct-09 12:16 PM

That's not unusual. You can get that by entering numbers into a range that has the default (General) cell format and then changing the cell format to Text. The format is Text but the values are still numbers until you double click them. If you have the Numbers as Text warning enabled, you won't see the warning before you double click. The best way to avoid this is to format the cells as Text before the values are entered.

The easiest way to get the values recognized as Text is to select the range (this can be the entire column) and use Data|Text to Columns from the menu. Just click Next on the first two dialog boxes and then choose Text as the Column data format on the third and then Finish.

That seems to be a typical column in Formatting of the column used in Vlookup function. Or,

[)ia6l0 iii replied to simplify _ on 22-Oct-09 08:54 PM
You could even select the column (that is causing the problem), right-click on the column and format it as Text. 

and one more trick is to do a math operation on this column and force the result to be a number.

[)ia6l0 iii replied to simplify _ on 22-Oct-09 09:03 PM
You can do this by copying a blank cell or any cell with actual numbers, and then select the "problematic" column data range and then choose PasteSpecial - [Paste - Values] - [Operation - Add] - OK

That should fix it as well.
Gary Byrne replied to simplify _ on 22-Oct-09 10:56 PM

One sure-fire way to make sure that vlookups work is to format the two data sets exactly the same... say your zip codes are in column A, you can enter this formula in a nearby cell:


Then select the whole range of those formulas, copy and paste special values over them.  Do this to both the original data range and the range that you are looking up from.

simplify _ replied to Jonathan VH on 23-Oct-09 07:10 AM
Jonathan, :D worked perfectly, thanks a ton :D
Jonathan VH replied to simplify _ on 23-Oct-09 07:38 AM
Just be careful if you had zip codes from New England, as they will have lost their leading zeros when the cells were numeric.
Rana Connolly replied to Jonathan VH on 21-Oct-11 10:36 AM
Thanks so much for this. You just saved me hours!