Get Second to Last from List

[Clermont]

I have attempted to get a formula to pick up the second from last entry of a particular selection from a list, however appears not perfect.


=index(b2:b1000,sumproduct(max(row(a2:a1000)*(h2A2:A1000))-2))

H2=Selection / Column B= Result Person) / Column A= Data. I would appreciate any pointers in the right direction.

Many Thanks

 

[Eric W]

Welcome to the forum.

Try:

=INDEX(B2:B1000,AGGREGATE(14,6,(ROW(A2:A1000)-ROW(A2)+1)/(A2:A1000=H2),2))

 

[DanteAmor]

Try

=INDEX(B:B,COUNTA(B2:B1000))

 

[Clermont]

Eric - Works a treat. Many thanks

 

[Eric W]

You're welcome.