EXCEL: MAX IF with multiple criteria to return name

Question

I have the following task: Which country had has the highest level of traffic in week 11? This is a snapshot of the table (with over 60.000 lines):

There is week 11 and 12 and multiple countries, so I need to find the country in week 11 which returns the highest sessions number/measure. I tried MAXIF and VLOOKUP, but I can't find a way to either return a name or incorporate multiple criteria (here: week 11 as restriction). Any suggestions what to do?

Answer 1

Got it in 2 steps. Still trying to join them together into 1 formula.

Assuming that you enter your chosen week "2016-11" in H1

Enter the following formula in I1 to get the max number for that week

=MAX(IF(B2:B60000=$H$1,D2:D60000))

Enter this formula in J1 to get the country that matches the week and the max number.

=IFERROR(INDEX($A$2:$D$60000,SMALL(IF(($B$2:$B$60000=$H$1)*($D$2:$D$60000=$I$1),ROW($D$2:$D$60000)-1),ROW(1:1)),1),"")

IMPORTNANT use CTRL + SHIFT + ENTER when inputing these formulas.

UPDATE

SINGLE FORMULA assuming you have the week "2016-##" in H1

=IFERROR(INDEX($A$2:$D$60000,SMALL(IF(($B$2:$B$60000=$H$1)*($D$2:$D$60000=MAX(IF(B2:B60000=$H$1,D2:D60000))),ROW($D$2:$D$60000)-1),ROW(1:1)),1),"")

Answer 2

Your data is begging to be used in a Pivot Table.

1. Highlight all of your data
2. Click Insert, then Pivot Table, then OK
3. Drag "Sessions" into your Values field and "Country" into your Rows field

By default the pivot table will give you a sum of sessions per country, you want to change this to a max

4. In the Values field, click on "Sum of traffic" then Value field settings
5. Summarize your data by max

And there you go!