Vlookup problem => value not found

[danteske]

G'morning all

Long time since I've had to ask a question. Glad to see you're all still here! BTW, I searched forums for an answer, but wasn't successful.

Background: I'm using vlookup in a pretty simple way, nothing fancy. Thing is, the lookup value is the problem. The lookup value was (probably) imported data from some kinda mainframe, which was (most likely) converted from .csv into excel, then emailed to me in excel.

When I plug in a vlookup, I get #N/A. When I double click the lookup value, then somehow the cell becomes "active" and the vlookup works.

Is there any way to make active a column or selection without having to individually double click the cells?

Dan

 

[Andrew Poulsom]

Try Data|Text to Columns and click Finish. You may need to format the cells as General afterwards so that the numbers are right aligned.

 

[Jonmo1]

this is a very common problem. Probably numbers stored as text...

try typing a 1 anywhere on your sheet.
copy it

highlight the offending column, paste special - values - multiply

 

[shippey121]

it seems that your values are stored as text even though they may look like they are not, when you double click the cell it converts it to a number, try typing a zero 0, in a blank cell and copy it, now highlight all your data where the Vlookup is checking, now select EDIT >> PASTE SPECIAL, under where it says OPERATION select ADD, now click ok, this will effectivley ADD ZERO to all your values forcing them to change into numbers

HTH

 

[danteske]

The first one worked perfectly!! Thanks guys, I love this site!!!