Lookup 2nd most recent date for specific person and return corresponding value

[pacerfan07]

I have the following on Sheet1 below. On Sheet2 I just have everyone's Name listed once in column A (John, Kate, and Jim). In column B of Sheet2 I would like to look at the name from column A and see if it matches column A on Sheet1. If it does, then lookup the 2nd most recent occurrence in column B and return the corresponding values in column C. For example John should return 18.

Thanks!

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD]11/1/17[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]11/2/17[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Kate[/TD]
[TD]11/1/17[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]11/3/17[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Kate[/TD]
[TD]11/2/17[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jim[/TD]
[TD]11/1/17[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Kate[/TD]
[TD]11/3/17[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Kate[/TD]
[TD]11/4/17[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Jim[/TD]
[TD]11/1/17[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]John[/TD]
[TD]11/4/17[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

 

[bosco_yip]

1] Assume your Input table located in Sheet1 A1:C10

2] Output table located in Sheet2, criteria in A1:A3 enter: " John", "Kate" and " Jim"

3] In Sheet2, B1 enter formula and copied down :

=INDEX(Sheet1!C$1:C$10,AGGREGATE(14,6,ROW(Sheet1!C$1:C$10)/(Sheet1!B$1:B$10<>"")/(Sheet1!A$1:A$10=A1),2))

Regards
Bosco

 

[Aladin Akyurek]

Sheet1 (data)

[TABLE="class: grid, width: 135"]
<tbody>[TR]
[TD]John[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11/3/2017[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD="align: right"]11/3/2017[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD="align: right"]11/4/2017[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11/4/2017[/TD]
[TD="align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (processing)

[TABLE="class: grid, width: 196"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]2nd occurrence value[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


In B2 of Sheet2 control+shift+enter, not just enter, and copy down:

=INDEX(Sheet1!$C$1:$C$10,MAX(IF(Sheet1!$A$1:$A$10=$A2,IF(Sheet1!$B$1:$B$10=LARGE(IF(Sheet1!$A$1:$A$10=$A2,Sheet1!$B$1:$B$10),2),ROW(Sheet1!$C$1:$C$10)-ROW(Sheet1!$C$1)+1))))