second latest date, invoice and balance

[shyshine]

I know find second largest date can use LARGE formula, but I not very expert on how can I find SECOND latest date, invoice# and balance based on name & code IF the initial balance is show zero or negative. Attached is sample data in excel format which I have simplified to make it easy to understand.

[Richard Buttrey]

Hi

Perhaps you could give a few more examples and manually add the results you expect in columns G:I

I can see no 2nd occurrence of Banana..C12345D in the range A8:B14 so not sure what you expect.

[shyshine]

Hi Richard,
I have manually add in the result that i expected for Apple row, cell G to I.

My problem is,IF the balance at cell E show Positive figure, I can stop at here, no need go to further step, leave it blank. What IF cell E give me balance of Zero or Negative figure, then i need to find out again the second latest date, invoice and balance for cell G-I.

Since Banana row give positive figure, therefore no action needed. So it will leave it blank for that row. But Apple row will need to find out again since the balance (cell E) give 0.

Imaging there is hundred to thousand list of data, it could be same name and different code. Everytime i will need to find out date, invoice and balance from the raw date by based on name and code provided. Instead of manually sort and filter, i hope speed up the work by using formula. The sample provided, hope it's more understand.

[Teethless mama]

G3: =IF($E3<=0,LARGE(IF((A$8:A$14=A3)*(B$8:B$14=B3),C$8:C$14),2),"") ----> Press Ctrl+Shift+Enter, not just Enter

H3: =IF(G3="","",INDEX(D$8:D$14,MATCH(1,(A$8:A$14=A3)*(B$8:B$14=B3)*(C$8:C$14=G3),0))) ----> Press Ctrl+Shift+Enter, not just Enter

I3: =IF(G3="","",VLOOKUP(H3,A$18:C$23,3,0)) ----> Normally Enter