OR formula using array in the logic

[scott_86_]

Hi,

(Using Microsoft 365)

I'm sure I've been able to achieve this in the past; however, I'm struggling to do it again: Is it possible to use an array for 'logical1' in the OR (or another) formula?

I thought =OR(A2=$C$2:$C$8) would work and then copy the formula down; however, unfortunately it does not.

Example just using row 2 below: If A2 = any item in C2:C8 then it will be TRUE.

Thanks.

 

[rabsofty]

You could do something like this

  =IF(ISERROR(MATCH(A1,$C$2:$C$8,0)),"","True")

 

[rabsofty]

my match formula post#2 should have said A2 instead of A1

if you need an exact match, eg: case sensitive
the match formula i provided does not allow for case sensitivity.

if you require case sensitive match, this one does the trick

  =IF(ISERROR(MATCH(TRUE,EXACT($C$2:$C$8,A2),0)),"not in list","in list")

Note:

When you enter this formula, hold the CTRL + SHIFT key then press ENTER

then you can copy the formula down

 

[Peter_SSs]

Note:

When you enter this formula, hold the CTRL + SHIFT key then press ENTER

FYI: The OP has Microsoft 365 so Ctrl+Shift+Enter is not required.

I thought =OR(A2=$C$2:$C$8) would work

As I understand your requirement, it does work. Isn't this what you are describing?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

24 03 23.xlsm
	A	B	C
1
2	Breating Apparatus	TRUE	Breating Apparatus
3	Confined Space	TRUE	Confined Space
4	Other	FALSE	First Aid
5	First Aid	TRUE
OR

Cell Formulas
Range	Formula
B2:B5	B2	=OR(A2=C$2:C$4)

 

[rabsofty]

Peter_SSs, thanks for the tip about Microsoft 365, I'm running Office 2019