How to display a most frequent word, based on certain criteria

Hey Guys,

I'm hoping someone can help me with my problem. Until now I have only found a solution to return a most frequent field (using INDEX and MODE), but nothing with regards to returning the field only if it meets certain criteria.

For example, I am trying to create a formula for a fantasy sports game, which will return the highest scoring Player that was picked the most (from a table of various lineups), but only during Team 1 vs. Team 2 games. I then want to expand this formula for Team 1 vs Team 3, 4, 5 and so on.

I have attached a sample of my worksheet, in which incorporates the rosters of the 10 best NFL players picked by 8 different Fantasy Players over the course of 3 different game dates (each game date comprises 6 games played by each of the 12 teams against each other).

I tried combining a MODE function together with an IF function but then got lost somewhere......

=IF(OR(AND(C:C="Steelers",D:D="Patriots"),AND(D:D="Steelers",C:C="Patriots")),INDEX(B2:B241,MODE(IF(OR(AND(C:C="Steelers",D:D="Patriots"),AND(D:D="Steelers",C:C="Patriots")),(MATCH(B2:B241,B2:B241,0))))))

In a nutshell, what I am trying to accomplish is as follows: I want to run an equations that will tell me if Team 1-10 will play against Team 10-1 tomorrow, then the statistics (from previous picks of those same teams playing against each other) will show that NFL players I-J will most probably be picked.

Thanks in advance for advice/direction/ideas and what not!

Cheers,

Joey

Fantasy League Stats Sample.xlsx

Keep ranges to minimum size (I've set it to 400):

Formula:

Please Login or Register  to view this content.

Thanks so much for the swift response! Would you mind perhaps explaining how your equation calculates the result (I'm not familiar with a few of the function), this would greatly help me in case I'd like to tweak it further.

Thanks again!

Joey

This is basis of the function to find most occuring word (think of it as 'by the book'):

Formula:

Please Login or Register  to view this content.

Now, instead of matching in all range (red) you need to match only those that has criteria if:

• $C$2:$C$400&$D$2:$D$400=C2&D2 (Data in range is same as cells in C and D)
• $B$2:$B$400<>"" (Exclude empty cells)

But now that you have criteria your formula will return error in cases where it doesn't match.
So you need to add IFERROR to avoid errors (or MODE function won't work):

Formula:

Please Login or Register  to view this content.

When you combine all of those things together you get solution:

Formula:

Please Login or Register  to view this content.