Comparing Data in a cell to data in multiple columns

[Joym]

Column A and B has multiple data in each Cell. I want to compare data input in column c to the Column A n Column B.
Eg if data input in Cell C1 matches any Data in Column A or Column B.
If Data input in C1 Matches any Data in Column A then check if D1 is greater than 2 if both are true then display "X" in E1
If Data input in C1 Matches any Data in Column B then check if D1 is greater than 4 if both are true then display "X" in E1

Please Login or Register  to view this content.

codes i have tried

Please Login or Register  to view this content.

[mrice]

Try

=IF(AND(COUNTIF(A:B, C1) > 0, D1>2),"x","")

[FlameRetired]

Or do you mean if either "z" or "x" (in C1) is found in A1:B1 or do you mean the whole string "zx"?

Are we to understand that this is not case sensitive?

[Joym]

=IF(AND(COUNTIF(A:B, C1) > 0, D1>2),"x","") does work for the first portion -If Data input in C1 Matches any Data in Column A then check if D1 is greater than 2 if both are true then display "X" in E1
however if i change value in C1 to xc and D1 to 3 the outcome should me null however it displays SLA Exceeded as it is equating D1>2

Do i need compare column by column
i tried the below equation but get error in value

Please Login or Register  to view this content.

[Joym]

Try

=IF(AND(COUNTIF(A:B, C1) > 0, D1>2),"x","")

=IF(AND(COUNTIF(A:B, C1) > 0, D1>2),"x","") does work for the first portion -If Data input in C1 Matches any Data in Column A then check if D1 is greater than 2 if both are true then display "X" in E1

however if i change value in C1 to xc and D1 to 3 the outcome should me null however it displays SLA Exceeded as it is equating D1>2

*

Do i need compare column by column

i tried the below equation but get error in value

Please Login or Register  to view this content.

[Joym]

Compare the string and not the individual characters
Also it is not case sensitive

[JeteMc]

I am going to first address the questions asked in the first post. The following formula will display an 'X' in E1 when 'zx' is in C1 and a value greater than two is in D1. It will also display an 'X' in E1 when 'xc' is in C1 and a value greater than four is in D1. However as stated in post #4, it will not display an 'X' in E1 when 'xc' is in C1 and a value of three is in D1.

Please Login or Register  to view this content.

In posts #4 and #5 cell J2 and "SLA Exceeded" were introduced. If I understand them correctly the formula could be modified to read:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Joym]

I am going to first address the questions asked in the first post. The following formula will display an 'X' in E1 when 'zx' is in C1 and a value greater than two is in D1. It will also display an 'X' in E1 when 'xc' is in C1 and a value greater than four is in D1. However as stated in post #4, it will not display an 'X' in E1 when 'xc' is in C1 and a value of three is in D1.

Please Login or Register  to view this content.

In posts #4 and #5 cell J2 and "SLA Exceeded" were introduced. If I understand them correctly the formula could be modified to read:

Please Login or Register  to view this content.

Let us know if you have any questions.

Thanks that was very helpful
And sorry about the other 2 posts with the different inputs
That was an error on my part

[JeteMc]

You're Welcome. Thank you for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.