1

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

1 Answer 1

2

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.
4
  • 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?
    – linucks
    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.
    – Daniele
    Aug 25, 2022 at 10:16
  • Thank you for the additional explanation @Daniele , much appreciated.
    – linucks
    Aug 25, 2022 at 19:43
  • 1
    Now all you have to do is accept the solution I have proposed, by checking the appropriate tick
    – Daniele
    Aug 25, 2022 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.