# [Solved]: Excel VLOOKUP Not Working with Numbers

Get FREE Advanced Excel Exercises with Solutions!

The VLOOKUP function is a handy function to find things or values in a table or array. By utilizing the VLOOKUP function, we can get required column data in return from a specific table or array if the first column of the array meets certain criteria. However, we sometimes face errors when applying a criterion that consists of Numeric Data. In this article, I will show you how we can fix the issue when the VLOOKUP function is not working properly with numbers.

## Excel VLOOKUP Not Working with Numbers: 2 Possible Solutions

In this section, we will demonstrate 2 effective solutions to fix the stated problem in Excel with appropriate illustrations. But before jumping into the solutions, let’s see what our problem looks like. Here, I have attached a screenshot where I have two tables. As we can see, on the left table, we have Some Student Ids and their Marks on a subject. On the right side, the table contains Names and Student IDs. Furthermore, the Marks column is supposed to be filled up using the VLOOKUP function from the left table. So we use the following formula to obtain Marks using the VLOOKUP function.

`=VLOOKUP(F5:F9,B5:C9,2,FALSE)`

Here we look for value form F5:F9(Student ID) in the 1st column of the B5:C9 array and return the 2nd column value(2) from the array which is Marks.

But, unfortunately, we got this error. Now, to get rid of this error, we can follow either of the two solutions given below.

### 1. Converting Text to Numbers

It might be the case that the conditions that we have assigned to the VLOOKUP function are not uniformly formatted. For example, in this case, maybe the Student ID column on the two tables is not in a single format. To check and remove this error, follow the steps below.

Steps:

• The easiest way to check is to pick any two corresponding cells from those two columns and write the following in any cell on the worksheet. (I have taken B5 and F5)
`=F5=B5` • If the result comes TRUE, then they are in a uniform format. On the other hand, if the result comes FALSE, then they are not in a uniform format. • As the result is FALSE, hence the formatting needs to be changed to make those cells uniform. By inspecting, I found that the Student ID column of the left array is not in the Number format but rather in the Text option. • Now, to convert the column from text to numbers, first, select the column (B5:B9), and from the Data Tab, select the Text to Columns option.  • As a result, you will see that the previously shown error messages have gone. ### 2. Use of Ampersand Operator in the VLOOKUP formula

Now, for removing the error message, there is an alternative to change the formatting of the column data. Instead of changing the column format, we can slightly modify the VLOOKUP formula with an Ampersand sign (&) to gain our desired result without any error messages. To do this, follow the steps below.

Steps:

• In the formula bar of G6 cell, write down the following formula.
`=VLOOKUP(F5:F9&"",B5:C9,2,FALSE)` • Here, we just added &“” after F5:F9. This will make the value received as Text. So we don’t need to manually change the formatting.
• After pressing Enter, you will get the desired error-free result. ## Fixing #VALUE! Error in VLOOKUP Formula

While using the VLOOKUP function, if the conditional cells contain more than 255 characters, then you will see the error like this below. Here, in the Student Id column, the cell values are more than 255 characters, hence this error is showing. To solve the issue, instead of using the VLOOKUP function, use the combination of INDEX-MATCH functions. To learn more, follow the steps below.

Steps:

• In cell G6, write down the following formula.
`=INDEX(\$C\$5:\$C\$9,MATCH(TRUE,INDEX(\$B\$5:\$B\$9=\$F5,0),0))` • Now, autofill the rest of the cells with Fill Handle. You will get the desired result. 🎓How Does the Formula Work?

• INDEX(\$B\$5:\$B\$9=\$F5,0)

It searches the value in of F5 in (B5:B9) column and returns an array of True(if matched) and False(If not matched).

• MATCH(TRUE,INDEX(\$B\$5:\$B\$9=\$F5,0),0)

The MATCH function returns the row number which is TRUE in the array returned by the INDEX function

• INDEX(\$C\$5:\$C\$9,MATCH(TRUE,INDEX(\$B\$5:\$B\$9=\$F5,0),0))

This returns the value of the cell in the (C5:C9)array in which the row number is equal to the result of the MATCH function.

## Things to Remember

• Always check the data type of conditional cells before applying the VLOOKUP function

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects. Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  