Return value is specific arguments are true

[marcellojek]

Hi all,

I am working on a construction site, where we have to fill out and commit some checklists before we cast the foundations with concrete. I have an Excel sheet with the list of foundations and a list of checklists - in one checklist we can commit several foundations for inspection. 

This is what the foundation list checklist looks like: 

And this is what the checklist sheet looks like:

There are checklists before we cast the concrete (Pre pour) and after the concrete is cast (Post pour). Now, I want to automate the process, so that Excel automatically ticks off the foundation once the checklist is marked as Finished.  Let's take the foundation P35-C5 as an example. In the Foundation list sheet, I would like Excel to return an "OK" value if the following conditions in the Checklists sheet are fulfilled:
- "x" in Pre pour? column 

- Structure column contains the name of the foundation (P35-C5)

- Status is Finished

In other words, I would like Excel to check whether there is a Finished pre pour checklist containing the given foundation and tick it off in the Foundation list. 

I tried with AND and OR functions, but these return the positive value only if all or any of the arguments are true, and therefore do not work in this case:

=IF(OR(ISNUMBER(SEARCH("x";Checklists!$D$4:$D$406));ISNUMBER(SEARCH("P35-C5";Checklists!$H$4:$H$406));ISNUMBER(SEARCH("Finished";Checklists!$J$4:$J$406)));"OK";"")

Can this be resolved with functions, or macros, or am I asking for too much?

[#2]

@marcellojek

Let's say that P35-C5 is in A10 on the Foundations sheet.

=IFERROR(IF(AND(INDEX(Checklists!$D$4:$D$406;MATCH("*"&A10&"*";CheckLists!$H$4:$H$406;0))="x";INDEX(Checklists!$H$4:$H$406;MATCH("*"&A10&"*";CheckLists!$H$4:$H$406;0))="Finished");"OK";"");"")

This could return an incorrect result if there are foundations named for example P35-C50 or P35-C58. To avoid that, use

=IFERROR(IF(AND(INDEX(Checklists!$D$4:$D$406;MATCH("*"&A10&CHAR(10)&"*";CheckLists!$H$4:$H$406&CHAR(10);0))="x";INDEX(Checklists!$H$4:$H$406;MATCH("*"&A10&"*";CheckLists!$H$4:$H$406;0))="Finished");"OK";"");"")

You may have to confirm the formula by pressing Ctrl+Shift+Enter.