Highest Value Search/Corresponding Month Return

Hi everyone,

I helped create this report that lists the amount of customer visits our sales reps go on for the year, seperated by month.

The report also lists, per year, the Total visits in each year, and which month had the highest and lowest visit count. This is where I have an issue.

I want to use a function to figure out which month had the lowest and highest values and then display the month which corresponds to those values, but I have yet to find any function or combination of functions that work.

Prior to this I was inputting this information manually.

I've attached a sample of what I'm trying to do. Thanks in advance for any help!

Hi

I have attached a revised version of your file showing what you are looking for, this is using a combination of INDEWX, MATCH AND MIN/MAX. There is however a caveat inasmuch as it will only show the first matching value if there is more than one.

Regards

Jeff

Thank you very much for your help. Worked perfectly!

You can get two values by this Multiple Lookup Formula

[ =if(iserror(INDEX($A$3:$A$14,SMALL(IF(MAX($B$3:$B$14)=$B$3:$B$14,ROW($B$3:$B$14)-ROW($B$3)+1),ROW($H2)-1)),"",INDEX($A$3:$A$14,SMALL(IF(MAX($B$3:$B$14)=$B$3:$B$14,ROW($B$3:$B$14)-ROW($B$3)+1),ROW($H2)-1)) ]


FOR MIN

[ =if(iserror(INDEX($A$3:$A$14,SMALL(IF(MIN($B$3:$B$14)=$B$3:$B$14,ROW($B$3:$B$14)-ROW($B$3)+1),ROW($H2)-1)),"",INDEX($A$3:$A$14,SMALL(IF(MIN($B$3:$B$14)=$B$3:$B$14,ROW($B$3:$B$14)-ROW($B$3)+1),ROW($H2)-1)) ]