Nov 08 2021 05:24 AM
Good morning,
I'm having trouble with extracting the region that records the most sales for the "Furniture", "Office Supplies" and "Technology" only for the year 2015 using INDEX() and MATCH().
I have a tabel with the regions and years as rows and the category as columns (please refer screenshot).
My best attempt is:
=INDEX(Y13:Y31, MATCH(MAX(AA14,AA19,AA24,AA29),AA13:AA31,0))
It has loads of workarounds, the worst one being the first matrix (Y13:Y31), because the actual regions from the tabel are in Z12, Z17, Z22 and Z27. Y13:Y31 is an extra row to the left of the tabel.
I would greatly appreciate help.
Regards Tim
Nov 08 2021 06:14 AM
Solution=INDEX(A4:A22,MATCH(LARGE(IF(B4:B22=D24,C4:C22),1),C4:C22,0))
Is this what you are looking for? I have to enter formula with ctrl+shift+enter. The year can be entered in cell D24 in my example.
Nov 08 2021 06:43 AM
As variant
=CHOOSE(
INT( ( MATCH( MAX(($Z$13:$Z$31=Z34)*$AA$13:$AA$31),
$AA$13:$AA$31,0)
-1 ) / 5 )+1,
$Y$14,$Y$19,$Y$24,$Y$29 )
Nov 08 2021 06:14 AM
Solution=INDEX(A4:A22,MATCH(LARGE(IF(B4:B22=D24,C4:C22),1),C4:C22,0))
Is this what you are looking for? I have to enter formula with ctrl+shift+enter. The year can be entered in cell D24 in my example.