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.


Comments

Post a Comment

Popular posts from this blog

Mint vs. CreditKarma - Personal Finance Management Products Comparison

Image
Mint Do you need to take better care of your finances?  Would you like to see all of your accounts in one place? Would you like to receive advice on how to reach your financial goals?   Credit Karma Then you should read further to see whether Mint or CreditKarma (or both tools) will help you on your journey... Description:   First of all, let me answer this question. Do you need either of these services? I believe so and here is why. Most of us - even those of us with college degrees - never really learn how to take care of our money and because of that, we struggle coming to terms with unfulfilled dreams that all go to the way side because we did not have the means to take control. So here are two tools that are available FREE of charge that will help you take control over your finances and they both have their own benefits and drawbacks that I will try to elaborate on based on my own experience.  Evaluation of Pros and Cons: ...
Read more

Doodle - Free Scheduling & Polling Tool

Image
Description: Need help finding time that works for everyone to schedule a meeting? Doodle is the  free scheduling tool you have been looking for.  Doodle can actually be looked at as multiple tools in one. It makes it easy to schedule appointments (single or group) and it can also be also used as a polling tool. When you create a free account, you can use both of these features and you can also sync your calendar with Doodle so that you do not need to switch from one to the other when you schedule a meeting (Doodle supports: Outlook, Google Calendar, iCal and Yahoo Calendar). Once you sign up for this service, you have two main options: To schedule and Event or to start a Poll. The following steps outline the process of scheduling an event (which is essentially a specific type of a poll). In the first step (of either type) you fill out basic information about your event/poll.  In the next step you select the possible dates you would like to hold your event...
Read more

Trello - Free tool that lets you organize your life

Image
Description: Trello can be used to organize anything from simple to-do lists to research projects or event planning. You can work on your own or collaborate with others. Additionally you can access these lists from all of your devices so waiting in line for your Starbucks coffee just became very productive. It is incredibly flexible, it allows you to create text or picture based "cards" that are behind the organization. Below you can see a sample board that someone has created. Trello - Example of a Board The  Trello  cards can then be organized, labeled, color coded... The front of the card provides a summary of the event. Trello - Card Front Side The back of the card provides all of the details and notes necessary for you to learn more about what is happening or needs to happen. Trello - Card Back Side Once you sign up, this is what the welcome board looks like. This board will explain to you how to use  Trello  and you can then set up your own boar...
Read more