CODEWITHSUNDEEP
excelexcel-formulaexcel-2019
Denis McElligott
Denis McElligott

Reputation: 3

Get last value in a column not equal to some value

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?

Upvotes: 0

Views: 286

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

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.

Upvotes: 1

Related Questions