From the course: Excel: Advanced Formulas and Functions
Two-way lookups
- [Narrator] Now it's time to do a two-way lookup. What is that? Let's say that I want to order the Planet7 and I want to order 33 of 'em. I need to be able to do a lookup that will do an intersection showing the Planet7 and the price that I would get for ordering 33. So I should pay 80%. That would be a 20% discount and that is the 0.8 at the intersection of Planet7 and the 20 because the 33 that I want to order it is in between 20 and 50. And at 50 and above, the Planet7 would be a 30% discount. I would pay 0.7%. Okay. Now let's kind of refresh what we know about the XLOOKUP because we have to get the price first. Okay. Here's the price equals XLOOKUP, double click. What do I want to look up? I want to look up curled axle, comma, look up array. Where do I want to look for curled axle? I'm going to highlight here. And because that data is not in a table I'm going to hit F4 for the absolute sale reference. 'Cause when this formula is dragged down, I don't want the formula to move. All right. Comma, Return array. I want to return the price. Here we go. And we need F4 again. The if not found match mode search mode are all optional. In this case, I don't need any of them. I can hit enter. Boom. Now I have all of the prices. Now the juicy part, the two-way lookup. Let's go into the discount. Equals XLOOKUP double click. Look up what? Look up curled axle. Comma, look up array. Look for it over here. In I5 five through I10. And then F4 for the absolute reference. Comma, return array. Now we have to do the second part of this two-way look up. I'm going to go XLOOKUP, double click, look up what? I want to look up the quantity. And because the formula is in the way, I'm going to go to this 70 and then use the up arrow to get to the sale that I want. And you see in the formula, it shows add quantity. That's the column that the formula's going to look at. Comma, Look up array. Where do we want to look for that number? Want to look for it here. And then again, F4 to lock it down. Comma, return array. Now watch this. We have to capture this intersection. Okay? It's got to be as wide as those tops. The 1, 5, 20, 50. Okay? And then going down the side. Okay, we've got the intersection and again, we have to lock it down with F4. Comma, if not found, I'm not worried about that. Comma, match mode. We want the exact match or next smaller item. So if I order 33, I get the discount that goes with 20. If I order 9,000, I get the discount associated with the 50. So I want to put negative one. Okay. Now, the formula has done something funny that I have never seen before but it is still here. It's squeezed itself down into the column for some reason. But I can continue on. I'm going to close parentheses that closes the second XLOOKUP. Now I'm going to hit parentheses again to close the first XLOOKUP and enter. Look at that. Now let's check out what we've done. We ordered one curled axle and there is no discount on that. Okay, we see we order one, we've paid full price: the $10.80. Let's look at the Norton. We ordered 70. 70 Norton's gets the 35% discount, which is the 0.65. Now let's go ahead, calculate the cost and then we'll be done. Okay. Equals the price times the quantity, times the discount, and enter. Double click this to widen it out. We can see. Check that out. We have to pay $439.08 for the order of 70 Nortons. And one thing, as a bonus, I'll show you this. I'm go to table design while the cursor is inside the table. And I'm going to go to total row. Look at that. And that is the total of our order: 610.58 Euros. And there is an example of A two way lookup nesting an XLOOKUP inside an XLOOKUP.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.