Forum Discussion
Solved
Array Formula, If, Index? How to make my formula work to get what I need?
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 ...
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