Forum Discussion
Solved
INDEX and MATCH to find max from specific year
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
I'm sorry but i see no limitations which could prevent to use nested IF. The only point it is a bit less flexible compare to nested If equivalents.
But what to use depends on goals, in some cases quick hardcording works quite fine.
If use formatting nested if becomes much more clear and editable. Like this
=IF(ISNUMBER(SEARCH("Sales", B3,1)),"Sales",
IF(ISNUMBER(SEARCH("Arch", B3,1)),"Architecture",
IF(ISNUMBER(SEARCH("Land", B3,1)),"Land",
IF(ISNUMBER(SEARCH("All", B3,1)),"All",
IF(ISNUMBER(SEARCH("Contracts", B3,1)),"Contracts",
IF(ISNUMBER(SEARCH("Construction", B3,1)),"Construction",
"No Match"
))))))
If instead of hardcoded strings use references nested IF becomes more flexible. And if add some extra references (nested if) for future strings to find it becomes even more flexible.
=IF(ISNUMBER(SEARCH($F$1,B3,1)),$G$1, IF(ISNUMBER(SEARCH($F$2,B3,1)),$G$2, IF(ISNUMBER(SEARCH($F$3,B3,1)),$G$3, IF(ISNUMBER(SEARCH($F$4,B3,1)),$G$4, IF(ISNUMBER(SEARCH($F$5,B3,1)),$G$5, IF(ISNUMBER(SEARCH($F$6,B3,1)),$G$6, IF(ISNUMBER(SEARCH($F$7,B3,1)),$G$7, IF(ISNUMBER(SEARCH($F$8,B3,1)),$G$8, IF(ISNUMBER(SEARCH($F$9,B3,1)),$G$9, "No Match" )))))))))I don't vote for nested IF, i would like to say where is no limitations here. What to use that's concrete person choice.
2 Replies
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 )=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.
.png){kind=link}