------ Register to get rid of these "In Post" ads! ------


i am unable to use vlookup in numeric numbers stored as text - (2500 rows of
data)

=vlookup(a5,eg:gh,2,0)

col 1 col 2 col 3
1000372786741026 1000372786741026 $10

what formula is needed to lookup the number in column 2 and return the
information in col 3

------ Register to get rid of these "In Post" ads! ------


In your example, column 2 has to be EG and column 3 must be EH. The value to
be matched must be in the leftmost column of the lookup range. When I set it
up this way, your example works for me. If that's not the problem, check for
trailing spaces in one of the text numbers.

Hope this helps,

Hutch

"slf" wrote:

> i am unable to use vlookup in numeric numbers stored as text - (2500 rows of
> data)
>
> =vlookup(a5,eg:gh,2,0)
>
> col 1 col 2 col 3
> 1000372786741026 1000372786741026 $10
>
> what formula is needed to lookup the number in column 2 and return the
> information in col 3

------ Register to get rid of these "In Post" ads! ------


Tom,
Thank you for your response- i left out some of the information tho.

That lookup will work if i type in those numbers. However, the numbers are
received in database that stores the numbers as "General" - if i try to
convert to #'s it converts the last number to "0". Rendering the information
useless.

If i change the formatting to "text" it still doesn't recognize.

I thought maybe a formula using "Match", or "Sum Product" but not sure about
them.

Any other thoughts?
Thanks,




"Tom Hutchins" wrote:

> In your example, column 2 has to be EG and column 3 must be EH. The value to
> be matched must be in the leftmost column of the lookup range. When I set it
> up this way, your example works for me. If that's not the problem, check for
> trailing spaces in one of the text numbers.
>
> Hope this helps,
>
> Hutch
>
> "slf" wrote:
>
> > i am unable to use vlookup in numeric numbers stored as text - (2500 rows of
> > data)
> >
> > =vlookup(a5,eg:gh,2,0)
> >
> > col 1 col 2 col 3
> > 1000372786741026 1000372786741026 $10
> >
> > what formula is needed to lookup the number in column 2 and return the
> > information in col 3

------ Register to get rid of these "In Post" ads! ------


Tom,
it was the "trailing space" - i hadn't heard of that before. Fixed the
problem with a =trim
thank you so much.


"slf" wrote:

> Tom,
> Thank you for your response- i left out some of the information tho.
>
> That lookup will work if i type in those numbers. However, the numbers are
> received in database that stores the numbers as "General" - if i try to
> convert to #'s it converts the last number to "0". Rendering the information
> useless.
>
> If i change the formatting to "text" it still doesn't recognize.
>
> I thought maybe a formula using "Match", or "Sum Product" but not sure about
> them.
>
> Any other thoughts?
> Thanks,
>
>
>
>
> "Tom Hutchins" wrote:
>
> > In your example, column 2 has to be EG and column 3 must be EH. The value to
> > be matched must be in the leftmost column of the lookup range. When I set it
> > up this way, your example works for me. If that's not the problem, check for
> > trailing spaces in one of the text numbers.
> >
> > Hope this helps,
> >
> > Hutch
> >
> > "slf" wrote:
> >
> > > i am unable to use vlookup in numeric numbers stored as text - (2500 rows of
> > > data)
> > >
> > > =vlookup(a5,eg:gh,2,0)
> > >
> > > col 1 col 2 col 3
> > > 1000372786741026 1000372786741026 $10
> > >
> > > what formula is needed to lookup the number in column 2 and return the
> > > information in col 3