Ms Office Tune Up

When one business supplies a product that another business intends to sell, the purchas price is never the same as the list price of the product. The retail business has to mak money, of course, and for the most part it does that by buying products from the supplie at a cost below the list price and then selling to the likes of you and me at or near the lis price. The gross profit is the difference between what it paid to the supplier and what i received from its customers.

The supplier’s purchase price is usually a percentage off the list price, and this percentag is called the discount. For example, most publishers sell books to bookstores at a discoun 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 copie might result in a 20 percent discount, ordering 6–25 copies might result in a 40 percen discount, and so on.

If you’re setting up a worksheet model for such transactions (either as a supplier or as retailer), you need to handle the varying discount. In Excel, you do that by using a looku function that takes a lookup value (such as the number of copies purchased) and checks 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 lookin in the first column of a table for the value you specify. (The V in VLOOKUP() stands fo 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 o columns (which you specify) and returns whatever value it finds there. Here’s the full synta 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.

Note: If range_lookup is TRUE or omitted, you must sort the values in the first column in ascending order.

A worksheet that uses VLOOKUP() to look up a customer’s discount in a discount schedule.

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 larges 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 rang $H$5:$I$11 is the discount schedule table. VLOOKUP() searches down the first colum (H5:H11) for the largest value that is less than or equal to 20. The first such cell is H (because the value in H7—24—is larger than 20). VLOOKUP() therefore moves to the secon column (because we specified col_num to be 2) of the table (cell I6) and grabs the valu there (40%).

Popularity: 2% [?]