Search list of strings(cells) for particular string

I used array formulas to calculate sum and max for all rows which contains e.g. same month.

=MAX(IF('[Tasklist Scotford 1702.xlsb]tasklist c300rev1702'!$A:$A=AA3108,'[Tasklist Scotford 1702.xlsb]tasklist c300rev1702'!$P:$P,0))

Open in new window


I wonder if it's possible to search through strings (cells) from the same month looking for the ones that contain "scaffold". All I need is an indicator that this key word exists in one of the cell for selected month.
LVL 3
yahoooooAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naresh PatelFinancial AdviserCommented:
You Could Use This Kind Of Statement

=IF(ISERROR(MATCH(look up string,Range,FALSE)),"Not Found","Found")


Thanks
0
Rgonzo1971Commented:
Hi,

And if scaffold is only a part of the cell use

=IF(ISERROR(MATCH("*scaffold*",A1:A30,FALSE)),"NotFound","Found")

Open in new window

Regards
0
yahoooooAuthor Commented:
that would probably work for the whole sheet but i want it to look only at rows which contains selected value like month or year.
Look at my MAX code above. It would use the same range to look for that string
so that bit of my code -'[Tasklist Scotford 1702.xlsb]tasklist c300rev1702'!$A:$A=AA3108 - has  to be reused
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rgonzo1971Commented:
Hi,
pls try someting like this

=IF(MAX(('[Tasklist Scotford 1702.xlsb]tasklist c300rev1702'!$A:$A=AA3108)*(IFERROR(FIND("scaffold",'[Tasklist Scotford 1702.xlsb]tasklist c300rev1702'!$P:$P),0))),"Found","NotFound")

Open in new window


as array formula

Regards
0
yahoooooAuthor Commented:
I tried that before and now again. (without MAX as well)
I'd use search as it's not case sensitive, but that doesnt work :/

=IF((('[Tasklist Scotford 1702.xlsb]tasklist c300rev1702'!$A:$A=AA3108)*(IFERROR(SEARCH("SCAFFOLD",'[Tasklist Scotford 1702.xlsb]tasklist c300rev1702'!$P:$P),0))),"Found","NotFound")

Open in new window

0
Rgonzo1971Commented:
Hi,

Could you send a dummy example?

Regards
0
yahoooooAuthor Commented:
sheet1 contains a list of all cnt&group. I left one as an example. What Im doing is summing up norm. dur. and getting max number to another sheet for every cnt&group. That assumption should exclude all which at least one operation contains scaffolding, hence my question.

For now I sorted it as I created a list of all cnt&group which contains scaffolding and vlookup them with my current data but it would be good to automate it as i'd reuse it many times with different datasets
Book1.xlsx
0
Rgonzo1971Commented:
Hi,

see formula in Sheet2!G2

=IF(MAX((Sheet1!A:A=Sheet2!B2)*(IFERROR(SEARCH("scaffold";Sheet1!H:H);0)));"Found";"NotFound")

Open in new window

Regards
Book1v1.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yahoooooAuthor Commented:
good thinking with that Max ;) i should have realized that earlier! thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.