Approximate match with XLOOKUP

So, so far what we have looked at is the exact match, but what if you want to look up an approximate match? Well that’s what we’re going to look at now. So the exact match was the default and we seen if we go back in here and I’m going to press f2 to go back into edit mode. So we see in here, her exact match of 0 that we don’t need to select zero if we want an exact match we can leave a blank because it is the default setting but if you want to find a match exact or above or exact or below these are additional options that we had that we didn’t have in VLOOKUP.

So let’s take a look at an example. We have some units and then we have some discount. So we have a discount table and basically if we sell from zero to a hundred units, there’s a 7% discount. If we sell between 100 and 250, there is a 10% discount if we saw between 250 and 600. It’s 15 percent and if we saw between 600 and a thousand units, they get a 20% discount. Now note from that table that it is not sorted. If you’re doing an approximate match with the older vlookup functions, you would have to have this table sorted but with xlookup you don’t have to have it sorted and that is a really really cool feature.

So let’s go ahead and look at this with a vlookup. So the lookup value is let’s say we had 550 units ordered. We want to look it up in our table. We want to return the second column Now, we can take an approximate match or we can actually leave that blank because approximate match will vlookup is the default setting now that’s after returning 7% because what happens is xlookup goes down through the table, assuming that you have it sorted correctly and it says well, Is 100 greater or less than 550? I mean it says it’s less than, so it jumps to the next number and then it says well, Is 600 greater than or less than 550?

and if it’s greater than it’ll revert back to the previous number and then return the corresponding value for that. Now let’s do this within XLOOKUP. First of all, we will look for the next smallest value. So we’re going to take our 550. We are going to look it up in our lookup_array in our turn array. We’re going to select our return_array 0 if value is not found. Now into the match mode. We want to find the next smallest. So we seen when we looked at the exit or the vlookup there went to the next biggest and then when route back to the next smallest and took the corresponding value from that.

Well, this is the same when you select next smallest, is that it will go back to the next smallest, but the difference is the table does not have to be sorted. So now it’s saying that the, is a greater than a hundred said yes. Is it greater than 250 it didn’t you come down to this 600 and then go back it went down to this 250 is a greater than this? Yes, it’s greater. But we want the next smallest. So we will take this one. We can also do an xlookup then with the next largest.

So let’s say we wanted to have the next largest percent which would return the 15% but we can select the value we want to look up. We can then select the lookup_array, the return_array. I’m going to put in 0 if value is not found and then I’m going to select the next largest. So although it is an approximate match. It nows move to the next largest and we see there we have quickly got 15%. So now we have looked at the xlookup and we’ve looked at two of the optional features. We’ve looked at if not found and match mode.

There also is a search mode but that is outside the scope of this lesson In the resources to this lesson, you will find this workbook. So you can practice along what you’ve been shown here, and this is a great function to master. Thank you very much for watching this lesson. My name Is Paula Guilfoyle and it’s been a pleasure. [END]