Most frequently used word - subject to a particular filter

[Excelina1234]

Hi All,

If you know about Index, Mode, Match ("IMM") as an alternative to finding the most frequently used piece of *text* in a particular range (as opposed to simply using mode for a range comprising of *numbers*) you may be able to help me!

What I am trying to do is take IMM to the next level. Essentially, I have a range of text cells which correspond to different categories (illustrated below):

Category Text
France Train
France Bike
France Train
France Train
Belgium Bike
Belgium Bike
Belgium Bike

So - although Bike is the most frequent word - I want to retrieve the most frequent word for *France only* (in this case Train).

Is there any way we can develop the IMM formula to make it more specific so as to retrieve the word Train for France and Bike for Belgium?

Thanks a lot!

 

[Excelina1234]

Got it, thanks Andrew! Only two more things:

1. If a particular client category doesnt have more than 1 or 2 values, the formula retrieves #N/A. Is there a way to fix this?

2. How should I change the formula so that I can have two filters for each client: EB_Geography and EB_Currency? I've tried to do but it doesnt seem to work =INDEX(EB_Geography,MODE(IF(EB_Client=E3&EB_Currency=F3,MATCH(EB_Client&EB_Geography&EB_Currency,EB_Client&EB_Geography&EB_Currency,0))))

Thanks Andrew

 

[Andrew Poulsom]

The formula will return #N/A if there is no mode.

To add a criterion for currency try:

=INDEX(EB_Geography,MODE(IF(EB_Client=E3,IF(EB_Currency=F3,MATCH(EB_Client&EB_Geography&EB_Currency,EB_Client&EB_Geography&EB_Currency,0)))))