Excel matrix: YOUR OBJECTIVE
You want to calculate commissions due your company’s sales staff. There are different commission rates for each product group. You have an Excel worksheet where a salesperson’s sales of various of products have been entered. To complete the table for the salesperson, you want to extract the relevant commission rate from a commission rates table, also located on the worksheet. To fill in the commission rates for the salesperson, you would like to use a function that can search the product groups in the commission rates table, read out the associated commission rates, and insert them individually into the salesperson’s sales table.
In this learning module “Excel matrix”, you will be shown in detail how to use the VLOOKUP function to complete this task. The very similar HLOOKUP function, used less frequently in everyday practice, is also discussed.
Excel matrix: HOW DOES IT WORK?
The next illustration shows the table in which the commission rates have to be filled in at the left. Here too, the commissions themselves will later be calculated. At the right, you see the table array that contains your company’s product groups and their associated commission rates. You want to transfer the proper commission rate for each product group from the table array into column C of the salesperson’s table. The VLOOKUP function requires that search criteria be arranged vertically in the first column of an array (table) with the values of interest in another column of that same array. The commission rates schedule in this example is arrranged accordingly and can therefore be used with the VLOOKUP function.
The search criterion in our example is the name of the product group. The VLOOKUP function works like this:
- The VLOOKUP function reads a search criterion from the product group’s column (column A, in example). Then it finds the matching criterion in the first (i.e., leftmost) column of the specified table array. It is important to note that the VLOOKUP function searches only the first column of the table array.
- The function then looks to the right in the same row that the criterion was found. It then reads a value from a column that you specify in one of the function’s arguments and returns exactly that value. To identify the column from which the value is to be read, you will specify the relative column number. Since the search criteria are always in column 1 of the table array, the next column to its right is column 2, a column beyond that would be column 3, and so on. In our example, the table array consists of two columns. The values to be returned, the commission rates, are located in column 2.
To begin, click in the cell that is to contain the commission rate for the salesperson’s first product group, in our example, C4. Then start the Function Wizard by clicking the button just to the left of the formula bar. Now find and select the VLOOKUP function in the Lookup & Reference category. Then click OK. The Function Arguments dialog box will appear (as shown below):
The VLOOKUP function takes four arguments:
The Lookup_value for which the commission rate must be found, for instance: “Meat products”.
The Table_array is the cell range with the company’s standard commission rates for the various product groups.
The Col_index_num is the number of the column to the right of the first column in the table array. Column 1 always contains the search criteria, the next column to its right is column 2. In the current example, column 2 contains the standard commission rate that we wish to locate.
The Range_lookup is optional. If you leave it empty or type in TRUE or some number, the values in the 1st column of Table_array must be in ascending order; otherwise, the function may return an incorrect value; the function will now return a value for an exact match or an inexact match. If FALSE or 0 (zero) is entered in Range_lookup, the function returns a value only if there is an exact match; in this case, the Table_array need not be sorted; the first value for which there is an exact match will be returned; if no exact match is found, #N/A will be returned.
Excel matrix: CAUTION
If you require an exact match, don’t forget to enter 0 (zero) for Range_lookup. Otherwise the results will probably be wrong.
Excel matrix: CAUTION
If an exact search does not find a corresponding value in the array, the function will return the error value “#N/A”.
The first letter of the functions VLOOKUP and HLOOKUP differ only in the leading letter of the function’s name:
V: The function searches vertically in the first column.
H: The function searches horizontally in the first row.
One can think of the HLOOKUP function as a VLOOKUP function turned 90 degrees. It searches for the Lookup_value in the first row of the array (instead of the first column) and returns a value taken from the corresponding cell in one of the the array’s rows as indicated by the Row_index_num argument.
In practice, one encounters the VLOOKUP function more often for the simple reason that its vertical orientation is how most Excel tables are typically constructed.
Excel matrix: CAUTION
This example given in this learning module is not terribly realistic. In practice, such a commission rates array, which is valid for all sales persons, would be found in some other spread sheet, either in the same workbook or in a separate workbook. When using the VLOOKUP function, you can still easily access a table array that is located outside the current spreadsheet. All you have to do is open the workbook that contains the array when you are entering the arguments for the VLOOKUP function. Later, when you are calculating the commissions (for example) the workbook mentioned in the argument will automatically be accessed without your having to open the relevant workbook yourself.