VLOOKUP stops working with pasted values

[Guest]

I have the following formula in Excel 2000, which normally works fine:-

=VLOOKUP(E2,$U$2:$V$24,2,FALSE)

I have a long list of values in column E with a matching formula in column F
to return a category. Every month I get a new set of values in column E,
which I would like to copy and paste from an external source (previous
version of Excel spreadsheet). When I do a Paste or Paste Special Values, the
VLOOKUP formula returns #N/A. If I overtype the pasted value with the number
shown, the formula works again.

I have tried using INDEX and MATCH instead of VLOOKUP and I get the same
problem.

 

[Arvi Laanemets]

Hi

It looks like pasted values are treated as texts. Use PasteSpecial>Values
instead of paste. (And format the whole column E as General or Numeric
before)

 

[Guest]

Thanks, but I have already tried this and it makes no difference. I have
preformatted the column and I am using PasteSpecial>Values.

 

[Guest]

Try something like: =VLOOKUP(E2+0,$U$2:$V$24,2,FALSE)

The "+0" operation made to the pasted lookup values in col E
might suffice to coerce these to real numbers.
Or we could try multiply by 1, viz.: E2*1

---

 

[Guest]

Marvellous Thanks. Both these options work.

 

[Harlan Grove]

Max wrote...

Try something like: =VLOOKUP(E2+0,$U$2:$V$24,2,FALSE)

The "+0" operation made to the pasted lookup values in col E
might suffice to coerce these to real numbers.
Or we could try multiply by 1, viz.: E2*1

Or we could exponentiate by 1, E2^1, or FTSHOI, double unary minuses,
--E2, but don't forget unnecessary function calls, VALUE(E2).

The key points for the OP are whether COUNT(E2) returns 1 or 0, whether
COUNT(U2:U24) returns 23 or 0 or something in between, and that number
formatting has no affect whatsoever on value. Format a cell as Text,
and if you paste a number into that cell it'll still be a number, not
text.

 

[Guest]

Marvellous Thanks. Both these options work.

Glad it helped !
Thanks for posting back ..

---