Excel Find Last Item In List Using MATCH But Not XMATCH - 2442

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?