if value exists in all 3 columns

[gtd526]

Hello,
How can I highlight cells if value exists in ALL 3 columns?
conditional formatting??
thx

NBA.xlsm
	AC	AD	AE
128	ORL	CLE	CLE
129	MIN	ORL	BOS
130	OKC	BOS	MIN
131		OKC	OKC
Favs

Cell Formulas
Range	Formula
AC128:AC131	AC128	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AC$128:AC128))),"")
AD128:AD131	AD128	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AD$128:AD128))),"")
AE128:AE131	AE128	=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$128:AE128))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

How can I highlight cells if value exists in ALL 3 columns?

Is this what you mean?

24 02 28.xlsm
	AC	AD	AE
128	ORL	CLE	CLE
129	MIN	ORL	BOS
130	OKC	BOS	MIN
131		OKC	OKC
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE131	Expression	=COUNTIF($AC128:$AE128,"?*")=3	text	NO

 

[StephenCrump]

Yes, the question is ambiguous. I'm guessing:

	AC	AD	AE
128	ORL	CLE	CLE
129	MIN	ORL	BOS
130	OKC	BOS	MIN
131		OKC	OKC
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE131	Expression	=COUNTIF($AC$128:$AE$131,AC128)=3	text	NO

which is based on the assumption that a value may appear either 0 or 1 times (no more!) in each column.

 

[Peter_SSs]

I'm guessing:

I like your guess better but could be foiled if a value can occur more than once in a column like this.

24 02 28.xlsm
	AC	AD	AE
128	ORL	MIN	CLE
129	MIN	ORL	OKC
130	OKC	BOS	MIN
131			OKC
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE131	Expression	=COUNTIF($AC$128:$AE$131,AC128)=3	text	NO

This covers that case but can it be done in a simpler way?

24 02 28.xlsm
	AC	AD	AE
128	ORL	MIN	CLE
129	MIN	ORL	OKC
130	OKC	BOS	MIN
131			OKC
CF (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE131	Expression	=COUNTIF($AC$128:$AC$131,AC128)*COUNTIF($AD$128:$AD$131,AC128)*COUNTIF($AE$128:$AE$131,AC128)	text	NO

 

[StephenCrump]

I have a feeling there will be lots more than three columns, so perhaps:

	AC	AD	AE
128	ORL	MIN	CLE
129	MIN	ORL	OKC
130	OKC	BOS	MIN
131			OKC
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE131	Expression	=AND(MMULT(TRANSPOSE(ROW($AC$128:$AC$131)),--($AC$128:$AE$131=AC128)))	text	NO

 

[gtd526]

Yes, the question is ambiguous. I'm guessing:

	AC	AD	AE
128	ORL	CLE	CLE
129	MIN	ORL	BOS
130	OKC	BOS	MIN
131		OKC	OKC
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE131	Expression	=COUNTIF($AC$128:$AE$131,AC128)=3	text	NO

which is based on the assumption that a value may appear either 0 or 1 times (no more!) in each column.

Yes, if the value is repeated in each column (3 Times).
The values will only appear once in each column.
How can we eliminate blank cells?
Row 134 will be the end of the range. And each cell contains a formula.

NBA.xlsm
	AC	AD	AE
128	IND	LAC	CHI
129	MIN
130
131
132
133
134
Favs

Cell Formulas
Range	Formula
AC128:AC134	AC128	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AC$128:AC128))),"")
AD128:AD134	AD128	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AD$128:AD128))),"")
AE128:AE134	AE128	=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$128:AE128))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE134	Expression	=AND(MMULT(TRANSPOSE(ROW($AC$128:$AC$131)),--($AC$128:$AE$131=AC128)))	text	NO
AC128:AE134	Expression	=COUNTIF($AC$128:$AC$134,AC128)*COUNTIF($AD$128:$AD$134,AC128)*COUNTIF($AE$128:$AE$134,AC128)	text	NO

 

[gtd526]

I like your guess better but could be foiled if a value can occur more than once in a column like this.

24 02 28.xlsm
	AC	AD	AE
128	ORL	MIN	CLE
129	MIN	ORL	OKC
130	OKC	BOS	MIN
131			OKC
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE131	Expression	=COUNTIF($AC$128:$AE$131,AC128)=3	text	NO

This covers that case but can it be done in a simpler way?

24 02 28.xlsm
	AC	AD	AE
