# Excel VLOOKUP is Not Working with Numbers – 2 Solutions

This is the sample dataset.

The table on the left showcases Student Ids and their Marks.

The table on the right contains Names, Student IDs and a Marks column.

• Enter the following formula in F5.
`=VLOOKUP(F5:F9,B5:C9,2,FALSE)`

the formula looks for a value from  F5:F9(Student ID) in the 1st column of  B5:C9 and returns the 2nd column value(2) in the array, which is Marks.

An error is displayed.

### Solution 1 – Converting Text to Numbers

Steps:

• Select two cells from columns B and F (B5 and F5).
• Enter the following in any cell in the worksheet.
`=F5=B5`

• If the result is TRUE, they have a uniform format. If the result is FALSE, then they have a different format.

• As the result is FALSE,  the formatting needs to be changed.
• Cells in the Student ID: column F are not in Number format. They are in Text format.

To convert the column from text to numbers:

• Select the column (B5:B9).
• In the Data Tab, select Text to Columns.

This is the output.

### Solution 2 – Use the Ampersand Operator in the VLOOKUP formula

Steps:

• Select G6 cell and enter the following formula.
`=VLOOKUP(F5:F9&"",B5:C9,2,FALSE)`

&“”  was added after F5:F9 to convert the Text value.

• Press Enter to see the result.

## Fixing the #VALUE! Error in a VLOOKUP Formula

If the conditional cells contain more than 255 characters, an error will be displayed.

Steps:

• In G6, enter the following formula.
`=INDEX(\$C\$5:\$C\$9,MATCH(TRUE,INDEX(\$B\$5:\$B\$9=\$F5,0),0))`

This is the output.

Formula Breakdown

• 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, whose row number is equal to the result of the MATCH function.

## Things to Remember

• Check the type of data of conditional cells before applying the VLOOKUP function.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF