"MAXIF" Function, or equivalent?

Hi all!

I have a spreadsheet that I'm working on that compiles survey data from an online survey. I have averages, high scores, low scores, etc. figuring off of my data to product charts and graphs for a client.

I am attempting to find the high and low scores for individual surveys (there are 1054 surveys total right now). I know of max and min, but I really need something that would function like a "maxif" (which I realize does not exist).

Here's the problem:

In column A, I have a list of insurance provider names per survey (so, 1054 entries total). I then have some columns in between that show scores on questions from the survey, and then column AB contains my averages for each individual survey. Column AD has a list of the insurance provider names in alphabetical order (there are 167 unique provider names used in the 1054 surveys). Column AF is my high score column and lines up with column AD (so, I am looking to have 167 high scores in total as I want the high score per each provider, not survey).

Let's pretend A2:A10 say "Anthem Insurance."
I want to create a formula that would basically say something like:

Count what rows in column A = "Anthem Insurance," then match those cells to AB (so, it would figure since A2:A10 are the targeted cells, then I also want to correspond to AB2:AB10) and give me the high score (MAX) for that range of cells.

Is there a way to do this? Otherwise, I have to manually go through all 1054 rows and see what range of cells equal a certain insurance provider name to get those 167 high scores. I can keep doing this if I have to, but this data changes every single week and it eats up a lot of time.

Thanks!

Edit: Actually, one more question. If this would make the whole mess easier, is there a way to tell Excel to report a range to me? If I were to create a column, could I tell Excel, "Look at A2:A1055 and tell me what range of cells equals 'Anthem Insurance'" and have it report "A2:A10" as the value? If I could do that, at least the ranges would be right in front of me.

Here's an example for a MAXIF function. I've used the LARGE() as an array function.

Please Login or Register  to view this content.

A2:A7 has all the providers, B2:B7 their values and C2 the MAX for provider A2.
See my attachement. If you would like this for you WS then please attach it )or part)