1.

To begin, start Excel and open the file named Practice 10 in your Excel 2003 Practice folder. You can use File->Open, or click the file open button on the standard tool bar.

Your goal in this exercise is to construct a VLOOKUP function, such that the ticket price will be automatically entered into column C when the name of a country is entered into column A.

The ticket price can then be multiplied with the #of tickets to get a total price for column D.

2.

To begin, work out the details of the VLOOKUP function. The column that we want the ticket prices to appear in is column C, so this is where we will place the function. The values used to match the lookup table values are in column A, and the ticket prices that we are retrieving are in the 2^nd column of the lookup table. The lookup table has the defined name Price, so your VLOOKUP function should look like VLOOKUP(A2,PRICE,2), and be placed in cell C2

If you activate cell C2 and enter =VLOOKUP(A2,PRICE,2) into the formula bar, your worksheet should look like this.

Notice that C2 has a #N/A error because there is no value entered into cell A2 for the function to use as a match. If you activate cell A2 and type England, you will see the ticket price entered automatically in cell C2.

3.

The next step activate cell D2, and enter =B2*C2 in the formula bar. This will calculate the Total price based on the price per ticket and the #of tickets. Use Your mouse to AutoFill cell D2 down nine cells, and cell C2 down nine cells. Because you used relative references, the formula in D2 and the VLOOKUP function in C2 will adjust accordingly for the cells they are filled into.

Your worksheet should look like the one shown above. The #N/A errors are visible because there are no countries entered in column A for those rows. When you enter a country in column A, the VLOOKUP function will lookup the corresponding ticket price from the Price Table. When you enter a #of tickets in column B, the total price will be calculated for the corresponding cell in column D.

4.

Type Japan, Hungary, Brazil, and Italy in cells A3:A6 respectively. Then enter 2, 2, 3, 1, and 1 in cells B2:B6 respectively. Your worksheet should now look like this.

You omitted the last argument in the VLOOKUP function when you entered it (the function is visible in the formula bar). Because you left this argument out, the default argument of true is being used. This is why VLOOKUP found a price for Brazil even though the country is spelled incorrectly (Brasil) in the lookup table.

(True finds the most likely match; False will find only an exact match).

You can easily have lookup tables that are much larger than the one used in this exercise. If your lookup table has many columns, you just have to make sure you specify the correct column number in your function arguments, so the appropriate data will be retrieved.

5.

In this exercise, the lookup table and the finale data table are on the same worksheet. The lookup table can just as easily be located on an entirely different worksheet if that is more convenient.

Save your work and Exit Excel.