Excel Vlookup not working (sort of)

[piggeh]

I have an excel spreadsheet with a reference number in one column (on say, Sheet X). In another sheet (sheet Y for reference sake) I have the same reference numbers but also additional info such as short text, etc. When I go to do a vlookup to import in this short text into sheet X, excel tells me 'N/A' for every line. That is, until I double click into the reference number on sheet X and then simply press enter, at which point it right aligns the value within the column and decides that it can now find this value in the other sheet, despite the value not actually changing. :|

Anyone know why this happens and if there's an easy fix? Have a couple of hundred of lines to otherwise 'double-click and then enter on' just to make it recognise the text properly. Also happens quite a few times it seems, not the first time it's happened and on different machines.

Excel 2000 (9.0.3821) fwiw although also happened on excel 97.

[Salazaar]

I'm not sure why this is happening but there are a couple of checks you could try...

Is the list in sheet Y ordered? It should really be set up so that it's in order of reference number.

Are you sure that both sets of reference numbers are formatted the same, i.e they're definately formatted as numbers not as text or something?

[piggeh]

Sheet Y is in ascending order by reference number, both are formatted as general (changing to format as text or number makes no difference).

[piggeh]

Also I should add the values on sheet X originally came froma pivot table, which was then CnP'd with 'paste (special..)> value' only.

[Salazaar]

And you've got the range lookup operator set to 'False'?

[piggeh]

yep

[piggeh]

couldnt work out how to solve This time I just went through each line individually so that reference worked. However this happens occassionally so if anyone knows a solution it would eb appreciated!

[evilpuppy56]

I think I was having the same problem - go to Excel Options -> Formulas -> Workbook Calculation and make sure it is set to AUTOMATIC.

Hope that helps.

[Phage]

Also - go to both the reference and the variable column and in each case highlight the column, then go to the 'Data' menu and select 'text to columns'.
Had this prob myself many times.

[barry2811]

First thing I do with any VLOOKUP issues is to TRIM() and in the case of numeric references VALUE().