Forum Discussion
Array Formula, If, Index? How to make my formula work to get what I need?
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
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
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")
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
Thanks. It makes sense
