Long OR list, a better way?

[jkicker]

Hi all,

I want to check if my cell is equal to one of many things. At the moment I have:

=OR(I2="Tenured Faculty",I2="Continuing Teaching Stream",I2="All other Librarians",I2="Contract-Limited",I2="Term Assignment",I2="Limited-Term Lecturers")

Is there a better way to write this out (in cell and without VBA)?

 

[RoryA]

I'd put the list of valid entries into a range and then compare the cell to that range using something like COUNTIF or MATCH, or even an array-entered OR formula.

 

[jkicker]

Array-entered OR formula would be something like:

=OR(I2=B1:B6)
?

I'm nesting the OR statement in an IF statement as my TRUE/FALSE argument.

 

[RoryA]

Correct. Or use something like =COUNTIF(B1:B6,I2)>0, which doesn't require array-entry.

 

[jkicker]

you say it doesn't require array entry, but to me it looks like it does because i need to fill in B1:B6. So i was shooting for something where i did not need to fill out any other cells, i'm just trying to write that I2 equaling any of the following should return a true value without having to write "I2=" all the time. i know i could use VBA for this, was just wondering if i could do it in cell.

 

[Fluff]

How about
=OR(I2={"Tenured Faculty","Continuing Teaching Stream","All other Librarians","Contract-Limited","Term Assignment","Limited-Term Lecturers"})

 

[RoryA]

That's not what array-entry means. If you use OR(I2=B1:B6) you need to press Ctrl+Shift+Enter when entering the formula, not just Enter. That is what array-entry means.

 

[jkicker]

Thank you RoryA, i will do some follow up learning on that.
And thank you Fluff, that's what I was looking for!

 

[Fluff]

Glad we could help & thanks for the feedback