Vlookup Help

A

alisyed

I'm using a simple vlookup. The values I want results for (on the very
left of my array) apparently do not have the same format as the ones I
am looking up.

If I want them to produce a result, I have to copy and paste as a value
in the array and only then will my vlookup will work.

It's not a formatting issue - because I've checked formats and they're
both General.

Is there a way to make them comparable? Any help would be greatly
appreciated.
 
B

Bryan Hessey

Is there a reason you would not simply Copy the whole column, and Paste
Special, Values back over itsself?

ie, is the array changeable? - and if so how is the data created?

--
 
S

systematic

I had a similar problem just today. The value I was looking up was
formatted as text - even though Excel indicated it was a number in the
Format>>Cells menu.

There is probably a simpler way to fix the problem...but I used a
working cell =VALUE(CellReference) and used that cell for the vlookup
instead...

Hope this helps. But yes....the common sense approach is copy/paste
values....wish I had thought of that :(

/systematic
 
A

alisyed

yeah, i cant just copy and paste as values since the data is not in the
same order...
 
G

Gord Dibben

Simply formatting the cells as Number or General is not sufficient if Excel
believes they are text.

Select an empty cell formatted as Number and Edit>Copy.

Select your range of data and Paste Special>Add>OK>Esc to coerce the data to
Numbers.


Gord Dibben MS Excel MVP


I'm using a simple vlookup. The values I want results for (on the very
left of my array) apparently do not have the same format as the ones I
am looking up.

If I want them to produce a result, I have to copy and paste as a value
in the array and only then will my vlookup will work.

It's not a formatting issue - because I've checked formats and they're
both General.

Is there a way to make them comparable? Any help would be greatly
appreciated.
Click to expand...

Gord Dibben MS Excel MVP
 
Top