vlookup lookup format issues

  • Thread starter Thread starter graphguy
  • Start date Start date
G

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
 
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
 
Typo alert!!!!!!!!!!!

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

Follow instructions below that line.

Gord
 
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
 
Back
Top