VLOOKUP formula is a popular function for getting the value from a tabular data set and has 3 required parameters and an optional fourth, and looks something like this:
=VLOOKUP(reference_value, data, returning_column_value, [is_sorted])
The first parameter
reference_value is the value you are searching for in the first column of your
data set (the second parameter). The third parameter
returning_column_value is the column from your data set that you wish to return, and the optional fourth parameter
is_sorted is a boolean type where if this is true (by default it is) then the returned value from this formula will be its first match, otherwise if
FALSE then returns the closest match.
Here’s an example of the VLOOKUP in action in this simple spreadsheet example:
VLOOKUPformula at work, using a cell reference as the first parameter
So if we look at the above example, and reference a value that doesn’t exist in the first column, what happens?
VLOOKUPformula on an ID that does not work, we get an error that the value 1 could not be found
It may be obvious from this small example to see that I don’t have the value
1 in the
ID column, but what happens when you can clearly see that the value you’ve entered into the first parameter is in fact there?
VLOOKUP so many times, here is how I use to solve the
Check Reference Value Data Type
It can sometimes appear as though the number I have in the
reference_value parameter clearly matches the values in the data set I’m searching, however, what might appear to be a number when looking at the spreadsheet may actually be text!
Therefore to get around this problem I convert the first parameter to match the data type values. For example, if the first column of data has all of its first column values as numbers then I change the
reference_value to be a number. If the data set’s first column is strings, then I make sure my first parameter in the
VLOOKUP function is a string too.
Here are some examples of how I quickly convert the
reference_value being passed in to match the data set.
# where A5 is text and the data set's first column is number values =VLOOKUP(A5*1, A1:C4, 3) # where A5 is a number and the data set's first column is text values =VLOOKUP(A5&"", A1:C4, 3)
Another easier way could also be to select the cells comprising of the data set’s first column and to make them all the same data type (in Google Sheets select the Format menu item then Number then either Number or Plain Text).
#VALUE errors which can easily be fixed when using the
VLOOKUP formula, the lesser known, yet most frustrating error when using this formula is the
The most common reason for this type of error comes down to the different data types being used in both the first parameter of the VLOOKUP function and the first column in the data range being referenced.
To have this formula function properly you need to ensure they are of the same type.