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

 

[Chrissy_M]

Check if cell D3 has extra spaces.

Morning Cubist. Have another extension question for this task which appears to be ongoing.
I want to further categorise/validate which entries are new using column D and currently have the following fx: =IF(COUNTIF('Error Analysis'!A:A,'Input Sheet'!B2),"Ignore","New Error")

What I need to figure out is how I can extend this formula to also 'Ignore' any entries that = 'Ignore', or '#CALC!' in cell C# next to the D# cell reference.

In other words, they are only New Errors if they don't esit in the Analysis sheet and they're not already categorised to 'Ignore' or '#CALC!' - does that make sense?

Thanks

 

[Chrissy_M]

I would then like to take any of the New Errors and add them to the Analysis sheet, however I don't think this can be done without some VBA and really don't want to go there if I can avoid it

 

[Cubist]

It's not entirely clear.
Which sheet are you showing in the screenshot? The Input or the Analysis sheet?
If column C is "Ignore" or #CALC, what do you want to show in D?
If the entry appears on both sheets but labeled "Ignore", do you want "Ignore" or "New Error"?

 

[Chrissy_M]

It's not entirely clear.
Which sheet are you showing in the screenshot? The Input or the Analysis sheet?
If column C is "Ignore" or #CALC, what do you want to show in D?
If the entry appears on both sheets but labeled "Ignore", do you want "Ignore" or "New Error"?

apologies. Will clarify now I'm appropriately caffeinated!

The screenshot is reference to the main input sheet.
If column C is "Ignore" or #CALC, I would like column D result to = Ignore
If the entry (Measurement Point) appears on both lists, it means it's a prior known error so this result should be "Ignore"

Hope that makes a bit more sense - thanks

 

[Cubist]

This should work if there are no errors in column A of Error Analysis sheet. Try:

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

 

[Chrissy_M]

This should work if there are no errors in column A of Error Analysis sheet. Try:

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

The results of this are exactly the same as what I have currently.
I'll try to write this a bit differently, if the results of current fx in column D = "New Error", I want to add another criteria to say go check cell alongside in column C and if it is 'Ignore' or '#CALC!', then true result needs to = Ignore

Apologies, I'm new to these forums and trying to work out how best to spell this out. Appreciate your patience and help with this.

 

[Cubist]

Is there a reason why C has an error? Is it because there are no matches found in the list? If so, is it fine to return "Ignore" in the column C?
Secondly, is the new output you're asking for replacing your current D or is it going to be in column E?

 

[Chrissy_M]

Is there a reason why C has an error? Is it because there are no matches found in the list? If so, is it fine to return "Ignore" in the column C?
Secondly, is the new output you're asking for replacing your current D or is it going to be in column E?

In response to your questions, yes, C has an error because there are no matches found in that list. This is on an input sheet which has different entries day by day so we prefill a maximum number of lines with the formula ready to calculate, so technically we don't have to consider #CALC! as part of formula for column D I don't think.

Secondly, the new input will replace the current formula in column D - thanks

 

[Cubist]

Change the formula in C to this so it returns "Ignore" for the pre-rows instead of a #Calc error.

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

The formula in D:

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

 

[Chrissy_M]

Change the formula in C to this so it returns "Ignore" for the pre-rows instead of a #Calc error.

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

The formula in D:

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

Update to formula in column C works a treat - thanks

Think we're getting close on the last formula, just need to tweak a bit more I think. for the purpose of demonstrating the issue I have copied my original formula to column E - these results are correct in what I need them to show which is Meas Point in column B appears in a different Analysis sheet.

What I want to highlight is the two in E7 and E8, appear in the Analysis sheet, so we don't need these and they need to be ignored. Your extension formula pasted into column D is now reading anything in column C that has Ignore to Ignore, and all other entries to be classified as new entries which is not quite what I'm after.

Maybe I need to turn the formula around and say if column C = anything other than Ignore, go and see if it exists in the Analysis sheet and if not, it's a New Error.

Perhaps that might be better way to look at this calculation?