Best way to get multiple OR conditions

[Eisasuarez]

Hi All

i have a list of OR conditions that need to be met before returning a value

ie =if(or(a1=“test1”,a1=“test2”,a1=“test3”)...etc

rather than having to change the values inside the cell if the or conditions was to grow, how can I look it up in a range

ie =if(or(a1=OrConditionCriterias),”yes”,”no”)

 

[sheetspread]

One of many ways:

Excel 2010
	A	B	C
1	test4		test1
2	TRUE		test2
3			test3
4			test4
5			test5
6			test6
Sheet3

Cell Formulas
Range	Formula
A2	=COUNTIF($C$1:$C$6,A1)>0

 

[mse330]

An alternative way to do this is to set an dymanic name range called (Or_Conditions) in the below example in column D

To create the dynamic name range click on Formula Tab > Name Manager > New > Name enter : Or_Conditions > Refers to enter : =OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D:$D),1)

Then in column B you'll have your array formula {=IF(SUM(--(A2=Or_Conditions))>0,"Yes","No")}

Book1
	A	B	C	D
1	List to validate	if formula		Or Conditions
2	test2	Yes		test1
3	Apple	No		test2
4	test1	Yes		test3
5	test3	Yes
6	test1	Yes
Sheet1

Cell Formulas
Range	Formula
B2	{=IF(SUM(--(A2=Or_Conditions))>0,"Yes","No")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Or_Conditions	=OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D:$D),1)

 

[mse330]

You can also aviod the array formula by applying sheetspread solution

=IF(COUNTIF(Or_Conditions,A2)>0,"Yes","No")

 

[jtakw]

Hi,

Why not Just use COUNTIF as sheetspread suggested? That's what I'd do.

Book1
	A	B	C	D
1	List to validate	if formula		Or Conditions
2	test2	Yes		test1
3	Apple	No		test2
4	test1	Yes		test3
5	test3	Yes
6	test1	Yes
Sheet141

Cell Formulas
Range	Formula
B2	=IF(COUNTIF(D$2:D$100,A2),"Yes","No")

As the OR conditions grow, add to D Column, B2 formula copied down, change adjust cell reference/range as needed.

 

[My Aswer Is This]

To make a Dynamic Named Range I always use a Table

Create a Table name it MyDate

Now every time you add more data to your Table the Named Range expands

 

[Eisasuarez]

Thanks all

 

[My Aswer Is This]

Thanks all

Glad we were able to help you.
Come back here to Mr. Excel next time you need additional assistance.