Array Formula, If, Index? How to make my formula work to get what I need?

[Minghao Liao]

I have 2 sheets. Sheet 1 is Transactions and Sheet 2 is stock summary. 

Under Transactions, I have entries that look like 

            A            B                          C                  D                  E                   F

1     Action      StockName            Vol         Prev Row        Prev Vol       CumVol

2       Buy          Stock 1                3000            0                     0                 3000

3     Sell          Stock 1                  3000            2                   3000                 0

4      Buy         Stock 2                  1000            0                   200                200

Under Stock Summary, there is a cell that I would like to indicate the CumVol I have for a particular Stock. 

           A                       B

1    StockName           Units

2     Stock 1             Formulae needed

In Bold is the formula I would like to enter. I am trying for a formula that will looking for all entries with "Stock 1" in Sheet "Transactions", Take the Max of Column "Prev Row" for those rows with "Stock 1" and Show "CumVol" in Cell B2. However, I do not know which formula works. I have tried a few formulas. 

{=(IF(MAX((IF(StockName=B17,PrevRow))),CumVol))}. 

The problem with my formulae is that using the eg above, it gives me 3000 instead of 0 that I am trying to achieve. 

[#2 · Accepted solution]

Hi Minghao,

Please try this formula and find it in the attached file:

=IF(ISNUMBER(MATCH(A2,Transactions!$B$2:B4,0)),INDEX(Transactions!$F$2:$F$4,MATCH(MAX(IF(A2=Transactions!B2:B4,Transactions!D2:D4)),Transactions!$D$2:$D$4,0)),"")

Regards

[#3]

Really appreciate your help.

=IF(ISNUMBER(MATCH(B17,StockName,0)),INDEX(CumVol,MATCH(MAX(IF(B17=StockName,PrevRow)),PrevRow,0)),"NA") works. 

For my understanding, I did not understand the need for the MATCH and used the formula below instead. Am I missing something? 

=IF(ISNUMBER(MATCH(B17,StockName,0)),INDEX(CumVol,MAX(IF(B17=StockName,PrevRow))),"NA")

[#4]

Hi,

You have to keep the second MATCH because it's responsible to deliver the correct row number for INDEX.

MAX gives the value itself, but MATCH gives the relative position of this value.

The relative position represents the required row number.

I hope this makes sense

Regards

[#5]

Thanks. It makes sense