128	ORL	MIN	CLE
129	MIN	ORL	OKC
130	OKC	BOS	MIN
131			OKC
CF (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE131	Expression	=COUNTIF($AC$128:$AC$131,AC128)*COUNTIF($AD$128:$AD$131,AC128)*COUNTIF($AE$128:$AE$131,AC128)	text	NO

If the value is repeated in each column (3 Times).
The values will only appear once in each column.
How can we eliminate blank cells?
Row 134 will be the end of the range. And each cell contains a formula.

NBA.xlsm
	AC	AD	AE
128	IND	LAC	CHI
129	MIN
130
131
132
133
134
Favs

Cell Formulas
Range	Formula
AC128:AC134	AC128	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AC$128:AC128))),"")
AD128:AD134	AD128	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AD$128:AD128))),"")
AE128:AE134	AE128	=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$128:AE128))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE134	Expression	=COUNTIF($AC$128:$AC$134,AC128)*COUNTIF($AD$128:$AD$134,AC128)*COUNTIF($AE$128:$AE$134,AC128)	text	NO
AC128:AE134	Expression	=AND(MMULT(TRANSPOSE(ROW($AC$128:$AC$131)),--($AC$128:$AE$131=AC128)))	text	NO

 

[StephenCrump]

The values will only appear once in each column.

In that case, try this variation on Post #3

	AC	AD	AE
128	ORL	CLE	CLE
129	MIN	ORL	BOS
130	OKC	BOS	MIN
131	LAC	OKC	OKC
132
Sheet1

Cell Formulas
Range	Formula
AC132:AE132	AC132	=IFERROR(1/0,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE132	Expression	=COUNTIF($AC$128:$AE$132,IF(LEN(AC128),AC128))=COLUMNS($AC$128:$AE$132)	text	NO

 

[gtd526]

In that case, try this variation on Post #3

	AC	AD	AE
128	ORL	CLE	CLE
129	MIN	ORL	BOS
130	OKC	BOS	MIN
131	LAC	OKC	OKC
132
Sheet1

Cell Formulas
Range	Formula
AC132:AE132	AC132	=IFERROR(1/0,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE132	Expression	=COUNTIF($AC$128:$AE$132,IF(LEN(AC128),AC128))=COLUMNS($AC$128:$AE$132)	text	NO

Perfect. thx

NBA.xlsm
	AC	AD	AE
127	@ Home
128	Q >= 55%	AL >=60%	AM >=60%
129	ORL	ORL	CHA
130	BRK		ORL
131
132
133
134
135
Favs

Cell Formulas
Range	Formula
AC129:AC135	AC129	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AC$129:AC129))),"")
AD129:AD135	AD129	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AD$129:AD129))),"")
AE129:AE135	AE129	=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$129:AE129))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC129:AE135	Expression	=COUNTIF($AC$129:$AE$135,IF(LEN(AC129),AC129))=COLUMNS($AC$129:$AE$135)	text	NO

 

[gtd526]

In that case, try this variation on Post #3

	AC	AD	AE
128	ORL	CLE	CLE
129	MIN	ORL	BOS
130	OKC	BOS	MIN
131	LAC	OKC	OKC
132
Sheet1

Cell Formulas
Range	Formula
AC132:AE132	AC132	=IFERROR(1/0,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC128:AE132	Expression	=COUNTIF($AC$128:$AE$132,IF(LEN(AC128),AC128))=COLUMNS($AC$128:$AE$132)	text	NO

I've expanded the formula you supplied. And its working well. thx.
AB:AC and AD:AE, 2 working formulas were supplied, so I used both.

NBA.xlsm
	AB	AC	AD	AE	AF
126	Possible Candidates for Odd Ball
127	@ Home
128	Q >= 55%	AL >=60%	AM >=60%	AR=TRUE	All 4
129	BOS	DAL	DAL	ORL	ORL
130	ORL	BOS	BOS	TOR
131		MIN	MIN
132		ORL	ORL
133
134
135
Favs

Cell Formulas
Range	Formula
AB129:AB135	AB129	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AB$129:AB129))),"")
AC129:AC135	AC129	=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AC$129:AC129))),"")
AD129:AD135	AD129	=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AD$129:AD129))),"")
AE129:AE135	AE129	=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AR$4:AR$33=TRUE)*(AR$4:AR$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$129:AE129))),"")
AF129:AF135	AF129	=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*(AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*(AL$4:AL$33>=0.6)*(AL$4:AL$33<>"")*(AR$4:AR$33=TRUE)*(AR$4:AR$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AF$129:AF129))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF129:AF135	Expression	=COUNTIF($AB$129:$AF$135,IF(LEN(AF129),AF129))=COLUMNS($AB$129:$AF$135)	text	NO
AB129:AF135	Expression	=COUNTIF($AB$129:$AF$135,IF(LEN(AB129),AB129))=COLUMNS($AB$129:$AF$135)	text	NO