As the school year nears, I can count on a number of spreadsheet questions. One popular Excel question is, how do you retrieve a value from one Excel cell and use it someplace else in Excel? In this tutorial, Ill step you through how the Excel VLOOKUP function helped me with some data analysis. This article includes an Additional Resources section at the end including a video tutorial and downloadable VLOOKUP example worksheets.
If youre using Google Sheets, please seeUsing Google Sheets VLOOKUP.
When it comes to learning Microsoft Excel functions, I like to start with an easy example. This tutorial will provide two scenarios using different arguments and lookup values.
Feel free to jump ahead to any section by clicking a link below.
VLOOKUP is an Excel function that allows you to search and retrieve a cells content from another column. As you might guess, that V stands forverticaland relies on looking up data from the leftmost column of alookup table.
This column could be on the worksheet youre using or another. The function requires acommon field or keyand fourarguments. The function allows you to specify whether to use anexact matchorapproximate match. A common example might be looking up the name of a product from its SKU.
As well see shortly, Excel provides hints about which arguments are required.
Over the years, I have volunteered to work on local elections. The part I tend to get assigned is the data analysis of the voter registration file. These tend to be large files with lots of information.
One data element is the voters birthdate. The worksheet data gets sliced, diced and repurposed. However, I didnt want the voters birthdate to show on any of the distributed files.
Instead, I decided to create segments based on age ranges. I used a vertical lookup that would return the value to the desired cell. (Yes, some people are still mad at me for not revealing peoples ages.)
Lets start with the first fictitious voter, Sophia Collins. If you scan across to Column D youll see she is 39 years old and in the Mature segment. The value of Mature in Column E was dynamically pulled in using Excels VLOOKUP function.
The small table to the right with blue headings is thelookup table. Some people also refer to this lookup table as atable array. This is where Ive defined my 4 age segments.
The way segments work is if a voters age is under 21, they are New. From 21-38, they are Young. From 39-59, they are Mature and if they are 60 or older they are Senior.
In the case of Sophia, Excel would take her age of 39 from cell D2 and find the closest match from Column H.Both these columns contain age data, which is our common key.
When Excel found a match it would then go to Column I and get the Label. That value was then copied to cell E2, the Segment. It might help to think of columns as being vertical. After all, this is a vertical lookup.
You might notice that the lookup table doesnt list every age. It doesnt have to because Im using anapproximate match. Im telling Excel to find me the closest age.
For example, the next voter Evelyn Bennett is 51 but there is no value for 51 in Column H. In this case, 51 falls between 39 and 59 so she is also labeled Mature.
As you probably guessed,to use the VLOOKUP function there needs to a be a common key. In this case, its age. Both columns D and H contain ages.The headings and cell contents can be different.
Lets peel away some of the mystery and display how VLOOKUP shows in the formula bar. In this illustration, Ive clicked cell D2.
[A] This represents the VLOOKUP formula for Cell D2
[B] D2 is our first argument calledLookup_value.
[C] $H2$2:$I$5 is ourTable_arrayand the second argument.
[D] 2 is theCol_index_numfrom your Table_array and the third argument.
[E] TRUE is theRange_lookupand the fourth argument.
The term argument isnt as complicated as it sounds.If youre familiar with the Excel formula bar, an argument is what goes in between the parentheses (). It provides some sort of input value for an Excel function.
Some functions have required arguments and others dont need arguments. For example, to compute the voters age I also used the TODAY function=TODAY()which doesnt use any arguments. Some common argument examples include:
Using the formula from cell D2, heres how these arguments work.
1. Lookup_value Think of this field as your starting point. In this example, I want to look up Sophias Age from cell D2.
2. Table_array This is the cell range for your lookup table. This range lookup can be on your existing worksheet or another worksheet.
There are several rules to remember about this table array.
left column must contain the values being referenced
. Leftmost doesnt mean it has to be in Column A. Its just the leftmost column on the array. For example, on the table array above, the leftmost column is H.
in the leftmost column of the lookup range. I couldnt have two entries with the value 39 with one being Mature and another 39 for Go Getter. Excel would complain.
When referring to the lookup table, you want absolute cell references when you copy the VLOOKUP formula to other cells
For example, if I want to use the same formula in cells E3 through E11, I dont want my lookup cell references shifting each time I move down to the next cell. I need the cell references to be constant. This is called anabsolute cell reference.
After you define your lookup range of cells, you can pressF4. This will cycle through absolute and relative cell references. You want to select the option that includes a $ before your Column and Row. You can get around this if you know how to useExcel name ranges.
3. Col_index_num This is the number of the column on your lookup table that has the information you need. In our example, we want column 2 from the Label column. This will become our Segment name.
When we count, were counting the columns on the lookup table. Even though the Label column is Column I or the 9th column, its the 2nd column on the lookup table. Some people call this aColumn Index.
4. Range-lookup this field defines how close a match should exist between your Lookup_value (D2) and the value in the leftmost column on our lookup table. In our case, we want an approximate match so well use TRUE.
TheFunction Argumentsdialog will appear with textboxes for the required arguments.
Your Function Arguments dialog should look like the following. Notice in the lower left, you can see theFormula result.
. You should now see Mature in cell E2.
Click the small green square (fill handle) in the cells lower right corner to copy the VLOOKUP formula down the column.
The second scenario also dealt with that same election file. This time there was an extra worksheet for political parties. The voters party was listed as an alphanumeric value called Pcode and not the political party.
This coding wasnt intuitive. For example, D was for American Independent Party, but some thought it meant Democratic Party. Another difference was we needed an exact match for Party.
Again, the way to solve this problem was to use the worksheet with thePcodeand translation and have Excel use the VLOOKUP function for theParty name. I could then add a column called Political Party to my original worksheet to show the information from the lookup table.
Download thestarting Excel sample file. The file link is at the bottom of this tutorial.
worksheet. It has voter first and last names, but only a PCODE.
worksheet. It has a listing of party codes and political names. Each of the Party Codes and Names are unique. Youll also note that Column A is in
worksheet that will display the info pulled from the Lookup table on the
worksheet. In my example, I added a column called Political Party in Column D. This is where I will insert the Excel function.
After you clickOK, ExcelsFunction Argumentsdialog appears and allows you to define the four values. Youll see that your starting cell and the formula bar show the beginning part of the function=VLOOKUP(). The Function Arguments dialog adds the needed data elements that will display between ().
For illustration purposes, I have overlaid the Party Codes worksheet on top to show the relationships.
After entering the required arguments, my dialog looks like the example below.
You can see in the red outlined formula bar above, I now have more information based on my entries in theFunction Arguments dialog box.You might also note that when I clicked the Party Codes worksheet to add in my Table_array, Excel prepended the tab name before the cell range. However, I need to go back and enter in my $ signs.
The other item of interest is that when you build these functions, Excel displays the result in theFormula result =text line. This is great feedback which can show if your function is on target. In our example, we can see Excel looked up the Pcode of A and returned the Political Party Democratic.
VLOOKUP is a powerful Excel function that can leverage spreadsheet data from other sources. There are many ways you can benefit from this function. In this example, I used a 1:1 code translation, but you could also use it for group assignments. For example, you could assign state codes to a region such as CT, VT, and MA to a region called New England.
If youre trying to a do a horizontal lookup, youll be happy to learn that Excel has a HLOOKUP function. I havent done a HLOOKUP tutorial yet. If this interests you, let me know.
2019 Productivity Portfolio PO BOX 117361 Burlingame, CA 94011