Integrating Max into Index Match??

[MixedUpExcel]

Hi,

I have a 12 month table (3 columns per month - Sales Value / Margin / No. of orders) - 36 columns in total.

I have 3 columns in Columns A / B & C which are Product / Category and Suite/Component - table examples below.

I want to be able to match the Category with Component and the Max value in the monthly table for the required month and then return the Product from Column A.

I'm familiar with how index / match work and also max but work out how to integrate the 3 to get what I want.

Please help.



[TABLE="width: 1362"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JAN[/TD]
[TD]JAN[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]FEB[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]MAR[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]APR[/TD]
[TD]APR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]PRODUCT[/TD]
[TD]CATEGORY[/TD]
[TD]SUITE / COMPONENT[/TD]
[TD][/TD]
[TD]Sales Value[/TD]
[TD]Margin[/TD]
[TD]No of Orders[/TD]
[TD]Sales Value[/TD]
[TD]Margin[/TD]
[TD]No of Orders[/TD]
[TD]Sales Value[/TD]
[TD]Margin[/TD]
[TD]No of Orders[/TD]
[TD]Sales Value[/TD]
[TD]Margin[/TD]
[TD]No of Orders[/TD]
[/TR]
[TR]
[TD]PRODUCT 1[/TD]
[TD]CATEGORY 1[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]888[/TD]
[TD]21[/TD]
[TD]510[/TD]
[TD]789[/TD]
[TD]470[/TD]
[TD]344[/TD]
[TD]264[/TD]
[TD]262[/TD]
[TD]661[/TD]
[TD]733[/TD]
[TD]537[/TD]
[TD]343[/TD]
[/TR]
[TR]
[TD]PRODUCT 2[/TD]
[TD]CATEGORY 2[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]502[/TD]
[TD]136[/TD]
[TD]92[/TD]
[TD]304[/TD]
[TD]845[/TD]
[TD]814[/TD]
[TD]792[/TD]
[TD]14[/TD]
[TD]845[/TD]
[TD]635[/TD]
[TD]453[/TD]
[TD]626[/TD]
[/TR]
[TR]
[TD]PRODUCT 3[/TD]
[TD]CATEGORY 3[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]840[/TD]
[TD]1[/TD]
[TD]280[/TD]
[TD]597[/TD]
[TD]801[/TD]
[TD]864[/TD]
[TD]486[/TD]
[TD]192[/TD]
[TD]389[/TD]
[TD]93[/TD]
[TD]500[/TD]
[TD]255[/TD]
[/TR]
[TR]
[TD]PRODUCT 4[/TD]
[TD]CATEGORY 4[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]777[/TD]
[TD]996[/TD]
[TD]413[/TD]
[TD]460[/TD]
[TD]97[/TD]
[TD]838[/TD]
[TD]263[/TD]
[TD]536[/TD]
[TD]837[/TD]
[TD]589[/TD]
[TD]905[/TD]
[TD]940[/TD]
[/TR]
[TR]
[TD]PRODUCT 5[/TD]
[TD]CATEGORY 5[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]135[/TD]
[TD]993[/TD]
[TD]107[/TD]
[TD]142[/TD]
[TD]136[/TD]
[TD]473[/TD]
[TD]5[/TD]
[TD]941[/TD]
[TD]223[/TD]
[TD]840[/TD]
[TD]549[/TD]
[TD]614[/TD]
[/TR]
[TR]
[TD]PRODUCT 6[/TD]
[TD]CATEGORY 6[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]684[/TD]
[TD]772[/TD]
[TD]639[/TD]
[TD]601[/TD]
[TD]692[/TD]
[TD]366[/TD]
[TD]478[/TD]
[TD]339[/TD]
[TD]28[/TD]
[TD]862[/TD]
[TD]808[/TD]
[TD]752[/TD]
[/TR]
[TR]
[TD]PRODUCT 7[/TD]
[TD]CATEGORY 7[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]720[/TD]
[TD]808[/TD]
[TD]603[/TD]
[TD]23[/TD]
[TD]308[/TD]
[TD]294[/TD]
[TD]199[/TD]
[TD]525[/TD]
[TD]203[/TD]
[TD]869[/TD]
[TD]119[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]PRODUCT 8[/TD]
[TD]CATEGORY 8[/TD]
[TD]Component[/TD]
[TD][/TD]
[TD]320[/TD]
[TD]511[/TD]
[TD]199[/TD]
[TD]353[/TD]
[TD]544[/TD]
[TD]210[/TD]
[TD]185[/TD]
[TD]524[/TD]
[TD]918[/TD]
[TD]638[/TD]
[TD]865[/TD]
[TD]937[/TD]
[/TR]
[TR]
[TD]PRODUCT 9[/TD]
[TD]CATEGORY 9[/TD]
[TD]Component[/TD]
[TD][/TD]
[TD]803[/TD]
[TD]869[/TD]
[TD]255[/TD]
[TD]993[/TD]
[TD]505[/TD]
[TD]703[/TD]
[TD]879[/TD]
[TD]103[/TD]
[TD]94[/TD]
[TD]565[/TD]
[TD]903[/TD]
[TD]788[/TD]
[/TR]
[TR]
[TD]PRODUCT 10[/TD]
[TD]CATEGORY 2[/TD]
[TD]Component[/TD]
[TD][/TD]
[TD]893[/TD]
[TD]956[/TD]
[TD]223[/TD]
[TD]38[/TD]
[TD]171[/TD]
[TD]452[/TD]
[TD]291[/TD]
[TD]495[/TD]
[TD]359[/TD]
[TD]368[/TD]
[TD]451[/TD]
[TD]341[/TD]
[/TR]
[TR]
[TD]PRODUCT 11[/TD]
[TD]CATEGORY 2[/TD]
[TD]Component[/TD]
[TD][/TD]
[TD]944[/TD]
[TD]890[/TD]
[TD]555[/TD]
[TD]357[/TD]
[TD]42[/TD]
[TD]157[/TD]
[TD]775[/TD]
[TD]954[/TD]
[TD]745[/TD]
[TD]981[/TD]
[TD]991[/TD]
[TD]820[/TD]
[/TR]
[TR]
[TD]PRODUCT 12[/TD]
[TD]CATEGORY 2[/TD]
[TD]Component[/TD]
[TD][/TD]
[TD]456[/TD]
[TD]836[/TD]
[TD]405[/TD]
[TD]146[/TD]
[TD]224[/TD]
[TD]7[/TD]
[TD]277[/TD]
[TD]993[/TD]
[TD]146[/TD]
[TD]715[/TD]
[TD]489[/TD]
[TD]147[/TD]
[/TR]
[TR]
[TD]PRODUCT 13[/TD]
[TD]CATEGORY 2[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]730[/TD]
[TD]453[/TD]
[TD]279[/TD]
[TD]233[/TD]
[TD]309[/TD]
[TD]213[/TD]
[TD]345[/TD]
[TD]88[/TD]
[TD]437[/TD]
[TD]218[/TD]
[TD]294[/TD]
[TD]797[/TD]
[/TR]
[TR]
[TD]PRODUCT 14[/TD]
[TD]CATEGORY 10[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]842[/TD]
[TD]384[/TD]
[TD]115[/TD]
[TD]696[/TD]
[TD]616[/TD]
[TD]751[/TD]
[TD]50[/TD]
[TD]531[/TD]
[TD]952[/TD]
[TD]975[/TD]
[TD]621[/TD]
[TD]136[/TD]
[/TR]
[TR]
[TD]PRODUCT 15[/TD]
[TD]CATEGORY 2[/TD]
[TD]Component[/TD]
[TD][/TD]
[TD]586[/TD]
[TD]960[/TD]
[TD]321[/TD]
[TD]11[/TD]
[TD]742[/TD]
[TD]95[/TD]
[TD]247[/TD]
[TD]751[/TD]
[TD]281[/TD]
[TD]233[/TD]
[TD]951[/TD]
[TD]972[/TD]
[/TR]
[TR]
[TD]PRODUCT 16[/TD]
[TD]CATEGORY 2[/TD]
[TD]Component[/TD]
[TD][/TD]
[TD]155[/TD]
[TD]140[/TD]
[TD]982[/TD]
[TD]540[/TD]
[TD]81[/TD]
[TD]338[/TD]
[TD]640[/TD]
[TD]278[/TD]
[TD]751[/TD]
[TD]747[/TD]
[TD]53[/TD]
[TD]264[/TD]
[/TR]
[TR]
[TD]PRODUCT 17[/TD]
[TD]CATEGORY 2[/TD]
[TD]Component[/TD]
[TD][/TD]
[TD]573[/TD]
[TD]841[/TD]
[TD]966[/TD]
[TD]447[/TD]
[TD]691[/TD]
[TD]505[/TD]
[TD]496[/TD]
[TD]388[/TD]
[TD]440[/TD]
[TD]617[/TD]
[TD]167[/TD]
[TD]366[/TD]
[/TR]
[TR]
[TD]PRODUCT 18[/TD]
[TD]CATEGORY 5[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]491[/TD]
[TD]908[/TD]
[TD]263[/TD]
[TD]48[/TD]
[TD]423[/TD]
[TD]643[/TD]
[TD]911[/TD]
[TD]259[/TD]
[TD]450[/TD]
[TD]716[/TD]
[TD]190[/TD]
[TD]415[/TD]
[/TR]
[TR]
[TD]PRODUCT 19[/TD]
[TD]CATEGORY 6[/TD]
[TD]Suite[/TD]
[TD][/TD]
[TD]632[/TD]
[TD]729[/TD]
[TD]642[/TD]
[TD]67[/TD]
[TD]404[/TD]
[TD]54[/TD]
[TD]168[/TD]
[TD]362[/TD]
[TD]663[/TD]
[TD]460[/TD]
[TD]981[/TD]
[TD]44[/TD]
[/TR]
</tbody>[/TABLE]


