Microsoft Excel provides efficient functions that allow one to search for and lookup data. Of the lookup functions – VLOOKUP, INDEX and MATCH and the database function DGET are the most useful and most widely utilized.
The syntax for the VLOOKUP Function is the following:
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
where lookup_value is the value you want the function to use in order to find the matching output value, table_array is the range that contains all the columns of interest, the col_index_num specifies the number of the column containing the output value that we want to lookup, [range_lookup] is a true or false value – where TRUE denotes an approximate match and FALSE is an EXACT match.
The syntax for the INDEX and MATCH combined formula for lookups is the following:
=INDEX (range, MATCH (lookup_value, lookup_range, match_type))
where range is the destination range that contains the value one wants to lookup, and the subsequent match function in this combination formula tells the index function the position of that value in order to retrieve it.
The syntax for the DGET function is the following:
=DGET (database, field, criteria)
where database is the entire data set and the first row contains the labels or column headings, field indicates the column that is used in the function, and criteria is the group of cells that contains the conditions that one wants to specify.
Each lookup function has its advantages and associated disadvantages. So, let’s get started with a simple example to illustrate those advantages and disadvantages.
Table of Contents
The United States Tennis Association has a rankings system, called the National Tennis Ratings Program (NTRP) which designates NTRP ratings based on the tennis player’s skill level. For the Adult NTRP Ratings, skill level is designated by looking at the results of the player at hand, in USTA League and/or USTA NTRP adult tournaments. The scale starts at 2 and increases in increments of 0.5 all the way up to 7.
A hypothetical tennis coach is interested in retrieving the USTA rankings of certain players at his clinics, in order to pitch equally ranked players against each other in future matches. The source data is shown below:
1) For our example, we want to enter a player’s name and then find their NTRP rating, using the VLOOKUP Function.
2) In Cell I6, we enter the value Ian Roberts, which is the name of the player whose NTRP rating we want to find.
3) In Cell I7, we enter the following formula:
=VLOOKUP (I6, $B$6: $E$105, 4, FALSE)
4) Upon pressing CTRL-ENTER, VLOOKUP retrieves the value of 4.0 which is the player Ian Roberts’s NTRP rating.
The main advantage of the VLOOKUP Function is that it is easy to learn, additionally many Excel users are familiar with the function. Thus, when sharing sheets or when collaborating with co-workers, everybody is on the same page when it comes to understanding and using the formulas in the workbook.
The disadvantage of VLOOKUP is that it can only do lookups from left to right. In other words, one looks up a value in column, and the match must be in column to a right of that value or else VLOOKUP returns an error message.
Additionally, VLOOKUP can be prone to error in larger spreadsheets. So, in these cases its best to try other lookup functions such as the INDEX and MATCH formula combined and the DGET Function.
INDEX and MATCH
1) For our next example, we still want to enter a player’s name and then find their NTRP rating but this time we are going to use INDEX and MATCH, in order to retrieve the rating of the player at hand.
2) In Cell I6, again we enter the value Ian Roberts, which is the name of the player whose NTRP rating we want to find, as in the example above.
3) In Cell I7, we enter the following formula:
=INDEX ($E$6: $E$105, MATCH (I6, $B$6: $B$105, 0))
4) Upon pressing CTRL-ENTER, the INDEX and MATCH formula retrieves the value of 4.0 which is the player Ian Roberts’s NTRP rating.
The main advantage of using the INDEX and MATCH formula is that it is more versatile than VLOOKUP, and can do left to right and right to left lookups. It is also faster than VLOOKUP in spreadsheets that contain large amounts of data. The main disadvantage is that it is not as straight forward to learn as VLOOKUP and slightly less popular.
Now let’s see how to do the same with the database DGET Function.
1) For the DGET example, we still want to enter a player’s name and then find their rating, however when using the DGET function we have to setup our worksheet a little bit differently. We can use the DGET function to retrieve a single value based on input criteria. So, in this case our input criteria is going to be Ian Roberts, and our field we want to retrieve the matching NTRP Rating from, is NTRP rating.
2) We setup our criteria range in A2:E3 as shown, the field names in the criteria must match the column names of the database range. We enter the value Ian Roberts in Cell B3 as shown below.
3) Now that we have setup our criteria range, in Cell I7 we type the following formula:
=DGET ($A$6: $E$106, “NTRP Rating”, $A$2: $E$3)
4) Upon pressing CTRL-ENTER, DGET retrieves the value of 4.0 which is the player Ian Roberts’s NTRP rating.
The main advantage of using the DGET function is that can handle multiple criteria, and larger data sets with ease. The main disadvantage however, is that it is not as widely utilized as VLOOKUP and INDEX and MATCH, so other users of the spreadsheet would be unfamiliar with it. Also, the setup required initially can prove counter-intuitive to some users, since it has a slight SQL query feel to it.
Excel has many useful lookup functions, that can assist with retrieving values under specified conditions.
Please feel free to comment and tell us which lookup function you use most often in your spreadsheets.