When one business supplies a product that another business intends to sell, the purchase price is never the same as the list price of the product. The retail business has to make money, of course, and for the most part it does that by buying products from the supplier at a cost below the list price and then selling to the likes of you and me at or near the list price.

The gross profit is the difference between what it paid to the supplier and what it received from its customers. The supplier’s purchase price is usually a percentage off the list price, and this percentage is called the discount. For example, most publishers sell books to bookstores at a discount off the suggested list prices. However, the discount isn’t usually a fixed percentage. Rather, it depends (usually) on the quantity of books purchased. For example, ordering 1–5 copies might result in a 20 percent discount, ordering 6–25 copies might result in a 40 percent discount, and so on.

If you’re setting up a worksheet model for such transactions (either as a supplier or as a retailer), you need to handle the varying discount. In Excel, you do that by using a lookup function that takes a lookup value (such as the number of copies purchased) and checks a lookup table (such as a table of discounts) for the corresponding item. Most people use the VLOOKUP() function for this type of work. VLOOKUP() operates by looking in the first column of a table for the value you specify. (The V in VLOOKUP() stands for vertical.

A similar function is HLOOKUP(), which looks in the first row of a table you specify; the H in HLOOKUP() stands for horizontal.) It then looks across the appropriate number of columns (which you specify) and returns whatever value it finds there. Here’s the full syntax for VLOOKUP():

VLOOKUP(lookup_value, table_array, col_index_num[, range_lookup])

lookup_value
This is the value you want to find in the first column of
table_array. You can enter a number, a string, or a reference.

table_array
This is the table to use for the lookup. You can use a range reference
or a name.

col_index_num
If VLOOKUP() finds a match, col_index_num is the column number in the table that contains the data you want returned (the first column— that is, the lookup column—is 1, the second column is 2, and so on).

range_lookup
This is a Boolean value that determines how Excel searches for lookup_value in the first column: TRUE—VLOOKUP() searches for the first exact match for
lookup_value. If no exact match is found, the function looks for the largest value that is less than lookup_value (this is the default). FALSE—VLOOKUP() searches only for the first exact match for lookup_value.

Figure 5.3 shows a worksheet that uses VLOOKUP() to determine the discount a customer gets on an order that is based on the number of units purchased.

For example, cell D4 uses the following formula:
=VLOOKUP(A4, $H$5:$I$11, 2)

The range_lookup argument is omitted, which means VLOOKUP() searches for the largest value that is less than or equal to the lookup value; in this case, this is the value in cell A4.

Cell A4 contains the number of units purchased (20, in this case), and the range $H$5:$I$11 is the discount schedule table. VLOOKUP() searches down the first column (H5:H11) for the largest value that is less than or equal to 20. The first such cell is H6 (because the value in H7—24—is larger than 20). VLOOKUP() therefore moves to the second column (because we specified col_num to be 2) of the table (cell I6) and grabs the value there (40%).

• California (EYEWITNESS TRAVEL GUIDE) User Reviews Send this to a friend California (EYEWITNESS TRAVEL GUIDE) Manufacturer: DK Travel Customer Rating: List Price: $30.00 Sale...
• Investing in Uranium: The 5 Largest, Most-Traded Uranium Stocks in Canada If the oil sands wreak environmental controversy in Canada's west, uranium deposits and uranium production can fare no better.  Regardless,...
• Tips for Snowboarding pt 4 For a lot of people, however, it is their very first time buying a brand new snowboard, and so they...
• Tennis Glossary pt5 Court - The length of the tennis court is 78 feet and the width of the tennis court is 27...
• Internet Marketing Tips - The Art Of Creating Targeted Traffic Establishing a marketing campaign online for your business can give you pleasantly surprising results if you know what you're doing....