Problems with Vlookup

[Patti345]

I have a vlookup function

=TRIM(VLOOKUP($H$8,cc!$A$2:$E$498,3,FALSE))

It works fine when I type in the data manually in the H8 cell (
number). H8 has "general" format, same as the range on the cc sheet.

However, when I paste the data from another workhsheet into H8 cell
even if I only paste values, the formula does not work and I receive
"#N/A" error. I can fix it manually by typing in an apostrophe befor
the number.

In the past I also had similar errors when the numberic cells wer
identically formatted, yet Vlookup did not work until I either typed i
an apostrophe or an equal sign in the cell before the number of a looku
range.

Could you please help me and let me know how to avoid typing in tha
annoying apostrophe in each cell for Vlookup to work properly? Than
you very much.

Patti
Irvine, C

 

[Frank Kabel]

Hi
try
=TRIM(VLOOKUP(--$H$8,cc!$A$2:$E$498,3,FALSE))

If H8 always contains a numeric value

 

[RagDyeR]

You have conflicting descriptions!

<<"I can fix it manually by typing in an apostrophe before the number.">>

That simply means that you're *changing* the number to "text".
What that *also* means, is that your lookup column in cc!A2:A498 is *also*
text, even though you state that the format is "general".


<<"It works fine when I type in the data manually in the H8 cell (a
number)">>
This would seem to indicate that you "need", and the lookup column "is", a
true number?!?!?!

If your formula *always* works by prefixing H8 with an apostrophe, I would
suggest making sure that A2:A498 are "true" numbers.

You could try this:

Right click in a *new*, *unused* cell, and choose "Copy".
Select A2:A20.
Right click in the selection, and choose "PasteSpecial",
Click "Add", then <OK>.

Now, in H8, enter or paste data from your other worksheet, where you *know*
the values will be contained somewhere between A2:A20 (your revised data),
and see if this works without having to add the apostrophe.

If it does, then convert the rest of your data in column A.
--

HTH,

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


I have a vlookup function

=TRIM(VLOOKUP($H$8,cc!$A$2:$E$498,3,FALSE))

It works fine when I type in the data manually in the H8 cell (a
number). H8 has "general" format, same as the range on the cc sheet.

However, when I paste the data from another workhsheet into H8 cell,
even if I only paste values, the formula does not work and I receive a
"#N/A" error. I can fix it manually by typing in an apostrophe before
the number.

In the past I also had similar errors when the numberic cells were
identically formatted, yet Vlookup did not work until I either typed in
an apostrophe or an equal sign in the cell before the number of a lookup
range.

Could you please help me and let me know how to avoid typing in that
annoying apostrophe in each cell for Vlookup to work properly? Thank
you very much.

Patti
Irvine, CA

 

[Alan]

as Frank says try
=TRIM(VLOOKUP(--$H$8,cc!$A$2:$E$498,3,FALSE))
If this data is coming from the net you may have the ASCII Code 160 which is
a space, but not a space tha Excel will recognise, Excel regards a space as
ASCII32, or CHAR(32) in an a formula.
Try highlighting the entire sheet, Edit > Replace > Replace What > Hold down
the Ctrl key and type 0160 > Nothing will show in the dialogue box, hit
Replace All
Regards,