Below is a possible solution for the challenge – Challenge 9 – Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet
Put following Array formula in H4
=INDEX(D2:D20,MATCH(1,–(MAX(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))
=(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))),0))
Put following Array formula in H5
=INDEX(E2:E20,MATCH(1,–(MAX(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))
=(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))),0))
Note – Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.
The solution sheet can be downloaded from here – Solution-Challenge-9-–-Get-the-Customer-Spending-Maximum-Amount-for-a-given-Month-and-Starting-Alphabet
Solution that you can keep H4 or H5 empty:
H4
=INDIRECT("D"&MATCH(I5,E2:E20,0)+1)
H5
=MAX(IF( ( IF($H$3"",–(LEFT(D2:D20,1)=$H$3),–(LEFT(D2:D20,1)"#")) * IF($H$2"", –(C2:C20=TEXT(DATE(1,$H$2,1),"mmm")), –(C2:C20"#")) ) =1,E2:E20,0),1)