VLOOKUP stops working with pasted values

[SueD]

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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"SueD" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[SueD]

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

"Arvi Laanemets" wrote:

> 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)
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
>
> "SueD" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

[Max]

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
---

[SueD]

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
> ---

[Max]

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

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

[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.