VLOOKUP is a basic Excel function that finds a desired value by setting a lookup value in vertically arranged data. It is likely one of the basic Excel features many people use in their daily work. Because it is widely used in many situations, you may have experienced frequent errors. Today, let’s look at how to resolve VLOOKUP function errors.
VLOOKUP Function Errors and Solutions
The Excel VLOOKUP function has a very straightforward logic.
=VLOOKUP (lookup_value, table_array beginning with the lookup value, column index number of the value to return, exact match)
Let’s look at how to resolve errors in the lookup value, table array, and column index number highlighted in red.
Resolve VLOOKUP Lookup Value Errors
Excel values can be broadly divided into two types: “TEXT” and “NUMBER.”
In the SUM family of functions, the value type is generally not a major issue, but it is one of the arguments that must match when using the LOOKUP family of functions.
Data Is “NUMBER,” Lookup Value Is “TEXT”
Even when you have written the VLOOKUP formula correctly, check whether the value types match if it cannot return the correct value.
The image below shows, on the left, data recording the Monday-through-Friday sales quantities for a total of 10 products.
The product values in the data table are “NUMBER,” while the value in “I3 cell,” which will be used as the lookup value in the VLOOKUP function, is “TEXT.”
The Excel VALUE function was used to convert the value type from “TEXT” to “NUMBER.”
=VLOOKUP(VALUE($I$3),$B$2:$G$12,5,0)
What Is the Excel VALUE Function?
The Excel VALUE function converts numbers entered in text format to number format.
The function syntax is as follows.
=VALUE(TEXT)
Data Is “TEXT,” Lookup Value Is “NUMBER”
The product values in the data table are “TEXT,” while the value in “I16 cell,” which will be used as the lookup value in the VLOOKUP function, is “NUMBER.”
The Excel TEXT function was used to convert the value type from “NUMBER” to “TEXT.”
=VLOOKUP(TEXT($I$16,”##”),$B$15:$G$25,5,0)
What Is the Excel TEXT Function?
Resolve VLOOKUP Column Index Number Errors
You must enter the correct position of the value to return to get the desired result.
While simply counting the columns and entering the column index number into the VLOOKUP function is also correct, using the MATCH function when working with many arguments helps return accurate values without errors.
=VLOOKUP($I$29,$B$28:$G$38,MATCH($J$28,$B$28:$G$28,0),0)
Resolve VLOOKUP Table Array Issues
When repeatedly using the VLOOKUP function in Excel, entering the data range can also be cumbersome.
In this case, you can resolve the issue by using the table name feature.
=VLOOKUP($I42,DATA,MATCH(J$41,$B$41:$G$41,0),0)
VLOOKUP Alternatives
The main alternatives to VLOOKUP are the INDEX MATCH function and the XLOOKUP function.