search last occurence of 2D array

[ericlch16]

hi expert,

I have a 2 D array and i need to look the last occurrence of a string in one of column of that 2 d array. please note this column is sorted.

array(1,1) = 3
array(2,1)= 3
array(3,1)= 5
array(4,1)= 5
array(5,1)= 5
array(6,1)= 5
array(7,1)= 7
array(8,1)= 7

i am doing a loop in that array and when at row 3, i get [5] as value. How can i quickly get the last occurrence index of [5] without doing another loop ?

The last occurrence in this simple array example is 6 .

Is there a quick way to get it ? i am more concerned about SPEED as my array can be thousands of rows. Is there a binary function i can use or an array existing function i can use?

 

[Peter_SSs]

i am doing a loop in that array and when at row 3, i get [5] as value.

Could we see your existing code that includes that loop?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

 

[StephenCrump]

If MyArray contains 3 in column 1, and the column is sorted in increasing order, then the last row containing 3 should be equal to:

Application.Match(3, Application.Index(MyArray, , 1), 1)