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%).

• How to Spot Fake Coupons and Ways of Getting BIG Discounts Everyone's always looking for a good deal especially with the cost of food and everyday items going up because of...
• Save Time, Money and Space in Over 80 Ways If you're looking for handy gadgets, tools and various items that can save you time, money or space (or all...
• Money Saving Tips: 122 Ways To Trim Your Budget A while back All You Magazine offered to give away a few subscriptions to Frugal Dad readers.  The rules for...
• Depreciating assets as works of art Some items depreciate really quickly after they're bought. The "class life" of an item gives an estimate of how long...
• Scouting for Ski Hills: Tips and Tricks If you're new to the sport of skiing, it goes without saying that you'll want to find the right ski...