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
is the value you are searching for in the
set (the second parameter). The third parameter
is the column from your data set that you wish to return, and the optional fourth parameter
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
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
column, but what happens when you can clearly see that the value you’ve entered into the first parameter is in fact there?
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
parameter clearly matches the values in the data set I’m searching, however, what might
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
to be a number. If the data set’s first column is strings, then I make sure my first parameter in the
function is a string too.
Here are some examples of how I quickly convert the
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).
errors which can easily be fixed when using the
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.