This is basis of the function to find most occuring word (think of it as 'by the book'):
Formula:
=INDEX($B$2:$B$400,MODE(MATCH($B$2:$B$400,$B$2:$B$400,0)))
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:
IFERROR(MATCH($B$2:$B$400,IF(($C$2:$C$400&$D$2:$D$400=C2&D2)*($B$2:$B$400<>""),$B$2:$B$400),0),"")
When you combine all of those things together you get solution:
Formula:
=INDEX($B$2:$B$400,MODE(IFERROR(MATCH($B$2:$B$400,IF(($C$2:$C$400&$D$2:$D$400=C2&D2)*($B$2:$B$400<>""),$B$2:$B$400),0),"")))
Bookmarks