Vlookup Numbers/Text

[MikeDBMan]

We use numbers for various things in our ERP system. When we dump data into Excel sometimes those numbers show up as numbers and sometimes they show up in Excel as text. If I want to do a vlookup on that field, I have to ensure my lookup is looking at a number so both are numbers and text so both are text. This is a pain. I was trying to think of a way to work around that issue. Any suggestions?

 

[JackDanIce]

Try something like:

=VLOOKUP(A1+0,table,column_number,0)

"+0" makes Excel evaluate a number in text form to become numerical

 

[MikeDBMan]

That presupposes that I would always be looking up text. I could make sure that is always the case but if the field's value is "123A" then adding 0 might not work. But maybe I can take that into account. I'll play around with it. Thanks!

 

[MrVillareal]

You should always test your data using ISNUMBER OR ISTEXT before doing your vlookup to ensure quality results, be careful also of extra spaces for this you can try TRIM to remove those extra spaces.

 

[JackDanIce]

I focused on this part of your posting:

When we dump data into Excel sometimes those numbers show up as numbers and sometimes they show up in Excel as text. If I want to do a vlookup on that field, I have to ensure my lookup is looking at a number

I took that to mean you were always wanting a numerical look up value and not a mixture of alphanumeric characters. Perhaps if you provided an example, it would less easy to misinterpret what help you are after

 

[MikeDBMan]

JackDanIce, I agree with your reasoning on why you answered the way you did. There are rare cases where the number does contain alpha characters and I forgot to take that into consideration in my posting. I believe your solution will work and will go with it. Thanks for posting!