search

[kevin carter]

Hi
I have the following formula that works a treat.

INDEX(A1:A100,MATCH(MAX(COUNTIF(A1:A100,A1:A100)),COUNTIF(A1:A100,A1:A100), 0))

How ever i need to be able to ignore a word in the range

Is it possible to search the range(as above)ignoring one word

thanks in advance




kevin

 

[Guest]

Hi
try
=INDEX(A1:A100,MATCH(MAX(COUNTIF(A1:A100,A1:A100)*(A1:A100<>"word")),COUNTIF(A1:A100,A1:A100)*(A1:A100<>"word"), 0))