A question from my Daytona Beach seminar last week:
We know XLOOKUP can find the number just larger or just smaller than a number. But does this also work with Text? How about with Symbols?
In this video, Bill Jelen shows how the approximate match works with XLOOKUP. Unlike MATCH or VLOOKUP, the table does not have to be sorted. You can find a value just smaller or just larger than the search value.
This also works with Text, although it is case insensitive.
And, it works with symbols, but in an unexpected sequence.
We know XLOOKUP can find the number just larger or just smaller than a number. But does this also work with Text? How about with Symbols?
In this video, Bill Jelen shows how the approximate match works with XLOOKUP. Unlike MATCH or VLOOKUP, the table does not have to be sorted. You can find a value just smaller or just larger than the search value.
This also works with Text, although it is case insensitive.
And, it works with symbols, but in an unexpected sequence.
Transcript of the video:
I love learning new things. Does the approximate match in XLOOKUP work with letters?
How about with symbols?
All right, so yesterday I had a webinar on XLOOKUP and covered this XLOOKUP approximate match.
It's kind of like the VLOOKUP where you would say comma true at the end.
Or the match where you could say one or negative one at the end.
It can look for the exact match or just smaller, exact match are just larger.
And unlike VLOOKUP up and match the items you're looking up don't have to be sorted.
All right, so I have XLOOKUP here that's looking for 25 in this range and returning the rate from the adjacent range, and out here I'm looking for an exact match for the match mode.
And of course I can't find 25 because it's not in the list.
If I ask for the value that is just lower though, so that would be a negative one.
And by the way that's reverse from match. Match would have had a one there for just lower.
This makes a lot more sense than XLOOKUP.
Excel correctly finds the four as the age just lower than 25, and the eight as the age just greater than 30.
All right, so both of those are working great, but then the question that came up is does it work with text?
And I had never heard Joe on the Excel team talk about this, but let's just do a quick little test here.
So I don't have all the letters but I have various letters, and we're trying to see what the smaller is. So here I put in Kelly.
Kelly.
So K alphabetically comes before Kelly, and so the previous value is 700, that works great. And then what comes after that?
That would be the M, so that works great.
Here is Bill returning the A for just lower, or the C for just higher. Those seem to work now.
Now it shouldn't have to be sorted, so let's sort by column E, and it all continues to work.
And then I started to wonder okay, is it going to treat a capital K different than a lowercase K?
So over here I have the ASCII characters from 40 on down, and I carefully built my table in ASCII sequence.
So capital A comes along before so A capital J, in ASCII code comes long before a lowercase B.
And I was surprised that whether you look for capital Charlie or lowercase charlie it's treating both of those as just a C, so it's case-insensitive.
But then this one kind of threw me for a loop, let me just save you three hours of your life.
It goes zero to nine and then A to Z, upper or lowercase, and then these punctuation characters in this order.
These are the ASCII codes over here, it makes absolutely no sense, have no idea why.
Talked to a few people on the Excel team, they have no idea why. So that's what it it.
All right.
So XLOOKUP, approximate match does work with text and symbols, there is some logic built in there.
I want to thank you for stopping by, we'll see you next time for another net cast from MrExcel.
If you like this video please visit tl.page/mrexcel.
Scroll down, you'll find my group lessons and plenty more videos. Thanks for watching.
How about with symbols?
All right, so yesterday I had a webinar on XLOOKUP and covered this XLOOKUP approximate match.
It's kind of like the VLOOKUP where you would say comma true at the end.
Or the match where you could say one or negative one at the end.
It can look for the exact match or just smaller, exact match are just larger.
And unlike VLOOKUP up and match the items you're looking up don't have to be sorted.
All right, so I have XLOOKUP here that's looking for 25 in this range and returning the rate from the adjacent range, and out here I'm looking for an exact match for the match mode.
And of course I can't find 25 because it's not in the list.
If I ask for the value that is just lower though, so that would be a negative one.
And by the way that's reverse from match. Match would have had a one there for just lower.
This makes a lot more sense than XLOOKUP.
Excel correctly finds the four as the age just lower than 25, and the eight as the age just greater than 30.
All right, so both of those are working great, but then the question that came up is does it work with text?
And I had never heard Joe on the Excel team talk about this, but let's just do a quick little test here.
So I don't have all the letters but I have various letters, and we're trying to see what the smaller is. So here I put in Kelly.
Kelly.
So K alphabetically comes before Kelly, and so the previous value is 700, that works great. And then what comes after that?
That would be the M, so that works great.
Here is Bill returning the A for just lower, or the C for just higher. Those seem to work now.
Now it shouldn't have to be sorted, so let's sort by column E, and it all continues to work.
And then I started to wonder okay, is it going to treat a capital K different than a lowercase K?
So over here I have the ASCII characters from 40 on down, and I carefully built my table in ASCII sequence.
So capital A comes along before so A capital J, in ASCII code comes long before a lowercase B.
And I was surprised that whether you look for capital Charlie or lowercase charlie it's treating both of those as just a C, so it's case-insensitive.
But then this one kind of threw me for a loop, let me just save you three hours of your life.
It goes zero to nine and then A to Z, upper or lowercase, and then these punctuation characters in this order.
These are the ASCII codes over here, it makes absolutely no sense, have no idea why.
Talked to a few people on the Excel team, they have no idea why. So that's what it it.
All right.
So XLOOKUP, approximate match does work with text and symbols, there is some logic built in there.
I want to thank you for stopping by, we'll see you next time for another net cast from MrExcel.
If you like this video please visit tl.page/mrexcel.
Scroll down, you'll find my group lessons and plenty more videos. Thanks for watching.
