Using INDEX to find the top sales person each month

[AlanFisher]

Hello,

I need to analyse 12 columns of data (C:N), which is one column per month, to find the highest number of sales visits in each column.

Then I wish to identify from column B, the name of the person who has this number of sales visits.

I made a helper column (Col O) to MAX each row, then used =INDEX($B$17:$B$21,MATCH(MAX($O$17:$O$21),$O$17:$O$21,0)) to identify the sales person with the MAX visits. This works (please see S7) but I would prefer not to have the helper column.

I would like the formula to analyse the range C17:N21 and find the highest number but MATCH appears to reject that - (see S17). I have tried to use Named Ranges (see S10) but that didn't work either.

I'm wondering if ROWS or COLUMNS formula should be in there too but I don't know how to use them.

Finally, even if we get this to work, what will happen in the event of a tie - where two people make the same highest number of visits.

I have uploaded a file so I hope this is useful.

Thank you - regards, Alan.

 

[Somendra Misra]

@AlanFisher
Welcome to the forum.

Use this formula:
=INDEX($B$17:$B$21,SMALL(IF(MAX(RangeOfVisits)=RangeOfVisits,ROW(B17:B21)-ROW(B17)+1),1))


Enter with Ctrl+Shift+Enter as this will be an array formula.
Regards,

 

[AlanFisher]

Thank you. I will use this tomorrow and try to understand how it works. Much appreciated, Alan

 

[Somendra Misra]

Welcome antime.

Regards,

 

[NARAYANK991]

Hi Alan ,

The reason the following statement :

=INDEX($B$17:$B$21,MATCH(MAX($C$17:$N$21),$C$17:$N$21,0))

does not work is because the MATCH function works only with single-column or single-row ranges.

The MAX function will return the maximum value within a multi-column , multi-row range , but the MATCH function will fail to find a match in the multiple column multiple row range.

Narayan

 

[AlanFisher]

Hi Alan ,

The reason the following statement :

=INDEX($B$17:$B$21,MATCH(MAX($C$17:$N$21),$C$17:$N$21,0))

does not work is because the MATCH function works only with single-column or single-row ranges.

The MAX function will return the maximum value within a multi-column , multi-row range , but the MATCH function will fail to find a match in the multiple column multiple row range.

Narayan

Thank you. I thought that might be the case but worth knowing anyway. Alan