SOLVED

Last Dividend Transaction

New Contributor

Hi Guys, hope to get some help around here.

 

I am trying to return the last Div transaction from the worksheet Transactions to HoldingsDB.

 

A                              B              C                                                                            E

2022-05-12DivAPPLE INC. (XNAS:AAPL)                 0.23
2022-06-13BuyAPPLE INC. (XNAS:AAPL)             133.80
2022-08-11DivAPPLE INC. (XNAS:AAPL)                 0.23

 

The formula should be based on criterias: Type Div (B) and Stock (C)

 

Return (0.23) to J5 on worksheet HoldingsDB

 

A                                                                                          B              J

APPLE INC. (XNAS:AAPL)AAPL                  -  

 

Thank you for your support.

4 Replies

@rafaelcilios 

=INDEX('Transactions to HoldingsDB'!$D$1:$D$3,MATCH(LARGE(IF('Transactions to HoldingsDB'!$C$1:$C$3=HoldingsDB!A5,'Transactions to HoldingsDB'!$A$1:$A$3),1),'Transactions to HoldingsDB'!$A$1:$A$3,0))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

worksheet HoldingsDB:

HoldingsDB.JPG

 

worksheet Transactions to HoldingsDB:

Transactions to Holdings.JPG

Hi! Thank you very much for your help. The formula almost worked.
The only thing missing is the Type criteria, some of the stock is return the last Buy value.
best response confirmed by rafaelcilios (New Contributor)
Solution

@rafaelcilios 

You are welcome. Sorry the first formula was missing the "Div" condition.

=INDEX('Transactions to HoldingsDB'!$D$1:$D$3,MATCH(LARGE(IF(('Transactions to HoldingsDB'!$C$1:$C$3=HoldingsDB!A5)*('Transactions to HoldingsDB'!$B$1:$B$3="Div"),'Transactions to HoldingsDB'!$A$1:$A$3),1),'Transactions to HoldingsDB'!$A$1:$A$3,0))

You can try this formula which takes into account the Type criteria "Div". 

That worked perfectly, thank you for your help!

Cheers!