The easiest way to begin is to just have you create your own VLOOKUP.
Download and save the file below to follow along with the exercises.
Watch this brief video clip showing you how to do a VLOOKUP exact match and/ or read the section below.
To follow along go to 2_0
YouTube
If YouTube doesn’t work, click here
The VLOOKUP command allows you to LOOKUP information and based on what it finds, bring some other information back.
VLOOKUP stands for Vertical Lookup and will search down a column and then extract information from another column. You can also use the HLOOKUP (Horizontal Lookup) command which searches along a rows (covered later).
For now we are going to focus on finding EXACT matches.
In this example we want to find a certain student and pull through their result and symbol. The yellow cells should bring through the results relating to student number A111.
The syntax for VLOOKUP is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Based on a step by step process we need to tell Excel the following
What must you look up and where will you find it
The first part is telling Excel what it must look up and where it must go to find it. In the example below we are in the first yellow cell and have told Excel that the Lookup Value is in cell A14.
Now that Excel knows that it is looking for the contents of cell A14 ( student A111 in this case) it need to know where to go and look. To a human this is obvious, but Excel has thousands of columns and rows where it can look so we need to specify where. In this case it is the table shown in cells A4 to C8.
Some very important points:
- The first column in the table array (A4 to A8) MUST contain the data you are looking for in cell A14. Excel assumes that what it is looking for will ALWAYS be in the first column of the table
- The table array will almost always be absolute (i.e. $ signs to make Excel look in the same place each time).
Once you have found it, what must you bring back
Once Excel has found the student you are looking for, it needs to know what to bring back for you. In this case we want to see the students percentage.
The way to tell Excel what to bring back is by specifying which column OF THE TABLE ARRAY you are interested in. As shown below, the table array specified in the Formula (A4 to C8) contains 3 columns. We are interested in the Result which is in the second column so we type a 2 in the Column Index Number.
Finally we type FALSE into the Range Lookup. This will be explained fully later but for now know that it is telling Excel to find an exact match i.e. it must either find the exact student number A111 or else give an error message.
The answer
When you click OK you get an answer of 34%. Excel started at the top at student number A100 and went down cell by cell until is found student A111. It then went to the right to the second column and extracted the result.
What happens if we copy the formula down to the cell below? Why the error message as shown below? Try and find student A150 in the list.
If Excel is doing an exact match and cannot find the student number it returns a #N/A message. This means not applicable and is the correct outcome as there is no student A150. This error message is very useful and we will use it in many of the upcoming VLOOKUP exercises.
Now lets do some exercises on VLOOKUP with an Exact match. Each of this exercises must be done as they teach a little more about each of the components.