# Return the index of the last item in a text column in google sheets

I have a spreadsheet with a column with a list of text strings in it:

A1:A6 = {"apples", "pears", "oranges", "oranges", "pears", "apples"}

I want to get the index (row number) of the last matching item for my search string. So if I search for "apples" I want to get 6, and if I search for "oranges" I want to get 4.

I've tried MATCH with search types of 1, 0 and -1, none of which work (I think because the data is not sorted?).

Try this draggable formula:

``````=INDEX(MAX(ROW(A1:A)*(A1:A<>"")*(A1:A=A1)))
``````

Explanation

1. The 3 multiplications return the row number for each non-empty cell that is equal to the element of the first row.
2. `MAX()`, in conjunction with `INDEX()`, finds the maximum row value for that element among all elements in column `A`.
• Thank you so much @Daniele - that works! Amazing that what I thought was a trivial task requires such a complex formula. Can you explain how it works? Aug 25, 2022 at 7:58
• It is not a complex formula, indeed it is one of the simplest :)... but you are right, I have updated my answer. Aug 25, 2022 at 10:16
• Thank you for the additional explanation @Daniele , much appreciated. Aug 25, 2022 at 19:43
• Now all you have to do is accept the solution I have proposed, by checking the appropriate tick Aug 25, 2022 at 19:47