Lookup multiple criteria and find match within mixed alpha numerical cells and return the value matched

[Chrissy_M]

Hello, I need help with the following please. We get reports with hundreds of lines and I need to verify the error descriptions to catalogue and process errors. Only problem is the criteria is very specific and appears at different parts of string within the cell. Please see image attached to better explain the following:

B:B - column requiring formula

B2 to look for any criteria in full from D2:D6, search cell A2, and return value from D2:D6 that was found and matched.

Any help greatly appreciated. Thank you

 

[Cubist]

To summarize the conditions:
If the Error Description (C) is "Ignore" then D = "Ignore", it doesn't matter if it's new or old.
If the Error Description (C) is NOT "Ignore" then check the Analysis sheet. If it's on the Analysis sheet then it's an old/duplicate error so "Ignore", else "New Error".

=IF(C2="Ignore", "Ignore",IF(COUNTIF('Error Analysis'!A:A, B2)>0,"Ignore","New Error"))

 

[Chrissy_M]

To summarize the conditions:
If the Error Description (C) is "Ignore" then D = "Ignore", it doesn't matter if it's new or old.
If the Error Description (C) is NOT "Ignore" then check the Analysis sheet. If it's on the Analysis sheet then it's an old/duplicate error so "Ignore", else "New Error".

=IF(C2="Ignore", "Ignore",IF(COUNTIF('Error Analysis'!A:A, B2)>0,"Ignore","New Error"))

Wow, you are super clever! I would never have come up with that and I think my brain is pre-conditioned to over-complicating things. This is fabulous, thanks so much!