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]

Try:

=FILTER($D$2:$D$6,ISNUMBER((SEARCH($D$2:$D$6,A2))))

 

[Chrissy_M]

Try:

=FILTER($D$2:$D$6,ISNUMBER((SEARCH($D$2:$D$6,A2))))

Oh my days, wasted trying to sort this out with lots of head banging on the wall! Thank you, Thank you - works perfect!

 

[Cubist]

You're welcome.

 

[Chrissy_M]

You're welcome.

This is an extension question, and probably bit of a stretch but you seemed to handle my last question with such ease. What if I wanted to add two different error descriptions ("Duplicate" and "Parameter") and if these are found in the A:A reference, return "Ignore" or "Do Not Process" - something along those lines

 

[Cubist]

Add IF conditions.

=LET(f,FILTER($D$2:$D$6,ISNUMBER((SEARCH($D$2:$D$6,A2)))),IF(OR(f="Duplicate", f = "Parameter"),"Ignore", f))

 

[Chrissy_M]

Add IF conditions.

=LET(f,FILTER($D$2:$D$6,ISNUMBER((SEARCH($D$2:$D$6,A2)))),IF(OR(f="Duplicate", f = "Parameter"),"Ignore", f))

wow - you really are amazing - works a treat! Thanks a million!

 

[Chrissy_M]

hmmm, actually it's only coming up 'Ignore' for Parameter. Not calculating 'Ignore' for Duplicate.? This is beyond my expertise, is there any adjustments I can make to the fx to result with 'Ignore' when either of these criteria are met (Duplicate, or Parameter)? Thanks

 

[Cubist]

Check if cell D3 has extra spaces.

 

[Chrissy_M]

Check if cell D3 has extra spaces.

Rookie error - thanks!