Lookups: Approximate Search for all Lookups

Quick reference

• 00:04 Okay, in this example we have another invoice.
• 00:07 This time, the item codes, descriptions,
• 00:11 price per unit and quantities have all been entered.
• 00:15 But I have a new feature I'd like to add to this invoice discounts.
• 00:19 I'd like to give my best customers a quantity discount.
• 00:23 This table keeps track of how much of a discount we want to give.
• 00:28 It starts with 0 items which has a 0% discount.
• 00:31 But once you buy at least 10 of an item you get 4% off.
• 00:35 The moment you hit 40 of the same item, you get an 8% discount.
• 00:39 And if your order includes 80 of the same item, you get a 10% discount.
• 00:45 So, in this lesson, we can use a lookup function to compute the discount for
• 00:50 each customer's order.
• 00:53 But for this to work,
• 00:54 the units in the discount table must be listed from smallest to largest.
• 01:00 To find the discount for my specific quantity,
• 01:03 I need a lookup function that would look from the top of the column down.
• 01:08 And if it doesn't find an exact match, like there's no 15 for example,
• 01:12 it needs to give me the next smaller number.
• 01:16 So if this customer wants 15 guitars, Excel will go down the list.
• 01:21 It knows it's sorted from smallest to largest.
• 01:24 So the moment it hits 40, it knows that 15 is not on the list.
• 01:29 There's no exact match, so it will give me the next smaller item.
• 01:34 That will result in a discount for 10 items, which is 4%.
• 01:38 In other words, we're no longer using an exact match here,
• 01:42 now we're using an approximate match.
• 01:46 The first lookup I'll show you is vlookup.
• 01:49 Let's compute the discount amount for the first item here.
• 01:52 The lookup value is the quantity 15.
• 01:55 My table array is the discount table.
• 01:58 Column index number is 2.
• 02:02 And then my range lookup, that's where the difference comes in,
• 02:07 is true for an approximate match or false for an exact match.
• 02:11 I want an approximate match here, so we will say true.
• 02:15 And keep in mind with vlookup,
• 02:17 we don't actually need to say true because approximate match is the default.
• 02:21 We can still type in true there, just so we can see it clearly.
• 02:26 And look at that, I get my discount of 4%.
• 02:29 Once I confirm that's correct, let's edit that formula and
• 02:33 compute the dollar amount of the discount.
• 02:36 That'll be 4% times the price per unit, times the number of units.
• 02:45 So my total discount for this is $532.
• 02:49 Let's look at the next row, the violins with a quantity of 50.
• 02:55 So 50 should get me an 8% discount.
• 02:59 Let's try to solve this with the index match.
• 03:04 Index in my array is my table, which one do I need?
• 03:09 The row depends on what the quantity is.
• 03:12 So that is going to be my match replacement, a lookup value of 50.
• 03:17 So find 50 in that column.
• 03:22 And then the match type, it's not an exact match,
• 03:25 I need the value just less than that.
• 03:28 And it gives me a 1.
• 03:30 The default for match is also 1, an approximate match.
• 03:36 Okay, and I need to return the second column, so type in a 2.
• 03:42 So index of that table, I'm going to look for
• 03:47 the 50 and find the next smaller unit of 40.
• 03:53 Give me the second column, the 8%, and
• 03:57 there I have 8% and once I confirm that's right,
• 04:03 I can say that times 228 times the 50.
• 04:08 And that's my total discount on that line item.
• 04:11 The amount after discount is 10,488.
• 04:15 All right, last one.
• 04:17 Let's do an xlookup for the flute order.
• 04:21 Xlookup, lookup value is 95,
• 04:24 lookup array is only the first column.
• 04:30 Return array is the second column.
• 04:34 If not found, we can ignore, that's optional.
• 04:38 Go right to match mode, exact match is 0.
• 04:41 And for an xlookup, it's different than the other two.
• 04:44 The default here is exact match.
• 04:47 So I have to specify the type of approximate match I want.
• 04:52 I want exact match or next smaller item.
• 04:56 That's the option I'm going to choose.
• 04:58 The value for that argument is -1.
• 05:01 Close the parentheses, hit Enter.
• 05:04 And I get a 10% discount, I can visually confirm that's correct.
• 05:09 So once again, let's edit the formula,
• 05:12 multiply that 10% by the price per unit times the quantity, hit Enter.
• 05:18 And we have our total discount for
• 05:20 the line item which brings the total amount to 17,000.
• 05:24 So, we did a few different approximate matches, but
• 05:27 you can see that each one works perfectly.

Lesson notes are only available for subscribers.