[Solved]: Excel VLOOKUP Not Working with Numbers

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.

2 Useful Solutions for Fixing VLOOKUP Not Working in Excel

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

Converting Text to Numbers for Solving VLOOKUP Not Working with Numbers

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

Converting Text to Numbers for Solving VLOOKUP Not Working with Numbers

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

Converting Text to Numbers for Solving VLOOKUP Not Working with Numbers

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

Converting Text to Numbers for Solving VLOOKUP Not Working with Numbers

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)

Use of Ampersand Operator in the VLOOKUP formula

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

#VALUE! error in VLOOKUP formulas

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.


Related Articles

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I have my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, explore new things, walking randomly in unknown places. Currently, I am working as an Excel and VBA Content Developer. I try to present solutions of problems that are faced in excel in an easy and straightforward manner.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo