## A common VLOOKUP problem with an easy fix

**The situation** – VLOOKUP fails…

Have you ever encountered something like this? This is quite common. We are sure that the VLOOKUP formula is correct. We are sure that the lookup values are available in the lookup table (101, 106 107 in our example above). What we are not sure is the reason of the error returned… What happened?

The answer is simple: The “101” in B5 is different from the 101 in F5. What?? What’s the difference? The former is text, while the latter is numeric value. This is a basic but important concept for all Excel users, which I had discussed in the post HERE.

To fix the problem, we need to fix the data type in the lookup table. In other words, we need to make sure that all values for Room No. in the lookup table should be **text **(because the list of the lookup values in column B is **text**.

You may be wondering; can we simply change the cell format to text. See below:

Unfortunately, it does **not **work!

We cannot simply convert a number to text by changing the cell format. If we want Excel to accept a numeric input as text, we should set the cell format to Text BEFORE, not after the value is input.

We can use the function **ISTEXT** to test whether a value is text or not. See below:

Now we understand the problem. It’s time to show various solutions using formula approach. The trick is to use the function **TRIM **to convert all values under the lookup column into text to make the formula work.

You may download a sample file to follow along.

Let’s start with something simple, with a helper column.

**Solution 1 – Helper Column**

This would be the easiest way for and be welcomed by most users. Why is that? Because it is easy to execute and follow along. What is needed is a helper column next to the original lookup table. The goal is to convert all the values of Room no. into text. The function TRIM does the job.

In F5, input the following formula:

= TRIM(G5) 'Copy down

As you see in the following screenshot, we can construct the **VLOOKUP **formula as usual with the helper column. One thing we need to pay attention to is the new *column index number* (counting from the helper table).

And this is the formula input in C5:

= VLOOKUP($B5,$F$5:$I$15,3,FALSE) 'Note: the column index is 3 instead of 2

**Solution 2 – VLOOKUP with TRIM**

In some situations, we may not be able to add a helper column for whatever reason. If that’s the case, we can perform the **VLOOKUP **with a slight modification. And you won’t believe how simple the trick is.

This is the original formula that fails:

=VLOOKUP($B5,$G$5:$I$15,2,FALSE)

This is the modified formula that does the trick:

=VLOOKUP($B5,TRIM($G$5:$I$15),2,FALSE)

As you see, the trick is to wrap the lookup table with **TRIM**. The TRIM function here technically converts all values in the lookup table into text, and that is what we needed… even more than what we need. The following screenshot shows the new problem:

This modified formula works as along as the values to be returned are all text. In case we expect numeric values, it works only partially as all the results returned by the formula would be text. This is what we instructed Excel to do with **TRIM**.

Luckily, there is an easy fix. When we expect to return numeric values, we may modify the formula by adding two negative signs in front of **VLOOKUP**, as shown below.

In E5 = --VLOOKUP($B5,TRIM($G$5:$I$15),3,FALSE) 'Copy down

Wondering why? Please read the blogpost HERE.

As we have just seen, using **VLOOKUP,** and trimming the entire lookup table is not ideal. It would be nice if we could trim only the lookup keys. This is the moment we need solution 3 and 4. 😁😁

**Solution 3 – XLOOKUP with TRIM**

**XLOOKUP **is available in Microsoft 365 for Excel, and it is the perfect substitution of all existing lookup functions, in the future… because there are still quite a lot of users having no access to **XLOOKUP**. 😞

Talking about **XLOOKUP**, the arguments required is different from **VLOOKUP**. There are many differences which is not the key points in this post. What we need to know for this post is that the *lookup table array* and *column index* *number *are not required in **XLOOKUP**. Instead, we need to input the *lookup array *and the *return array *as the second and third arguments.

This arrangement makes the function much more flexible than **VLOOKUP**.

In C5, =XLOOKUP($B5,TRIM($G$5:$G$15),H$5:H$15) 'Copy down and right

Using the same trick of **TRIM**, the following formula works like a charm without the hassle of returning numbers as text.

Nevertheless, there is a high chance that we don’t have **XLOOKUP **(at work). No worries, **INDEX **and **MATCH **comes to rescue.

**Solution 4 – INDEX and MATCH, with TRIM of course **

There are lots of debates about which is better, **VLOOKUP **or **INDEX **and **MATCH**? It depends. Honestly in most scenarios, I use **VLOOKUP **simply because I am so getting used of it. Having said that, **INDEX **and **MATCH** wins in this situation as we can convert the *lookup array* instead of the entire lookup table.

Here’s the formula:

In C5, =INDEX(H$5:H$15, MATCH($B5,TRIM($G$5:$G$15),0)) 'Copy down and right

The logic behind is similar to the **XLOOKUP**. We specify the *lookup array *(inside **MATCH**) and the *return array* (inside **INDEX**) as two arguments, *index column number* is not required… just be reminded that the *return array *comes first in this construction.

Want to learn more about how this powerful combo work? Read the blogpost HERE.

Want to learn more about **VLOOKUP**? I have many blogposts about it. Check it out HERE. 😉

What do you think? Please leave your comments below.

Instead of the TRIM() function you can use the & operator: A1&””

It could be interesting to check how they differ in performance.

LikeLike

Thanks XLarium for your suggestion, a good one! There are so many ways to Excel 😄

LikeLike