VLOOKUP stops working with pasted values
 

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.

VLOOKUP stops working with pasted values
 

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"SueD" wrote in message
...
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.

VLOOKUP stops working with pasted values
 

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

"Arvi Laanemets" wrote:

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"SueD" wrote in message
...
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.

VLOOKUP stops working with pasted values
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

VLOOKUP stops working with pasted values
 

Marvellous Thanks. Both these options work.

"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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

VLOOKUP stops working with pasted values
 

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.

VLOOKUP stops working with pasted values
 

"SueD" wrote:
Marvellous Thanks. Both these options work.

Glad it helped !
Thanks for posting back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---