The VLOOKUP Function in Microsoft Excel

The VLOOKUP function in Microsoft Excel allows you to look for a specific value in a table and to return some related information.  There are two forms of the VLOOKUP function: one allows you to look for a specific item or code and find information about that exact item; the other allows you to look for a value and find out which band or category it belongs to.

With the A-Level and GCSE examination results released in the UK over the past two weeks, this seemed like the perfect time to show both types of VLOOKUP functions using the same data.

The Basics of the VLOOKUP Function

The basic syntax of the VLOOKUP function is shown below, followed by an explanation of what the arguments mean.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

There are four arguments in total, but only the first three are required.  The arguments are:

• lookup_value - this is the value we are going to look for in our lookup table.  For our examples we might want to look up our exam score in order to find out what grade we'll get, or we might want to look for a grade to find out what score we need to achieve that grade.
• table_array - this is the set of cells which contains all of the possible grades and scores, along with any other information we might want to return.
• col_index_num - this is the number of the column in our table which contains the answer we want to get.
• [range_lookup] - this argument lets us tell Excel whether we are looking for an exact match or not.

Using VLOOKUP to Find an Exact Match

For our first example we're going to assume that we want to find what score we need to get in order to achieve a certain grade.  This means we're going to give Excel a specific grade, such as B, and ask to see the score range for that grade.

The first thing we need to do is create the lookup table.  The diagram below shows what it will look like (the information is based on data taken from the AQA GCE Mathematics Unit D02 paper).

Our table consists of two columns, the first contains the values we are going to be looking up, and the second contains the answers we want to get. The order of the rows in this table doesn't matter as we'll be looking for an exact match.  We've also given our table a range name to make our formula easier to create.

In another part of the spreadsheet we can create our lookup system, as shown below:

We've created a cell for a user to type in the grade they want to get and given this a range name. The next job is to create the function to return the right answer.

The last step is to create the formula that will return the score needed to achieve the grade we're looking for.  To do this, we'll enter the formula shown below into cell B2:

This formula says to look for the grade we're looking for (GradeWanted) in the table of grades (GradeTable), give us the answer from the second column of that table (2), and make sure you're looking for an exact match (FALSE).

We should now be able to type any grade into cell B1 and see the required scores appear in cell B2.

The finished lookup system.

Using VLOOKUP to Categorise Values

The alternative way to use our VLOOKUP function would be to enter a numerical score and ask Excel to tell us what grade we'll get.  This is slightly more difficult than the previous example because multiple scores can return the same grade and we definitely don't want to have to create a huge lookup table to handle all the possibilities.  Instead, we'll tell our VLOOKUP function to behave differently when looking for answers.

To begin with we need to create the lookup table.  We have to be a little more careful when doing this for a lookup that doesn't look for an exact match.  The main things that we need to do are:

• Start the first row of the table with the lowest value we might ever want to look up.  Theoretically, the lowest exam score we could get would be 0, so that's what we'll start with.
• Work in ascending order up the list of scores.
• Don't enter every possible score on a separate row, just the scores at which the grade will change.

Following these rules, our table should look like this:

This type of table allows us to search for any score from as low as 0 and for Excel to find a match for us.

We can now create a similar lookup system to earlier, except this time we'll be typing in a score and getting Excel to tell us what grade we've achieved.

The final step is to create the VLOOKUP function return the right answer.

The key to creating this type of VLOOOKUP is in the optional fourth argument.  In the previous example we used FALSE to tell Excel that we were not looking for an approximate match.  In this example we need to do the opposite - we can either use the value TRUE, or miss out this argument altogether as shown below.

Here we've omitted the fourth argument altogether, although we could have used the value TRUE instead.

The function will now tell us what grade we achieved based on our score.

The finished VLOOKUP function.

Comments on this blog

This blog has 4 comments: