VLOOKUP - Excel Function Everyone Should Know

I usually post on this blog about different technologies, most of them web-based. It occurred to me however, that perhaps people would like to learn some cool functions they can use with Microsoft Excel. VLOOKUP is one of those Excel functions that can be sometimes overlooked but perhaps everyone should know about because it can save you a lot of time and can be very powerful when dealing with large data sets.

What exactly is VLOOKUP?

Basically, VLOOKUP lets you search for specific information in your spreadsheet. For example, if you have a list of players for a particular sports team and some basic information about them, you could search for the name of someone based on their number. To go along and work on the examples, please download the spreadsheet.

In this first example, we are going to use VLOOKUP to find the name for number 88. You can probably see that it is Jessica Chandler, 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.

Setting up a VLOOKUP Formula

Let's now create the formula in cell G2 (though we could have chosen 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(

The first argument is the name of the item you're searching for, which in this case is 88. Because the argument is a number, we can just provide it, but please remember that if you are looking for a textual value, you'll need to put it in double quotes:

=VLOOKUP(88

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

=VLOOKUP(88, A2:F20

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 88. In some cases, you may need to move the columns around so the first column contains the correct data.

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 name, and that information is provided in the second column. This means our third argument will be 2:

=VLOOKUP(88, A2:F20, 2

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 can be useful when approximate value is sought, however because in our case we are actually looking for an exact match, the fourth argument should be FALSE. This is our last argument, so go ahead and close the parentheses:

=VLOOKUP(88, A2:F20, 2, FALSE)

That's it! When you press Enter, it should give you the answer, which is Jessica Chandler.

VLOOKUP Formula Example

How does VLOOKUP work?

The formula first searches vertically down the first column (VLOOKUP is short forvertical lookup) until it finds a match for the first parameter88 in our case and then it returns the value it finds in the second column Jessica Chandler.

If we want to find the name of a different player, all we have to do is change the first argument:

=VLOOKUP(32, A2:F20, 2, FALSE)

or:

=VLOOKUP(11, A2:F20, 2, FALSE)

NOW TRY IT FOR player number 15 - creating the formula again from scratch in cell G5

Another example

This can of course work with problems that need to return value that is not in the second column. For example, if we wanted to know the home town/previous school of player number 21, we can use the VLOOKUP function to find this information.

To find this information, we will need to change our arguments in our formula. Please note that if the range in your formula did not already include other columns, now it will need to include them. We'll change the column index number to 5 because home town information is in the fifth column:

=VLOOKUP(21, A2:F20, 5, FALSE)

When you press Enter, you'll see where Chanelle Ward is from. 

VLOOKUP Formula Example

NOW create a VLOOKUP formula from scratch in G9 that will return the Year in School for player 4

To challenge yourself - How would you create a VLOOKUP that would find the Major for Alexandra Moore?

You should also know that you can lookup values in different sheets of a spreadsheet which really offers incredible value when dealing with more complex datasets. 

Summary

I hope you find VLOOKUP to be useful for your daily needs. VLOOKUP is certainly a tool that becomes more powerful the more you use it and the more familiar and comfortable you become with the different ways in which it can be beneficial. If you would like to learn more about other functions, please do not hesitate to let me know! Alternatively, if you are interested in learning more about Excel from the basics to more advanced topics, the Excel Bible offers it all!

Available for purchase on Amazon and other sellers.