Apologies as I'm not sure how to insert the table correctly with the borders etc.

The table I want to fill in with the values is below:

[TABLE="width: 852"]
<tbody>[TR]
[TD][/TD]
[TD]JAN[/TD]
[TD][/TD]
[TD]FEB[/TD]
[TD][/TD]
[TD]MAR[/TD]
[TD][/TD]
[TD]APR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PRODUCT[/TD]
[TD]SALES VALUE[/TD]
[TD]PRODUCT[/TD]
[TD]SALES VALUE[/TD]
[TD]PRODUCT[/TD]
[TD]SALES VALUE[/TD]
[TD]PRODUCT[/TD]
[TD]SALES VALUE[/TD]
[/TR]
[TR]
[TD]CATEGORY 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have tried a simple:

=INDEX($A$4:$A$22,MATCH(1,(L27=$B$4:$B$22)*(M27=$C$4:$C$22)*MAX(E4:E22),0))

It's fine until I add in the MAX(E4:E22) part and then it gives me an N/A... I'm guessing there is a better way and also, if possible, I'd like to incorporate a match for the month and the words Sales Value - rather than having to change the formula as I put it into a new cell for each month.

An example of a correct result would be (FOR JAN / CATEGORY 2 / COMPONENT / SALES VALUE):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]JAN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PRODUCT[/TD]
[TD]SALES VALUE[/TD]
[/TR]
[TR]
[TD]CATEGORY 2[/TD]
[TD]PRODUCT 11[/TD]
[TD]944[/TD]
[/TR]
</tbody>[/TABLE]


