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 parameterSo 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 foundIt 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.