0

I'm having a bit of trouble with a formula I've been writing. I'm trying to ask excel to find all of the cells in column B that do not contain the words in the SEARCH Function and then return the value in column A when a cell is found.

The closest I have got is it finding the cells without the words in the SEARCH function but then it would repeat what is written in column A three times which is not what I want, (I want it to write it just once). In addition it was also adding what is written in column A even if it did not meet the search criteria (e.g. It did contain the words).

I have returned the formula to how I had it originally to make it easier to understand. (without my attempts at using <>).

=IF(ROWS($1:1)>$L$2,"No Active Fishing Places",INDEX('[Fishing Contact list July 2015.xlsm]Fishing Places'!$A:$A,SMALL(IF('[Fishing Contact list July 2015.xlsm]Fishing Places'!$Q$2:$Q$80="A",IF(ISNUMBER(SEARCH({"Tonbridge Lakes","Hawkhurst Lake","Charity Lake"},'[Fishing Contact list July 2015.xlsm]Fishing Places'!$B$2:$B$80)),ROW('[Fishing Contact list July 2015.xlsm]Fishing Places'!$B$2:$B$80))),ROWS($1:1))))

Thanks in advance

JOsh
  • 169
  • 2
  • 4
  • 15
  • check accepted answer in http://stackoverflow.com/questions/14803944/excel-searching-for-multiple-terms-in-a-cell – MikeD Aug 20 '15 at 10:36
  • I've looked at the discussion, however I had tried this previously and it still seems to return all results. When I include NOT it still includes the same result and when I include NOT(ISERR(SEARCH("<>Tonbridge Lakes" etc.) it returns a #NUM! result. – JOsh Aug 20 '15 at 10:59
  • When using IF(NOT(ISNUMBER(SEARCH("<>Tonbridge Lakes" etc.) it still returns all results and this time repeats the names, double or tripple. I think what is happening is it is writing how many of them are matching the critieria in the SEARCH function. – JOsh Aug 20 '15 at 11:03
  • The `=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH(...)))))>0;1;0)` construct returns **1** if any of the search arguments is found, else **0** ... you should be able to take this further ... note the importance of `SUMPRODUCT` to aggregate the individual search results into a single result – MikeD Aug 20 '15 at 11:12
  • ok. I've been trying to get it working and i think I'm close. The only problem is it returns 0 for the first result. Then beneath that in the next row a random name that is not on the list appears. Then beneath that its all #NUM!. – JOsh Aug 20 '15 at 12:15
  • I have figured out a solution, thanks for the helpful post. – JOsh Aug 20 '15 at 14:14

0 Answers0