0

I have an excel spreadsheet with a column with states where I have ran races. I would like to write a function that returns the last out of state race I ran. How would I do that?

Denis McElligott
  • 3
  • 2

1 Answers1

1

You can use INDEX() to get the row number:

=INDEX(MAX((A:A<>"")*(A:A<>"NJ")*(ROW(A:A))),0)

enter image description here

and INDEX() to get the value:

=INDEX(A:A,INDEX(MAX(($A:$A<>"")*(A:A<>"NJ")*(ROW(A:A))),0))

for the last value not equal to NJ.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thanks @Gary's Student! That worked! Now I am trying to get the corresponding date ad I am using index match to do so. When I do that, it shows the first time I ran in the state, not the last time. Any idea why? – Denis McElligott Aug 22 '20 at 20:24