Return second last matching column value

[Psylence]

Hi all, following is the table I am working on.

No Name Code Date Amount
1 ABC T56U 42795 1000
2 DEF SK7 42796 3500
3 GHI SK7 42797 9847
4 JKL 34TY 42798 6254
5 MNO TR09 42799 8000
6 PQR SK7 42800 9400
7 STU TYPL 42801 10000
8 VWX SK7 42802 6000
9 YZ 34TY 42803 3170

I'd like to search for the second last instance of SK7 in the Code column and retrieve the corresponding value in the amount column. In this example the second last SK7 is row 6 with an amount of 9400.

Is there any way of doing this without going into VB please?

Thanks!

 

[bosco_yip]

G2 :

=INDEX(E1:E10,AGGREGATE(14,6,ROW(C1:C10)/(C1:C10="SK7"),2))

Regards

 

[Deepak]

AGGREGATE is a very fantastic fn but in case you have xl 2007 then you may try this one.

=INDEX($E$1:$E$10,INDEX(LARGE(($C$1:$C$10=G3)*ROW($C$1:$C$10),2),,))

 

[Psylence]

View attachment 41761

G2 :

=INDEX(E1:E10,AGGREGATE(14,6,ROW(C1:C10)/(C1:C10="SK7"),2))

Regards

Works like a charm! Thank you sir.

Would it be much of a bother for me to ask you to explain the formula? No issues if it'll take up too much of your time.

 

[Deepak]

Works like a charm! Thank you sir.

Would it be much of a bother for me to ask you to explain the formula? No issues if it'll take up too much of your time.

It will helpful to understand the logic.

https://www.techonthenet.com/excel/formulas/aggregate.php