Non Numeric String Lookup Issue

S

slf

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
 
T

Tom Hutchins

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
 
S

slf

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

slf

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top