Returning second to last value of a recurrent item of a column

[zarpelao]

Hi there i have a table that needs to update the backlog of calls that werent answered and update this number to the next time that I select this particular client.

The first time i select a client from a dropdown, the previous backlog information must be empty (for that is no problem, i use a countif formula), but if the client was already included the information that i have to display is the penultimate value contained in the "next backlog" column, i managed to make the cell display the last information of the "next backlog" but this is the same as the row itself, so i need help to make it show the second to last value.

The formula that i used is in B1:

=IF(COUNTIF($A$1:A1;A1)>1;IF(COUNTIF($A$1:A1;A1)=0;A1&" not found";INDEX($E$1:E6;MAX((IF(A1:E6=A1;ROW(a1:e6)-1;1))-1));0)

The value returned is 0 because the client has never appeared before, if is a recurrent client, the value shown is the E column (which would be the last occurrence of the backlog value for that given client, but i need it to return the second to last)

It looks like this:

[Column A: BRANDS] [Column B:PREVIOUS BACK LOG] [Column C:ANSWERED TODAY] [Column D:RECEIVED TODAY ] [Column E:NEXT BACKLOG (received + previous backlog - answered ) ]

1 AAAA 0 35 15 20
2 BBBB 0 5 2 3
3 AAAA 20 25 7 2
4 BBBB 3 4 1 0
5 CCCC 0 2 2 0
6 CCCC 0 0 10 10

Can someone help me?

[martindwilson]

this will give the second to last value from col e for corresponding second to last entry in column A ,if only one entry it returns 0 (dropdown in j1)
=IF(COUNTIF(A1:A100,J1)>1,INDEX(E1:E100,SUMPRODUCT(LARGE(INDEX(--(A1:A100=J1),0)*ROW(A1:A100),2))),0)

[zarpelao]

this will give the second to last value from col e for corresponding second to last entry in column A ,if only one entry it returns 0 (dropdown in j1)
=IF(COUNTIF(A1:A100,J1)>1,INDEX(E1:E100,SUMPRODUCT(LARGE(INDEX(--(A1:A100=J1),0)*ROW(A1:A100),2))),0)

It works perfectly!!! Thank you so much for the prompt response.. You just saved my day :D !!