There are a pair of well known Excel tricks with MATCH:
Find the last item in a long list of items that might contain empty cells.
Find the only number in a range of Error or Text values.
Excel tricksters have made use of these.
Why do they work?
And why do they not work with the new, superior XMATCH?
Thanks to Jose for sending this question in.
In this video, we dive in to how the binary search works in MATCH.
We see how to adjust XMATCH to find the last item in a list.
But there is no way to have XMATCH find the only number in a column of error cells. Which brings up the bigger question.... Why does this work at all with MATCH? It violates the rules of binary search.
Table of Contents
(0:00) Why can't XMATCH do what MATCH can do?
(0:30) Finding last item in list with MATCH
(1:30) How a binary search works
(5:30) Why doesn't the same trick work with XMATCH?
(7:55) Use MATCH to find only number in a range
(9:10) Excel Sort Sequence
(10:15) Why can't XMATCH find the only number?
(12:10) Why is MATCH finding the last item? Is it a bug?
Find the last item in a long list of items that might contain empty cells.
Find the only number in a range of Error or Text values.
Excel tricksters have made use of these.
Why do they work?
And why do they not work with the new, superior XMATCH?
Thanks to Jose for sending this question in.
In this video, we dive in to how the binary search works in MATCH.
We see how to adjust XMATCH to find the last item in a list.
But there is no way to have XMATCH find the only number in a column of error cells. Which brings up the bigger question.... Why does this work at all with MATCH? It violates the rules of binary search.
Table of Contents
(0:00) Why can't XMATCH do what MATCH can do?
(0:30) Finding last item in list with MATCH
(1:30) How a binary search works
(5:30) Why doesn't the same trick work with XMATCH?
(7:55) Use MATCH to find only number in a range
(9:10) Excel Sort Sequence
(10:15) Why can't XMATCH find the only number?
(12:10) Why is MATCH finding the last item? Is it a bug?
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2442 - Finding the last item in a list using MATCH.
Why does that work and why doesn't it work with XMATCH?
Learn Excel from MrExcel Podcast episode 2442.
Finding the only number in a list using MATCH.
But not XMATCH.
Oh wait, why does it work with MATCH?
It shouldn't!
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Great discussion today with Jose on YouTube about why this trick works.
If you have a long list of numbers and you want to figure out the last item in the list.
Just a great trick that we've always had.
Is that you do a MATCH for a very large number, a number larger than what's possible to happen.
So here we have a MATCH.
We're looking for 9999 and it finds that it is the 21st item in the list.
But if we had more items that exact same formula, would in this case, return 25.
Because that's the last time in the list.
It also works with text providing you look for the last thing alphabetically.
So here we're looking for ZZZ and it finds the 11th item.
Here we're looking for ZZZ and it finds a 17th item.
If I would add another item – Texas.
It changes to 18 right?
Why does this work?
And then why doesn't it work with XMATCH?
Here we go.
To understand how this MATCH is working. This particular MATCH with the one at the end that one says we're looking for the value just less than.
In other words, if it is not in there, find the value just less than it.
The less than and greater than are doing something called a binary search.
And the way that works is we look and see how many items are in the list.
15 in this case right?
And the first thing they do is they search directly in the middle of the list.
Now with an odd number of items, that's easy.
It's going to be 8.
And just as a footnote, because I can never remember it.
If there's an even number of items.
In other words, 14 items.
The middle of that list would be seven and a half.
It searches seven first.
It's not really relevant to right now.
I am just mentioning it in case you're wondering.
So it comes in here to to the middle of the list.
It sees that it's 17, and we're looking for 999.
So Excel says, it’s too small.
That means that we can throw out everything above us because the MATCH is assuming that the list is sorted ascending.
And Excel assumes if we're using this version of MATCH that we're going to follow the rules.
So it's able to throw out everything above that 17.
All of these get thrown out.
And now we have a simple little list of seven items.
In the middle of seven is the 4th item.
So the next place it's going to look is 85.
We're looking for 999 and it found 85.
It is too small so now they get to throw out all of these items.
And then we're down to these three: 54, 78, and 14.
It's going to look at for 78 is that has that compared to 999 – too small.
So it sends it down again and when it runs out of numbers it says “well, year, this is as close as I can get”.
It doesn't have any recollection that it already found 78 and 85 and 17.
That's long since forgotten.
It's just a very - I don't want to call it stupid - but it's just, it's just an algorithm.
It just says, well, this is all I got, here we are.
So that's how it returns the last item in the list.
Alright, now here's the interesting thing.
What if we aren't looking for a really large number?
What have we changed to a number that could happen?
How does the binary search change?
It's still always going to come into this midpoint, so we're looking for 45.
It finds a 17.
It says, “Oh, all right, so we know it has to be below this point”.
And then from 17 it goes down here to the 50 and from 50, we're looking for 45 so it sends it back up.
The midpoint is 44 so it sends it back down.
That means that by adjusting these midpoints, we can change which direction Excel is going to look.
So we're looking for 45.
It comes in and sees the 99.
That's too large.
So it knows it has to go to the top half.
The midpoint is then 50, which is too large, so it sends it up to the top.
And then this 46.
This 46 this cell becomes a really important cell.
I can get either one, two or three year depending on what's in the 46.
We're looking for 45 so if it says exactly 45, then it found the answer and reports that it's in the second position.
But if I put in a number smaller than 45.
That's going to force it down into the third position, so it will find say that the match is here in the third location.
Or if I put a number larger than 45 that will send it up and to the first position, right?
So by taking advantage of these midpoints: the midpoint, the second midpoint, the third midpoint.
You can really control what direction the numbers are going to go in.
The reason that a MATCH of a really large number will always find the last item in the list is because we're controlling the fact that every time that it hits a midpoint, it always is going to send it down and send it down.
Send it down until we end up at the last item in the list.
That's the magic here.
OK, but then the question is why does the same thing not work with XMATCH?
XMATCH should be better than MATCH in every way.
And when we look for 99999 in this long list, instead of telling us 21, it's telling us 22.
Well, there's several reasons why this is not going to work.
The first thing is you have to be really careful.
In MATCH, the third argument was called match_type and a positive one meant less than.
But in XMATCH, the match_type change to match_mode and they reversed it.
So if you want the next smaller item, it is a negative one.
So the first thing you have to do is change the one to a negative one.
We do that.
We're looking for 99999 and actually what it does is it says it's in the 5th item, which is right there.
Which happens to be correct.
It's the largest item in the list, so XMATCH is not finding the last item in the list.
It's finding the largest item of the list.
And so we have to go on here to the 4th argument in XMATCH, which is search mode.
By default, it's doing this search first to last with the data not having to be sorted.
What we have to do is choose Binary Sorted Ascending.
Even though the data is not sorted ascending!
So we have a third thing we have to do here.
We have to well first put the X in.
Change the one to a negative one.
And then add comma two.
And then we have it working just like MATCH would do.
So XMATCH can find the last item in the list, if you use the negative one for the second argument and the two for the last argument.
It's a three simple steps.
Change the MATCH to XMATCH, change the third argument from one to negative one.
Add a fourth argument of two.
And here are XMATCH formulas can do exactly what the MATCH formula is doing - find, the last item in the list.
And here I will test it.
I'll just put it in a four here in that 21 should change to a 22 because there's a new item in the list.
After Ohio, well add Texas and 11 should change to 12.
So there we are.
It's working.
Now this whole question came up when I was trying to do the word search puzzle for the Financial Modeling World Cup a while ago and I used this trick.
So in the Word Search podcast, I took advantage of the fact that I know that if we have a range that is almost all errors, just a single one in there that you can find the location of the one by using a MATCH of a “really large number”.
In this case, the really large number is 2.
And looking in that range with the “just smaller than” and it will find the location.
But why does that work?
If we do a binary search, the first place is going to look is 9.
It finds the value error.
The value is larger than any number, so then there's eight items left.
It's going to come in here to four.
I'm like, “Oh my God, is it only working because I was just lucky that I just happen to have the number in the podcast fall at one of these midpoints?” So I did a test here with all 16 possible values, putting the one in a different location each time and it doesn't seem to matter.
It comes in at 9 and whether it goes to four or ges to the 13, it always successfully finding the location of that largest value and I just take this for granted.
It's something that works.
But why is it working?
You know, and there's an interesting little fact here about the way that Excel sorts numbers.
I am going to take this whole range here and sort it A to Z.
Data, A-Z and the way that it sorts is numbers come first.
A date is just a really large number that's 44,000 or something like that.
Then text, then booleans with False first, then True.
And then the errors show up in the exact same order.
All errors are treated equally in the sort range, they just stay in the same order they were.
And then blank cells finally are last.
So the sorting rule is numbers first, then text, then Boolean, then errors with all errors are equal, followed by empty cells.
So the VALUE error and text would all appear after a number.
So the same trick here of using MATCH to find the lone 1 within a sea of text also works just perfectly well.
But does it work with XMATCH?
When I switch over from MATCH to XMATCH.
Change the one to negative one.
Add the two.
It's not working in most cases.
It's working in the cases where it found the number in the spot where it looked.
Like right here the 9th one worked.
And then once it encounters a #VALUE error, it knows it has to look up from there.
And so XMATCH then has eight to choose from.
And remember, our footnote is going to look at 4, right?
So that's why that one works.
And then once it finds the #VALUE error here, it's going to head up and look at the 2.
That's why that one works.
Once it finds the #VALUE error there it's going to head up and look for the one.
So it's able to find the 1, 2, 4 and 9.
Which is actually correct.
XMATCH says that #VALUE error is larger than a number.
So what I'm looking for must be up from there, right?
And so I can understand why XMATCH is not going to work in this case.
There's no way to get XMATCH to find the only number in the list.
There's plenty of other ways to do it.
I suppose we could do a FILTER, or things like that.
But then that brings me back to “wait a second, why is MATCH working?” This seems to be, this seems to be a bug, right?
I mean, we're all taking advantage of that bug.
But it certainly seems to be a bug.
Why is MATCH working when you have a whole bunch of error values and one numeric value?
Why is MATCH finding the numeric value?
If it was doing what a binary search should do, it should never be able to find the single number in the bottom half of the list.
It's a mystery.
I don't know.
I'm sure when Joe McDaid and his team encountered this with XMATCH they said, well, this is a bug and we are going to fix it.
Unfortunately, it means that there's still one trick here that MATCH can do that XMATCH cannot do and that’s to find the only number in a range of errors.
Alright, so the good news: for those of you who have been using MATCH to find the last item in a list, there is a way to do it with XMATCH.
For those of you who are using MATCH to find the only number in a range, it looks like we're going to have to keep using MATCH.
Although we will stay awake at night wondering why it works the way that it works.
If you know, let me know down in the YouTube comments.
This is a pretty heady podcast.
I can't even recommend this book if you like this podcast.
If you made it this far, you're way beyond this book.
If you like this video, I have to say, “What's wrong with us?” Down below, click Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank Jose for sending that great question in.
Actually it just opens up more questions than we have answers.
And I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Let's go, Nancy.
Why does that work and why doesn't it work with XMATCH?
Learn Excel from MrExcel Podcast episode 2442.
Finding the only number in a list using MATCH.
But not XMATCH.
Oh wait, why does it work with MATCH?
It shouldn't!
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Great discussion today with Jose on YouTube about why this trick works.
If you have a long list of numbers and you want to figure out the last item in the list.
Just a great trick that we've always had.
Is that you do a MATCH for a very large number, a number larger than what's possible to happen.
So here we have a MATCH.
We're looking for 9999 and it finds that it is the 21st item in the list.
But if we had more items that exact same formula, would in this case, return 25.
Because that's the last time in the list.
It also works with text providing you look for the last thing alphabetically.
So here we're looking for ZZZ and it finds the 11th item.
Here we're looking for ZZZ and it finds a 17th item.
If I would add another item – Texas.
It changes to 18 right?
Why does this work?
And then why doesn't it work with XMATCH?
Here we go.
To understand how this MATCH is working. This particular MATCH with the one at the end that one says we're looking for the value just less than.
In other words, if it is not in there, find the value just less than it.
The less than and greater than are doing something called a binary search.
And the way that works is we look and see how many items are in the list.
15 in this case right?
And the first thing they do is they search directly in the middle of the list.
Now with an odd number of items, that's easy.
It's going to be 8.
And just as a footnote, because I can never remember it.
If there's an even number of items.
In other words, 14 items.
The middle of that list would be seven and a half.
It searches seven first.
It's not really relevant to right now.
I am just mentioning it in case you're wondering.
So it comes in here to to the middle of the list.
It sees that it's 17, and we're looking for 999.
So Excel says, it’s too small.
That means that we can throw out everything above us because the MATCH is assuming that the list is sorted ascending.
And Excel assumes if we're using this version of MATCH that we're going to follow the rules.
So it's able to throw out everything above that 17.
All of these get thrown out.
And now we have a simple little list of seven items.
In the middle of seven is the 4th item.
So the next place it's going to look is 85.
We're looking for 999 and it found 85.
It is too small so now they get to throw out all of these items.
And then we're down to these three: 54, 78, and 14.
It's going to look at for 78 is that has that compared to 999 – too small.
So it sends it down again and when it runs out of numbers it says “well, year, this is as close as I can get”.
It doesn't have any recollection that it already found 78 and 85 and 17.
That's long since forgotten.
It's just a very - I don't want to call it stupid - but it's just, it's just an algorithm.
It just says, well, this is all I got, here we are.
So that's how it returns the last item in the list.
Alright, now here's the interesting thing.
What if we aren't looking for a really large number?
What have we changed to a number that could happen?
How does the binary search change?
It's still always going to come into this midpoint, so we're looking for 45.
It finds a 17.
It says, “Oh, all right, so we know it has to be below this point”.
And then from 17 it goes down here to the 50 and from 50, we're looking for 45 so it sends it back up.
The midpoint is 44 so it sends it back down.
That means that by adjusting these midpoints, we can change which direction Excel is going to look.
So we're looking for 45.
It comes in and sees the 99.
That's too large.
So it knows it has to go to the top half.
The midpoint is then 50, which is too large, so it sends it up to the top.
And then this 46.
This 46 this cell becomes a really important cell.
I can get either one, two or three year depending on what's in the 46.
We're looking for 45 so if it says exactly 45, then it found the answer and reports that it's in the second position.
But if I put in a number smaller than 45.
That's going to force it down into the third position, so it will find say that the match is here in the third location.
Or if I put a number larger than 45 that will send it up and to the first position, right?
So by taking advantage of these midpoints: the midpoint, the second midpoint, the third midpoint.
You can really control what direction the numbers are going to go in.
The reason that a MATCH of a really large number will always find the last item in the list is because we're controlling the fact that every time that it hits a midpoint, it always is going to send it down and send it down.
Send it down until we end up at the last item in the list.
That's the magic here.
OK, but then the question is why does the same thing not work with XMATCH?
XMATCH should be better than MATCH in every way.
And when we look for 99999 in this long list, instead of telling us 21, it's telling us 22.
Well, there's several reasons why this is not going to work.
The first thing is you have to be really careful.
In MATCH, the third argument was called match_type and a positive one meant less than.
But in XMATCH, the match_type change to match_mode and they reversed it.
So if you want the next smaller item, it is a negative one.
So the first thing you have to do is change the one to a negative one.
We do that.
We're looking for 99999 and actually what it does is it says it's in the 5th item, which is right there.
Which happens to be correct.
It's the largest item in the list, so XMATCH is not finding the last item in the list.
It's finding the largest item of the list.
And so we have to go on here to the 4th argument in XMATCH, which is search mode.
By default, it's doing this search first to last with the data not having to be sorted.
What we have to do is choose Binary Sorted Ascending.
Even though the data is not sorted ascending!
So we have a third thing we have to do here.
We have to well first put the X in.
Change the one to a negative one.
And then add comma two.
And then we have it working just like MATCH would do.
So XMATCH can find the last item in the list, if you use the negative one for the second argument and the two for the last argument.
It's a three simple steps.
Change the MATCH to XMATCH, change the third argument from one to negative one.
Add a fourth argument of two.
And here are XMATCH formulas can do exactly what the MATCH formula is doing - find, the last item in the list.
And here I will test it.
I'll just put it in a four here in that 21 should change to a 22 because there's a new item in the list.
After Ohio, well add Texas and 11 should change to 12.
So there we are.
It's working.
Now this whole question came up when I was trying to do the word search puzzle for the Financial Modeling World Cup a while ago and I used this trick.
So in the Word Search podcast, I took advantage of the fact that I know that if we have a range that is almost all errors, just a single one in there that you can find the location of the one by using a MATCH of a “really large number”.
In this case, the really large number is 2.
And looking in that range with the “just smaller than” and it will find the location.
But why does that work?
If we do a binary search, the first place is going to look is 9.
It finds the value error.
The value is larger than any number, so then there's eight items left.
It's going to come in here to four.
I'm like, “Oh my God, is it only working because I was just lucky that I just happen to have the number in the podcast fall at one of these midpoints?” So I did a test here with all 16 possible values, putting the one in a different location each time and it doesn't seem to matter.
It comes in at 9 and whether it goes to four or ges to the 13, it always successfully finding the location of that largest value and I just take this for granted.
It's something that works.
But why is it working?
You know, and there's an interesting little fact here about the way that Excel sorts numbers.
I am going to take this whole range here and sort it A to Z.
Data, A-Z and the way that it sorts is numbers come first.
A date is just a really large number that's 44,000 or something like that.
Then text, then booleans with False first, then True.
And then the errors show up in the exact same order.
All errors are treated equally in the sort range, they just stay in the same order they were.
And then blank cells finally are last.
So the sorting rule is numbers first, then text, then Boolean, then errors with all errors are equal, followed by empty cells.
So the VALUE error and text would all appear after a number.
So the same trick here of using MATCH to find the lone 1 within a sea of text also works just perfectly well.
But does it work with XMATCH?
When I switch over from MATCH to XMATCH.
Change the one to negative one.
Add the two.
It's not working in most cases.
It's working in the cases where it found the number in the spot where it looked.
Like right here the 9th one worked.
And then once it encounters a #VALUE error, it knows it has to look up from there.
And so XMATCH then has eight to choose from.
And remember, our footnote is going to look at 4, right?
So that's why that one works.
And then once it finds the #VALUE error here, it's going to head up and look at the 2.
That's why that one works.
Once it finds the #VALUE error there it's going to head up and look for the one.
So it's able to find the 1, 2, 4 and 9.
Which is actually correct.
XMATCH says that #VALUE error is larger than a number.
So what I'm looking for must be up from there, right?
And so I can understand why XMATCH is not going to work in this case.
There's no way to get XMATCH to find the only number in the list.
There's plenty of other ways to do it.
I suppose we could do a FILTER, or things like that.
But then that brings me back to “wait a second, why is MATCH working?” This seems to be, this seems to be a bug, right?
I mean, we're all taking advantage of that bug.
But it certainly seems to be a bug.
Why is MATCH working when you have a whole bunch of error values and one numeric value?
Why is MATCH finding the numeric value?
If it was doing what a binary search should do, it should never be able to find the single number in the bottom half of the list.
It's a mystery.
I don't know.
I'm sure when Joe McDaid and his team encountered this with XMATCH they said, well, this is a bug and we are going to fix it.
Unfortunately, it means that there's still one trick here that MATCH can do that XMATCH cannot do and that’s to find the only number in a range of errors.
Alright, so the good news: for those of you who have been using MATCH to find the last item in a list, there is a way to do it with XMATCH.
For those of you who are using MATCH to find the only number in a range, it looks like we're going to have to keep using MATCH.
Although we will stay awake at night wondering why it works the way that it works.
If you know, let me know down in the YouTube comments.
This is a pretty heady podcast.
I can't even recommend this book if you like this podcast.
If you made it this far, you're way beyond this book.
If you like this video, I have to say, “What's wrong with us?” Down below, click Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank Jose for sending that great question in.
Actually it just opens up more questions than we have answers.
And I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Let's go, Nancy.
