Excel Vlookup - 3 Practical Ways to Identify and Solve Problems in the Lookup Column

The easiness of performing a Vlookup stops when you deal with messy data. Low quality tables lead to non-matching headaches. The problems are located either on the lookup column or the index column of the source table. The most common glitches are:

• Invisible characters
• Numbers stored as texts
• Spaces
• Undesired characters

These issues have to be managed carefully. Here I will show you handy techniques you can use to identify and get rid of those problems.

1) Identify the cells that are not numbers Use the function N. The N function converts a non-number value to a number. Numbers are returned the same, and non-numbers are returned as 0.

Write the function on an auxiliary column this way: =N(A2). See column D below…

You can also use the VALUE, ISNUMBER, ISTEXT functions with similar results.

Write the function on an auxiliary column this way: =ISNUMBER(A2). See column D below…

2) Make spaces evident to the eyesight

Write the CONCATENATE function on an auxiliary column this way: =CONCATENATE("*",A2,"*"). See column D below…

You can also use the LEN function

This tactic is useful when you deal with same-length cells. Those cells that contain spaces will have a greater dimension.

Write the function on an auxiliary column this way: =LEN(A2). See column D below…

3) Show flags for numbers stored as texts

Go to Options>Formulas>Error Checking

Then assign a color that gets your attention. See below…

You can easily identify those cells that has problems

4) Highlight cells that are not the type of data you want

You can do this easily by using conditional formatting. The example below is showing the cells that are texts as red.

How do you do that?

Launch the conditional formatting command and then…

Choose the option “Use a formula to determine which cells to format”

Use the functions: ISTEXT, ISNUMBER or combine them with IF formulas:

5) Reveal numbers that are texts easily

Adding 0 to a text would produce a #VALUE error, this is an easy way to detect numbers that are not numbers

Write the function on an auxiliary column this way: =A2+0. See column D below…

6) Filter cells that contain spaces or any other undesired character

Apply a filter to the lookup column, use the “Contains” criteria and type a space as the character. See below

Now that you have identified the problems; how to fix them?

The surest way to do it is by replacing things by hand, of course you don’t want to do it this way so; here I share to you handy techniques to do it…

How to convert numbers stored as texts to numbers

• Use this simple formula: --(A2). The double dash in the formula makes the job of converting the number-as-text to number
• Convert them massively. Select the problematic column and click the warning icon and then choose “Convert to Number”. All numbers-as-text in the selection will be converted to numbers. If the warning icon is not shown after the selection, use the Tab key until one cell that contains the mark is highlighted

How to get rid of spaces

• Use the TRIM function

=TRIM(A2)

Take into account that this function does not work all the time.

• When the cells contain equal length values you can extract the left portion that is correct.

Use the MID function this way: =MID(B2,1,4)+0

Add 0 at the end to convert number-as-texts to numbers automatically.

• My non-orthodox way

When the cells contain variable-length data you can replace the “*” character twice and then add 0 to convert the cell to a number. This is the function…

=SUBSTITUTE(SUBSTITUTE(B66,CHAR(160),"*"),"*","")+0

Believe or not, there are some cells that contain weird spaces (Excel interprets these spaces as a different character). When you replace spaces by “*”, a space remains, so I use the SUBSTITUTE function twice. On the second replacement you get rid of it and then you are ready to work with those cells.

Conclusion.

Use the above techniques to catch problems in your data, combine them as per need and then get rid of those intruders using the above tactics or your own techniques.

Don’t stop cleaning your data until it is free of problems. Why starting to write a Vlookup if you know the source table or lookup column contains problems.