vlookup lookup format issues

[graphguy]

function: vlookup

vlookup(a2,ref,2,false)

source table
A B
289 =vlookup(a2,ref,2,false)
354
....

lookup table(ref)
A B
289 Smith
354 Jones


Formats are General. I have tried TRIM, CLEAN, changing format t
number, no luck. But if I go into the source table and retype th
number, then the vlookup value in Col B appears

 

[Gord Dibben]

If the numbers are seen by Excel as text, changing the format will do the
trick.

Format a blank cell to General then copy it.

Select your range of numbers and Paste Special>Add>OK>Esc.

Gord Dibben Excel MVP

 

[Gord Dibben]

Typo alert!!!!!!!!!!!

Should read......Changing the format will NOT do the trick.

Follow instructions below that line.

Gord

 

[RagDyer]

If Gord's suggestion doesn't solve your problem, you could have "other"
invisible codes that are *not* removed with "Trim" and/or "Clean".

A common one is a "non-breaking space" [ CHAR(160) ].

To find out if that might be your trouble, you could try using "Edit &
Replace" on some of the "bad" numbers, and see if they become "good".

Select some cells that are *not* returning an expected answer.
Then,
<Edit> <Replace>
And in the "Find What" box, enter:
<Alt> 0160
Using the numbers on the Num keypad, *not* the ones under the function keys.
You will *not* see anything displayed in the box.
Then, just click "ReplaceAll".

Now, see if you get your expected return from those particular cells.

If not, post back for more ideas.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
Typo alert!!!!!!!!!!!

Should read......Changing the format will NOT do the trick.

Follow instructions below that line.

Gord