Hope you are having a great Friday afternoon!  I took some time out my day to learn a little more about lookup and reference functions because I actually encounter plenty of tasks at my internship in which these tools could expedite the processes.

Vlookup

I’ll start with the most relevant function called “Vlookup.”  Vlookup is a function or tool that Excel uses to look up and get data from a column in a table. The values that you are “looking up” are in the first ~vertical~ column of the table (hence the name) and the lookup columns are to the right.

Vlookups are helpful with a mass of information that needs to be matched to a set of values from a table. It is essentially a more complex and reliable “Find and Replace” function for complex data.

Like all functions in excel, there is a proper “syntax” or command order that you must input the instructions in; if you do not do so, the Vlookup will most definitely fail…speaking from experience.  Here is what you enter in the formula bar =VLOOKUP (value, table, col_index, [range_lookup]) Each section between these parentheses is called an “argument,” so I will break down each argument for you from the example in Figure 1.

• Value – This is what you are looking for in the first column. In this example, I am looking at column A so I would enter A2 here (Keep in mind, we will copy this formula down the column).
• Table – This is the table that you will get the values from. I like to think of these table as the “code.” Because I am pulling from the area that contains the organized information, I will enter Projections!A2:T270 into this argument on this example.
• Col_index – This was the most confusing part of this tool that I met. This argument tells Excel the column in the table from which to retrieve a value. This number, typically a smaller value from 1-5, is relative to the original set of values. In this example, I would enter 3 because that is the third column from the data which gives me the information that I want.  This information was pulled from the “Projections” worksheet in the attached example.
• Range_lookup – This section of the argument is quite common when using functions and is (FUN FACT) an optional entry in this case. Your options are either “TRUE” or “FALSE.” When you enter “TRUE,” you are searching for an approximate match; when you enter “FALSE,” you are telling Excel to search for an exact match. I have learned a few different opinions on this, but for most purposes, it is better to enter “FALSE” because then you know when there has been an error.

Here are some tips when using this function that I have discovered along the way:

1. Vlookup is made to retrieve data from a table organized into columns in which each row is a new section of data. If you want to do this same technique but have your data organized horizontally, then you can use Hlookup which is exactly the same function with columns and rows switched.
2. Vlookup will only search to the right. It is important to remember this when setting up your data ¬– the base values that you are searching should always be to the left.
3. Vlookup will only match data based on its column number which may or may not be different than its assigned column letter. You have to imagine that each column in your table is numbered, but these shouldn’t be hard because you won’t have more than a few, most likely.
4. I am attaching a spreadsheet below that actually uses Vlookup formulas to help a group of guys predict fantasy football pics…a humorous application, but useful nonetheless.

VLOOKUP_Fantasy Basketball

Match and Index

This function will work similarly to the Vlookup in terms of the different arguments that you must input.  However, the “MATCH” function actually gives you the position of a value in a given range.

For example, this list of firms has already been sorted by revenue from largest to smallest.  If I was asked to find the account number of the nth largest firm by revenue from a long list of account, then I could easily use the MATCH function to do so as seen below.

The formula is =MATCH(lookup_value, lookup_array, [match_type]).  I will again explain how you know what to type in for each section of this argument in the parentheses.

• Lookup Value – This is what you are looking for in the first column. In this example, I am looking for a specific account number so I would enter 26.
• Lookup Array – This is the column or area that you will get the values from. I like to think of these table as the base. Because I am pulling from the area that contains the organized information, I will enter A2:A15 into the argument on this example.
• Match Type – This, like the Vlookup situation above, tells Excel how exact to make the return position.  In most cases, you will want this to be as accurate as possible, so go ahead and make a habit of seeing this to 0.

The “INDEX” function is relatively similar, so it is easiest to go ahead and introduce this one as well.  This function actually gives users a specific value in a specified range.  In the example below, I would tell it to give me the 10th largest firm instead of the other way around.

The formula is =INDEX(lookup_array, lookup_value) and it follows the same instructions as before.  These can actually be used in conjunction with each other to perform a two-way lookup!

If you want to practice this tool, I have attached my internship document in which I got to use this function, but I changed information to preserve confidentiality.

MATCH & INDEX Account Info

There are a variety of reference functions to be learned in Excel, but these are the basis for the rest of the sets and set the stage for more complexity.  You can always use Find (Cmd+F) or Find & Replace to change data quickly just as you would in other applications.

Thanks for reading, and I cannot wait to explore more together next time!