Forum Discussion

JosWoolley's avatar
JosWoolley
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
JosWoolley's avatar
JosWoolley
Iron Contributor
Mar 30, 2023

Lorenzo 

 

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)

 

?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 30, 2023

JosWoolley 

 

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

shall be fast

 

 

  • JosWoolley's avatar
    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's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 31, 2023

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

      =XMATCH(,A:A)-1

       

      • Lorenzo's avatar
        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)

Resources