Have you ever worked in financial services? Are you an excel power user? Do you work with large data sets often?

If you’ve answered NO to all of the questions above, you may have never heard of the mighty excel function, “VLOOKUP”.

screen-shot-2016-10-16-at-9-36-08-am

VLOOKUP gives you the power to search for specific information in your spreadsheet without straining your eyes looking at every cell trying to find your answer. For example, if you have a list of students that have specific scores, you could search for the score of a specific student. Of course you can always do a simple “FIND” (CTRL F) to search for the score, but when you have a large data set, the VLOOKUP will save you more time and create a formulaic way of changing what you are looking for.

We’re going to use VLOOKUP to find the test score for Charlene Davis. You can probably already see that her score is 95.3, but that’s because this is a simple example. Once you learn how to use VLOOKUP, you’ll be able to use it with larger, more complex spreadsheets, and that’s when it will become truly useful.

screen-shot-2016-10-08-at-8-33-33-pm

 

 

 

 

 

We’ll add our formula to cell D2, but you can add it to any blank cell. As with any formula, you’ll start with an equals sign (=). Then type the formula name. Our arguments will need to be in parentheses, so type an open parenthesis. So far, it should look like this:

=VLOOKUP(

Adding the arguments (4)

Now, we’ll add our four arguments. The arguments will tell VLOOKUP what to search for and where to search.

Argument 1 (name of the item):

The first argument is the name of the item you’re searching for, which in this case is Charlene Davis. Because the argument is text, we’ll need to put it in double quotes:

=VLOOKUP(“Charlene Davis”

Argument 2 (cell range that contains the data):

The second argument is the cell range that contains the data. In this example, our data is in A2:B16. As with any function, you’ll need to use a comma to separate each argument:

=VLOOKUP(“Charlene Davis”, A2:B16

Note: It’s important to know that VLOOKUP will always search the first column in this range. In this example, it will search column A for “Photo frame”. In some cases, you may need to move the columns around so the first column contains the correct data.

Argument 3 (column index number)

The third argument is the column index number. It’s simpler than it sounds: The first column in the range is 1, the second column is 2, etc. In this case, we are trying to find the score for the student, and the scores are contained in the second column. This means our third argument will be 2:

=VLOOKUP(“Charlene Davis”, A2:B16, 2

Argument 4 (search for approximate matches)

The fourth argument tells VLOOKUP whether to look for approximate matches, and it can be either TRUE or FALSE. If it is TRUE, it will look for approximate matches. Generally, this is only useful if the first column has numerical values that have been sorted. Because we’re only looking for exact matches, the fourth argument should be FALSE. This is our last argument, so go ahead and close the parentheses:

=VLOOKUP(“Charlene Davis”,A2:B16, 2, FALSE)

You are finished! Now press Enter. It should give you the answer, which is 95.3.

If your results return a #N/A, try this.

Remove Extra Spaces. Use the TRIM function to strip leading and trailing spaces.

If you have other errors, please feel free to send me an email at charlene@charlenerhinehart.com.

Coming Soon! Freebie: Top 10 Excel Formulas For New Accounting And Finance Professionals

One of the biggest limitations of VLOOKUP is that it can only look for values on the left-most column in the table array. So if your lookup value is not in the first column of the array, you will see the #N/A error.

When the range_lookup argument is FALSE, and VLOOKUP is unable to find an exact match in your data, it returns the #N/A error.

Solution: If you are sure the relevant data exists in your spreadsheet and VLOOKUP is not catching it, make sure that the referenced cells don’t have hidden spaces or non-printing characters. Also make sure that the cells follow the correct data type. For example, cells with numbers should be formatted as Number, and not Text.

Use the CLEAN or TRIM function to clean up data in cells.

Leave a Reply

Your email address will not be published. Required fields are marked *