Formula Help Please

[How_Do_I]

Hello, if I was using the following formula to search for cells containing the word "land" how would I add to my formula to exclude "England" but return "Scotland, Ireland" etc please?

=IF(ROWS($B$8:B8)>$C$6,"",SMALL(IF(ISNUMBER(SEARCH($C$3,Results)),ROW(Results)-ROW(INDEX(Results,1))+1),ROWS($B$8:B8))).

 

[mrhstn]

Hi

Try like this

=IF(ROWS($B$8:B8)>$C$6,"",SMALL(IF(ISNUMBER(SEARCH($C$3,Results))*NOT(ISNUMBER(SEARCH("England", Results))),ROW(Results)-ROW(INDEX(Results,1))+1),ROWS($B$8:B8)))

 

[FormR]

Hi, if it is just one specific case to exclude then here is another option:

=IF(ROWS($B$8:B8)>$C$6,"",SMALL(IF(ISNUMBER(SEARCH(SUBSTITUTE(UPPER($C$3),"ENGLAND",""),,Results)),ROW(Results)-ROW(INDEX(Results,1))+1),ROWS($B$8:B8)))

 

[How_Do_I]

Thank you both for your solutions.