Any suggestions or solutions would be appreciated.

Thanks in advance.

Simon

 

[steve the fish]

Is your first table a pivot table? This calculation will be quite a laborious and long formula because you have data in a tabular fashion rather then flat.

 

[steve the fish]

There must be a shorter formula id imagine but then again it has a lot to do:

=INDEX(IF(B$28="Product",INDEX($A$4:$A$22,0),INDEX($E$4:$P$22,,MATCH(1,IF($E$1:$P$1=A$27,IF($E$3:$P$3=B$28,1)),0))),MATCH(1,IF(INDEX($E$4:$P$22,,MATCH(1,IF($E$1:$P$1=OFFSET(B$27,0,IF(B$28="Sales Value",-1,0)),IF($E$3:$P$3="Sales Value",1)),0))=MAX(IF($B$4:$B$22=$A29,INDEX($E$4:$P$22,,MATCH(1,IF($E$1:$P$1=OFFSET(B$27,0,IF(B$28="Sales Value",-1,0)),IF($E$3:$P$3="Sales Value",1)),0)))),1),0))

Its an array formula so enter Ctrl-Shift-Enter. Your first table pasted into A1. Your 2nd into A27. Paste the formula into B29 and copy across and down.

 

[MixedUpExcel]

Hi Steve,

Thanks for putting your time into this.

The table is actually part of a much larger data sheet so the format has to be this, rather than 'flat'.

Thanks for the formula.. it works but there's a bit missing.

One of my criteria is that in Column C - it has to match 'Component' - not interested in the 'Suite'.

Also, as I've not got my head around your formula totally, is there a part that you are indicating the number 1 highest value or is that just what MAX does? As I was hoping to integrate this into a different table afterwards, with a little change to show Category 2 - 1st / 2nd / 3rd / 4th & 5th highest sellers.

Are these easy changes to the formula?

Thanks.

 

[steve the fish]

You really need to use flat data. The majority of your queries could then be answered with pivot tables. Its a different formula to get the 2nd, 3rd etc. You would then need LARGE. The 1 is there in the first formula because it is matching up which row conforms to your criteria, ie an array of TRUE/ FALSE results. The IF places a 1 at TRUE results then the MATCH(1,etc. produces the row number to give to INDEX. Your new formula that only matches up to 'Component' would be something like:

=INDEX(IF(B$28="Product",INDEX($A$4:$A$22,0),INDEX($E$4:$P$22,,MATCH(1,IF($E$1:$P$1=A$27,IF($E$3:$P$3=B$28,1)),0))),MATCH(1,IF(INDEX($E$4:$P$22,,MATCH(1,IF($E$1:$P$1=OFFSET(B$27,0,IF(B$28="Sales Value",-1,0)),IF($E$3:$P$3="Sales Value",1)),0))=MAX(IF($B$4:$B$22=$A29,IF($C$4:$C$22="Component",INDEX($E$4:$P$22,,MATCH(1,IF($E$1:$P$1=OFFSET(B$27,0,IF(B$28="Sales Value",-1,0)),IF($E$3:$P$3="Sales Value",1)),0))))),1),0))

You can see how complex these formulas can become because of the tabulated nature.

 

[MixedUpExcel]

Thanks Steve.