Google Sheet Function updated for Excel

[dmcmaste]

I am trying to replicated this Google Sheet in Excel. I can't get the array/filter functionality in Column I, L working correctly in Excel. Can anyone please help replicate/troubleshoot.

Portfolio - Troubleshooting

TRANS⇝Rollover IRA Date,Type,Stock,Transacted Units,Transacted Price (per unit),Avg. Price,Fees,Stock Split Ratio,Previous Units,Cumulative Units,Transacted Value,Previous Cost,Cost of Transaction,Cost of Transaction (per unit),Cumulative Cost,Gains/Losses from Sale,Realised Gains/Losses %,Cumul...

docs.google.com

For Column I - if it is the first time buying the stock previous units should be 0 otherwise added Transacted Units (Column D) to the previously bought Cumulative Unites (Column J).
Fol Column L - If it is the first time buying the stock it should be 0 otherwise it should be the previous Cumulative Cost (Column O) and added to the new transacted value (Column K)


Thanks!

 

[dmcmaste]

Try

=IFERROR(LOOKUP(2,1/($C$2:$C$1000=$S5)/($B$2:$B$1000="buy"),$J$2:$J$1000),"")

=IFERROR(LOOKUP(2,1/($C$2:$C$1000=$S5)/($B$2:$B$1000="buy"),$O$2:$O$1000),"")

Thanks for the reply. I will try later today! Really appreciate it!

 

[dmcmaste]

Try

=IFERROR(LOOKUP(2,1/($C$2:$C$1000=$S5)/($B$2:$B$1000="buy"),$J$2:$J$1000),"")

=IFERROR(LOOKUP(2,1/($C$2:$C$1000=$S5)/($B$2:$B$1000="buy"),$O$2:$O$1000),"")

Worked perfectly. Thanks!