Is there an "IN" type function like the one used in sql that can be used in excel? For example, if i am writing an If statement in excel and I want it to check the contents of a cell for 5 different words can i write something like:
=If(A1=IN("word1","word2","word3","word4","word5"),"YES","NO")
You could use MATCH :
=MATCH(A1, {"word1","word2","word3","word4","word5"}, 0)
which will return the index of the matching item in the array list. The trailing 0 means it should be an exact match. It will return #N/A if it isn't there, so you can tag a IF(ISNA( onto the front to make it behave like your "IN":
=IF(ISNA(MATCH(A1, {"word1","word2","word3","word4","word5"}, 0)),"NO","YES")
Note the change in order of the "YES" and "NO"
=IF(OR(A1={"word1","word2","word3","word4","word5"}),"YES","NO")
I think an improvement on
=IF(OR(A1={"word1","word2","word3","word4","word5"}),"YES","NO")
would be to use
=IF(OR(A1={"word1","word2","word3","word4","word5"}),A1,"NO");
which is more like the SQL's IN clause.
Use the OR function. It operationes very similiar to what you are looking for.
IF(OR(A1="word1",A1="word2",A1="word3")=TRUE,"Yes","No")
Also doesn't require the ctrl+shift+enter when using.
