Get Second to Last from List

Clermont

New Member
Joined
Mar 27, 2019
Messages
10
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,910
Welcome to the forum.

Try:

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,192
Office Version
2007
Platform
Windows
Try

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

Watch MrExcel Video

Forum statistics

Threads
1,102,384
Messages
5,486,542
Members
407,550
Latest member
LucasBordure

This Week's Hot Topics

Top