From the course: Excel: Advanced Formulas and Functions

Two-way lookups - Microsoft Excel Tutorial

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial Buy for my team

Two-way lookups

“

- [Instructor] 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 them. 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 rephrase 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 axel, comma, look up array. Where do I want to look for curled axel? I'm going to highlight here. And because that data is not in the table, I'm going to hit F4 for the absolute cell 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 axel, comma, look up array. Look for it over here in I5 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 cell that I want. And you see in the formula, it shows at 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'm going 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 axel, and there is no discount on that. Okay, we see. We order one. We've paid full price, the $10.80 cents. Let's look at the Norden. We ordered 70. 70 Nordens 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 and 8 cents for the order of 70 Nordens. 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 roll. 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.

Contents