FORMULA NEEDED: determine cell value in part by searching key words in other cells, sheets

Put this array* formulae in cell G2:

=IF(SUM(IF(ISNUMBER(SEARCH(Sheet2!A$2:A$15,F2)),1,0))>0,"Good","Bad")

It should copy down automatically.

*NOTE: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>.

Hope this helps.

Pete

Originally Posted by Pete_UK

Put this array* formulae in cell G2:

=IF(SUM(IF(ISNUMBER(SEARCH(Sheet2!A$2:A$15,F2)),1,0))>0,"Good","Bad")

It should copy down automatically.

*NOTE: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>.

Hope this helps.

Pete

Thanks so much, Pete! It appears to do what I need. I just have to translate it to the more complex context of the actual database. I notice that curly brackets appear around the formula now - is that because it's an array formula? I don't really understand the formula you sent, but I'd really like to. Could you break it down for me very briefly? Or perhaps point me to a resource that might help me learn more? Again, many thanks for your quick reply!! I'll be clicking Add Reputation BTW!

Yes, the curly braces are put in by Excel when you do CSE - you do not type them yourself.

The array in question is A2:A15 on sheet2, and the formula will cycle through each of those cells in turn. The function SEARCH looks to see if A2 of Sheet2 is contained within cell F2 - if it is, then 1 will be added on to a cumulative sum, otherwise zero. This will continue through the cells of that array, so that at the end if the SUM is greater than zero then there was at least one cell from that array that matched part of F2. If the SUM is zero then there were no matches, so return the appropriate phrase/word.

Hope this helps.

Pete

Originally Posted by Pete_UK

Yes, the curly braces are put in by Excel when you do CSE - you do not type them yourself.

The array in question is A2:A15 on sheet2, and the formula will cycle through each of those cells in turn. The function SEARCH looks to see if A2 of Sheet2 is contained within cell F2 - if it is, then 1 will be added on to a cumulative sum, otherwise zero. This will continue through the cells of that array, so that at the end if the SUM is greater than zero then there was at least one cell from that array that matched part of F2. If the SUM is zero then there were no matches, so return the appropriate phrase/word.

Hope this helps.

Pete

Yes, thanks very much!