vlookup -- converting numbers to text

  • Thread starter Janet Panighetti
  • Start date
J

Janet Panighetti

I have a report (worksheet) that stores my "item numbers" that look like
numbers as numbers.

I have another report (worksheet) that stores all item numbers as text.

I am trying to do a vlookup from the first report to the second report but I
am having problems converting the number to text.

For example, if the item number is 1202.01, I am trying to do the following:

vlookup(text(B2,"0.00"),Lookup,2,false)

where B2=1202.01 stored as a number. "Lookup" is a named range where the
first column is the item number which is stored as text.

This is returning "#N/A".

What am I doing wrong?

Thanks in advance!! :)

Janet
 
B

Bernard Liengme

Why are you using TEXT. Why not use just
vlookup(B2,Lookup,2,false)

By the way, many people avoid range names that have other meanings (LOOKUP
is a function name)

best wishes
 
J

Janet Panighetti

Bernard Liengme said:
Why are you using TEXT. Why not use just
vlookup(B2,Lookup,2,false)
Click to expand...

I will change named range to "Items" instead of Lookup.

The reason I do not use vlookup(B2,Items,2,false) is because:

B2 is 1202.01 stored as a number.

"Items" has the item number stored as text.

vlookup(B2,Items,2,false) will result in "#N/A" due to type mismatch.


By the way, many people avoid range names that have other meanings (LOOKUP
is a function name)
Click to expand...

I have changed my naming convention. :) Thanks
 
J

Janet Panighetti

In this reply, I am changing the named range from "Lookup" to "Items".

I think I've found one of the problems here. The "Items" ranges stores the
number as text by putting the single quote (') in front of the number.

I'll play with this and see up with which I can come.

Any suggestions are welcome.

Janet :)
 
J

Janet Panighetti

Well, in putzing around looking for a solution, I tripped upon the following:

vlookup(concatenate(b2),Items,2,false)

This WORKED! It's kludgy but it works. I'm going to use this until I find
another solution or it stops working... lol..

Thanks!

Janet
 
D

Dave Peterson

You could use:
=vlookup(b2&"",Items,2,false)

or if you have to match 1234.1 with 1234.10, you may want:
=vlookup(text(b2,"0000.00"),Items,2,false)

If you wanted to convert B2 to a number, you could use:

=vlookup(--b2, ....

The first minus will make the text number a number (but the opposite sign). The
second minus will change the sign back.
 
Top