Get last value in a column not equal to some value

Question

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?

Answer 1

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

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

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.