I have column like so


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")))) )

  • 1
    Most the row number come from the range or the sheet? – Jacob Jan Tuinstra Apr 15 '17 at 13:45
  • it should come from the range – jason Apr 15 '17 at 13:53
=max(arrayformula(if(A1:A5="Mike", row(A1:A5), 0)))
| improve this answer | |
  • 1
    Better is to write: =max(arrayformula(if(A1:A5="Mike", row(A:A), 0))) – Jacob Jan Tuinstra Apr 15 '17 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 '17 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 Tuinstra Apr 15 '17 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 '17 at 16:54
  • Since you found there is actually a glitch in the formula, I believe you could update your answer following @JacobJanTuinstra comment. – marikamitsos May 23 '19 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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