Thread: Finding text across three columns

Hi,

I hope someone can help a bit of a newby! I have a sheet of text values and want to return a "TRUE" if there is a "Poor" or "Insufficient" in any of three columns (with 100 rows). So my data looks like this:

Q1.2 Q2.2 Q2.6
Insufficient Sufficient N/a no restrictive interventions
Poor Insufficient Insufficient
Sufficient Sufficient Sufficient

And I would like a value at the end of each row to show "Contains Poor or Insufficient" / TRUE.

Thanks,
Kev B

Originally Posted by kball

Hi,

I hope someone can help a bit of a newby! I have a sheet of text values and want to return a "TRUE" if there is a "Poor" or "Insufficient" in any of three columns (with 100 rows). So my data looks like this:

Q1.2 Q2.2 Q2.6
Insufficient Sufficient N/a no restrictive interventions
Poor Insufficient Insufficient
Sufficient Sufficient Sufficient

And I would like a value at the end of each row to show "Contains Poor or Insufficient" / TRUE.

Thanks,
Kev B

I suppose that your data columns are A, B and C and data starts from A1

try this in D1

=IF(OR(COUNTIF(A1:C1,"poor"),COUNTIF(A1:C1,"insufficient")),"True","")
you can use "Contains Poor or Insufficient" in place of "True"

Regards.

That works a treat and is a big help!

Originally Posted by kball

Hi,

I hope someone can help a bit of a newby! I have a sheet of text values and want to return a "TRUE" if there is a "Poor" or "Insufficient" in any of three columns (with 100 rows). So my data looks like this:

Q1.2 Q2.2 Q2.6
Insufficient Sufficient N/a no restrictive interventions
Poor Insufficient Insufficient
Sufficient Sufficient Sufficient

And I would like a value at the end of each row to show "Contains Poor or Insufficient" / TRUE.

Thanks,
Kev B

Hi Kev B

If your data is in A1:C3, then in E1 = Poor and in F1 = Insufficient

then in D1 this formula

=IF(OR(A1=$E$1,A1=$F$1,B1=$E$1,B1=$F$1,C1=$E$1,C1=$F$1),TRUE,FALSE)

Hope this is what you want?

oldchippy

Originally Posted by kball

That works a treat and is a big help!

you're welcome kball and thanks for the feedback.