Analysis Paralysis

The VLOOKUP function is one of the most powerful functions within Microsoft Excel for data analysis and reporting; unfortunately, it tends to be one of the lesser-known and under-utilized functions. This session will provide some basics on the use of the VLOOKUP function; a later session will provide a more “real-world” example.

Function Purpose
The VLOOKUP function is useful when an analyst has a value in one table that they need to pull into another table. For example, consider that you have a table of customer transactions, but, for indexing purposes, only the Customer ID is shown. If you have an index of Customer Names by Customer ID, you may utilize VLOOKUP to add the Customer Name to the transaction table. Although this is more of a database function (a Microsoft Access query with a left join on Customer ID would run faster), there are times when a “quick and dirty” analysis is all that is required, making the added time of creating a database unnecessary.

Components of VLOOKUP
The VLOOKUP function contains requires four variables as shown:

=VLOOKUP(<LOOKUP VALUE>,<LOOKUP TABLE> ,<COLUMN REFERENCE> ,<ABSOLUTE LOOKUP FLAG> )

• LOOKUP VALUE is the value to be “looked up” from the alternate table; in the example above, this would be the Customer ID within the transaction table.
• LOOKUP TABLE is the table from which a value is to be pulled; in the example above, this would be the customer index table, which would contain Customer ID, Customer Name, etc.
• COLUMN REFERENCE is the number identifying the column within the LOOKUP TABLE from which a value is to be returned. If the Customer Index Table in our example contains Customer ID, Customer Name, etc, then the COLUMN REFERENCE would be 2.
• ABSOLUTE LOOKUP FLAG is a Boolean (TRUE/FALSE) field that specifies whether or not an exact match is required; in general, it is recommended that an analyst always use FALSE. If the flag is set to FALSE, VLOOKUP will return an error if an exact match is not found within the index table; if the flag is set to TRUE, VLOOKUP will return the closest match.

VLOOKUP Example
Reference File: http://www.box.net/shared/1hx8gxqlq2

The reference file provides a very basic example utilizing the VLOOKUP function. Within this example, A2:B8 represents the index table; D2:E8 provides an example of the VLOOKUP function where the ABSOLUTE LOOKUP FLAG is set to TRUE; G2:H8 provides an example of the VLOOKUP function where the ABSOLUTE LOOKUP FLAG is set to FALSE; J2:K8 provides the same example as G2:H8 with the ABSOLUTE LOOKUP FLAG set to FALSE, although an error handler has been added. Note within the Index Table (A2:B8), the index “3″ has been left out; this has been done intentionally to show the differences between utilizing TRUE and FALSE with the ABSOLUTE LOOKUP FLAG.

Example #1 – VLOOKUP with TRUE
The formula utilized in E4 is =VLOOKUP(D4,$A$4:$B$8,2,TRUE), which means that the function will pull the value within the second column of A4:B8, where the first column matches D4. Because the ABSOLUTE LOOKUP FLAG has been set to TRUE, an absolute match is not required, and the function will return the closes value. For Index “1″, the function returns “This is #1″ as shown in the Index Data. However, for Index “3″, the function is returning “This is #2″, which is obviously incorrect. Because the ABSOLUTE LOOKUP FLAG has been set to TRUE, when VLOOKUP cannot find an exact match, it will assume the data is sorted and return the value prior to the location where the actual value is located. In this example, “3″ is not within the Index Data, so VLOOKUP returns data for “2″ (the closest available match).

Example #2 – VLOOKUP with FALSE
The formula utilized in H4 is =VLOOKUP(G4,$A$4:$B$8,2,FALSE), which means that the function will pull the value within the second column of A4:B8, where the first column absolutely matches G4. Notice that, in this example, H6 (the lookup of index “3″) returns “#N/A”, a Microsoft Excel error flag. This is because “3″ does not exist within Index Data and the VLOOKUP function has been told to only return absolute cell matches.

Example #3 – VLOOKUP with FALSE (Error Handler)
The IF statement and use of the Error Handler (ISERROR) will be discussed at a later date. However, this example does show how an error handler may be utilized in conjunction with the VLOOKUP function to control what is returned when absolute matches do not occur. In this example, “#BLANK#” is returned for value “3″. If a customer transaction table was being utilized (the example discussed in the beginning of this session), the return value may be “Unknown Customer” or some other value. This use of the error handler may allow for better formatted reports, but also allows for the use of conditional formatting to flag potential problems within index tables.

Formula in K4: =IF(ISERROR(VLOOKUP(J4,$A$4:$B$8,2,FALSE)),”#BLANK#”,VLOOKUP(J4,$A$4:$B$8,2,FALSE))

Keep watch for the more advanced session on VLOOKUP, where VLOOKUP is applied in a more “real-world” example: employee pay.

For more information on Microsoft functions, always review the Microsoft Support site. For VLOOKUP and HLOOKUP functions, information may be found in the article, How to Use VLOOKUP or HLOOKUP to find an exact match.