Vlookup

The Vlookup function is useful for looking up values in tables. The Vlookup function looks down the vertical column of the left side of the table until the appropriate comparison value from the spreadsheet is found. When multiple answers could be the result of a formula, these answers can be placed in a table and the Vlookup function used instead of the If function.

The format of the Vlookup function is:

=Vlookup(LookupValue,Table-Range,Column-Position)

LookupValue is text, a value, or most often a cell reference of the item you are looking for and should be in the first column of the Lookup table.

Table-Range is the reference to the cells containing the table in which the search is to be made. This can be a range reference or a range name. If the Vlookup function is going to be copied, be sure you make the Table-Range an absolute reference.

Column-Position is the number of the column in the table which contains the result you are searching for. The far left column in the table has a position of 1; the second column has the number 2, the third column is numbered 3, etc.

As an example, let's use the Commission worksheet. Suppose there are five different commission rates based on different sales amounts. Look at the assumptions in rows 14 through 18. Now look at the Lookup Table I created for these assumptions in columns F and G. Notice the Sales are in the left column because we will be looking up sales to find the commission rate. Notice that the lowest value for each range is enetered You don't have to type 0-3,000 or 8,000 -9,000, only the lowest value in the range. Notice also that the Sales are listed in ascending order. Values in the leftmost column MUST be listed in ascending order. And last, the result that you are looking to find is in the second column of the table.

lookup.gif (7749 bytes)

The formula that will be written in cell C5 is =Vlookup(B5,$F$3:$G$7,2)*B5. The LookupValue is the amount of sales in cell B5. Next, the Table-Range F3:G7 is entered. Because this formula will be copied down column C, the table-range must be made absolute. Last, the Column-Position is entered. The value 2 is used to represent the second column in the table. Then the result of the Vlookup function is multiplied by the sales in cell B5 to obtain the commission amount.

You try it. Download the commission file and enter the formula in cell C5. Your results should look like this:

com_lookup_answer.gif (2985 bytes)

Here's another file for practice. Download Vlookup. The instructions are on the worksheet. Your results should look like this:

lookup_anwer.gif (4965 bytes)

Try one more example. This time you will have to create the table also. Please do this...it's good practice!! Download this Grades file and follow the instructions on the worksheet. Your answers should look like this:

GRADE_LOOKUP_ANSWER.gif (2462 bytes)

If you are having trouble with this, please e-mail me. If you've completed these practice exercises with no problem, you're ready to go on to your assignments.

Assignments:
Complete the following three assignments. When you save each file, type your first name at the beginning of the file name. After you complete the assignments, send them to me in one e-mail message with Tutorial 8 Worksheets as the subject of the message. You will have to attach each file to the message separately.

1. Case Problem 1 on pages 8.49 and 8.50.
2. Case Problem 2 on page 8.50.
3. Cortez Company Budget. Click here for instructions. You may print the instructions if you wish.