使用SQL“IN”在Excel中的功能 [英] Using SQL "IN" Function in Excel

问题描述

是否有一个IN类型的函数，像在sql中使用的函数可以在excel中使用？例如，如果我在excel中编写了一个If语句，并且我希望它可以检查一个单元格的5个不同单词的内容，我可以写下如下：

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")

推荐答案

您可以使用MATCH：

You could use MATCH :

=MATCH(A1, {"word1","word2","word3","word4","word5"}, 0) 

它将返回数组列表中匹配项的索引。尾随0表示它应该是完全匹配的。如果不存在，则会返回＃N / A，因此您可以将 IF（ISNA（）标记在前面，使其表现为IN：

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")

的YES和NO

这篇关于使用SQL“IN”在Excel中的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！