The
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:
| A | B | C | |
|---|---|---|---|
| 1 | ID | Employee Name | Salary |
| 2 | 1001 | Smith, John | 85,000 |
| 3 | 1010 | Doe, Jane | 105,000 |
| 4 | 1011 | Doe, John | 60,000 |
| 5 | 1001 |
85,000
=VLOOKUP(A5, A1:C4, 3)
|
VLOOKUP
formula 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?
| A | B | C | |
|---|---|---|---|
| 1 | ID | Employee Name | Salary |
| 2 | 1001 | Smith, John | 85,000 |
| 3 | 1010 | Doe, Jane | 105,000 |
| 4 | 1011 | Doe, John | 60,000 |
| 5 | 1 |
#N/A
=VLOOKUP(A5, A1:C4, 3)
|
VLOOKUP
formula 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?
Having used
VLOOKUP
so many times, here is how I use to solve the
#N/A
problem:
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).
Summary
Besides
#REF
and
#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
#N/A
error.
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.
