V-Lookup with Text Values

[gambils]

When I do v-lookups, I have often had to convert columns from text to number values by doing a paste special where I add a zero or something to make the value I'm looking up a numeric value.

But having an issue with items that are text and begin with a letter, such as V67.1 (instead of a, for example, 198.3 where it comes back fine) . When I apply my "add zero solution" to convert it to a number, it returns #VALUE! in the cell.

Any way I can change that column to numeric values where it still shows the V67.1 and converts the cells that can be changed to a number, such as the 198.3, accordingly?

All this assumes V-lookup only works with numeric values concerning which I could be mistaken?

 

[Joe4]

All this assumes V-lookup only works with numeric values concerning which I could be mistaken?

Yes, you are mistaken. VLOOKUP works for text as well as numbers. The key is that the data you are matching and the data you are looking up must be formatted EXACTLY the same.

So, for example, is you have 198.3 entered as Text, and are looking it up in a range where the values are entered as Numbers, it will not work. The both must be Text, or both must be Numeric.

Also, be aware of leading zeros or extra spaces, as they often cause problems, and be aware that what you see on screen is not necessarily how the data is stored in Excel (look in the Formula bar to see it as Excel does).

For example, you may have a zip code that looks like "01234" on screen. But is you go to the cell and look in the formula bar, you may see the value "1234". The leading zero is not really there, it is just that a Custom Format has been applied to make it display that way.

 

[gambils]

I thought that might be the case which is why I was doing the conversion to numeric which has worked before. Assuming I can't make the columns numeric, any way I can convert both to text?

 

[Joe4]

Highlight the Column, and from the Data menu, select "Text to Columns".
Hit the Next button until you get to Step 3, and select the "Text" radio button and then click Finish.