3

I have column like so

Mike
Fred
Mike
Andy
Mike

I want to return to me the last row number where the column = Mike. So in this case the output should be 5

I've think its combination of max filter row functions but need help on putting it together. Or maybe it could be done another way.

This is my best attempt so far: = ROW (FILTER(A1:A, ROW(A1:A) =MAX( FILTER( ROW(A1:A) , ("Mike")))) )

2
  • 1
    Most the row number come from the range or the sheet?
    – Jacob Jan
    Apr 15, 2017 at 13:45
  • it should come from the range
    – jason
    Apr 15, 2017 at 13:53

1 Answer 1

4
=max(arrayformula(if(A1:A5="Mike", row(A1:A5), 0)))
6
  • 1
    Better is to write: =max(arrayformula(if(A1:A5="Mike", row(A:A), 0)))
    – Jacob Jan
    Apr 15, 2017 at 15:35
  • Why would that better? I would argue that the range argument to the IF condition and to the ROW function should be exactly the same, for clarity's sake.
    – ttarchala
    Apr 15, 2017 at 15:54
  • 2
    Have you tried that? What happens if you do this: =max(arrayformula(if(C14:C18="Mike", row(C14:C18), 0)))?
    – Jacob Jan
    Apr 15, 2017 at 16:17
  • Interesting, I didn't know that it would return absolute vs. relative row numbers depending on the form of range argument. Is this documented somewhere? Certainly not in ROW()'s help.
    – ttarchala
    Apr 15, 2017 at 16:54
  • Since you found there is actually a glitch in the formula, I believe you could update your answer following @JacobJanTuinstra comment. May 23, 2019 at 5:37

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.