MATCH vs XMATCH: Binary Search to Find the Last Entry within a Range

Iron Contributor

I originally posted this as a response to this post, though thought it deserved a post in its own right.

 

To reiterate: I believe that the new XMATCH function has one serious shortcoming which the old MATCH does not have. That is, the the latter can be employed to perform a binary search in order to determine the position of the last value in a range (useful, for example, for defining dynamic ranges when one is not using a Table).

 

For example, with A1 and A10 containing "z" and "a" respectively (and all other cells in that column blank):

 

=MATCH("Ω",A:A)

 

which is equivalent to

 

 =MATCH("Ω",A:A,1))

 

will return 10, as desired.

 

(Of course, one can replace "Ω" in the above with a suitably large number for ranges comprising numerics only.)

 

However, I cannot find a way to replicate this functionality using XMATCH. As a result, I must continue to use MATCH in such cases.

 

I believe that the task of determining the last-used cell in a column using worksheet formulas is common enough that this post will be of interest to many of those who read it. Alternative formulas for obtaining that end result are linear, and therefore potentially thousands of times slower:

 

=MAX(IF(A:A<>"",ROW(A:A)))

 

for example, is a horribly inefficient construction, having to interrogate more than a million cells.

 

Am I missing something, or is this a huge oversight in the workings of the XMATCH function? Wasn't this function intended to be the 'successor' to MATCH, for which I would read 'able to mimic all that MATCH can do, plus extras'?

 

Regards

9 Replies

@JosWoolley 

=XMATCH("*", A:A, 2, -1)

appears to do it or I munderstood the point/question

Sample.png

@L z. 

 

Nice work! That will work for text entries. But is there an equivalent for a range comprising numerics, equivalent to

 

=MATCH(99^99,A:A)

 

?

@JosWoolley 

 

=XMATCH( TAKE( TOCOL(A:A,1), -1), A:A,,-1)

shall be fast

 

 

@L z. 

 

Ah, wait. I forgot that a search_mode parameter of -1 for XMATCH is a linear search, not binary, so that will most likely be much slower than the MATCH construction I gave.

Thanks, Sergei. But not very fast, I'm afraid. With A1:A1048575 containing 999 and A1048576 containing 99, 500 copies of your formula take considerably more time to calculate than 500 copies of the formula =MATCH(88^88,A:A). In the latter case, the 500 formulas calculate instantaneously; in your case, several seconds.

 

This makes sense, since, again, you are not employing binary searching.

 

Also, 3 functions instead of 1 seems unnecessary overkill.

 

Regards

 

@JosWoolley 

Ah, wait. I forgot that a search_mode parameter of -1 for XMATCH is a linear search, not binary, so that will most likely be much slower than the MATCH construction I gave.

 

I don't have the tools to measure this precisely but on a Sheet with 50k rows * 500 columns full of chars I could observe that repeating 500 times =MATCH("Ω", Sheet!A:A) appears to calc. instantaneously. On the other hand =XMATCH("*", Sheet!A:A, 2, -1) takes a bit more than 3 seconds here

@JosWoolley 
Yes, you right, I overestimated TAKE(). On the range without blanks inside I usually use

=XMATCH(,A:A)-1

 

@Sergei Baklan 

 

I had the same idea but no time to check perf. against MATCH this morning

Just did it with 50K rows x 500 columns. From what I can visually observe MATCH still perform better (here)

@L z. 

Yes, MATCH has better performance, XMATCH has more options. What to use depends on concrete situation.