Google sheets, get the last row number of repeated value

Question

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

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

ttarchala · Accepted Answer · 2017-04-15 15:05:31Z

4

=max(arrayformula(if(A1:A5="Mike", row(A1:A5), 0)))

answered Apr 15, 2017 at 15:05

ttarchala

1,69910 silver badges15 bronze badges

1

Better is to write: =max(arrayformula(if(A1:A5="Mike", row(A:A), 0)))
– Jacob Jan
Commented 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
Commented 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
Commented 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
Commented 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.
– marikamitsos
Commented May 23, 2019 at 5:37

| Show 1 more comment

1 Answer 1