**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.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**2 Possible Solutions When VLOOKUP Is Not Working with Numbers in Excel**

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.

- Now, a
**Convert Text to Columns Wizard**dialogue box will appear like the figure below. From there, click on**Finish.**

- As a result, you will see that the previously shown error messages have gone.

**Read More:**** [Fixed!] Excel VLOOKUP Not Working Due to Format (2 Solutions)**

### 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 step 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.

**Read More:** **[Fixed] Excel VLOOKUP Returning 0 Instead of Expected Value**

## 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.

**Read More:** **[Fixed!]: VLOOKUP Function Is Returning Same Value in Excel**

**Things to Remember**

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

**Conclusion**

That is the end of this article. If you find this article helpful in solving the issue when the **VLOOKUP** function not working with numbers then please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit **Exeldemy** for more exciting articles on **Excel**.