Forum Discussion

Iron Contributor

Mar 30, 2023

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

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...

excel

Formulas and Functions

SergeiBaklan

MVP

Mar 30, 2023

JosWoolley

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

shall be fast

JosWoolley

Iron Contributor

Mar 30, 2023

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

SergeiBaklan
MVP
Mar 31, 2023
JosWoolley
Yes, you right, I overestimated TAKE(). On the range without blanks inside I usually use

=XMATCH(,A:A)-1
- Lorenzo
  Silver Contributor
  Mar 31, 2023
  SergeiBaklan
  
  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)
  - SergeiBaklan
    MVP
    Mar 31, 2023
    Lorenzo
    
    Yes, MATCH has better performance, XMATCH has more options. What to use depends on concrete situation.