excel如何把单元格中的关键字（汉字）直接引用到函数中

Question

如何将：=IF(SUM(COUNTIF(A2,"*"&{"C2"}&"*")),"C2",IF(SUM(COUNTIF(A2,"*"&{"C3"}&"*")),"C3",IF(SUM(COUNTIF(A2,"*"&{"C4"}&"*")),"C4",IF(SUM(COUNTIF(A2,"*"&{"C5"}&"*")),"C5",""))));C2=浏基伍集团，C3="当代滨江,C4=官渡颐和,C5=珠江郦城. 变为=IF(SUM(COUNTIF(A2,"*"&{"浏基伍集团"}&"*")),"浏基伍集团",IF(SUM(COUNTIF(A2,"*"&{"当代滨江"}&"*")),"当代滨江",IF(SUM(COUNTIF(A2,"*"&{"官渡颐和"}&"*")),"官渡颐和",IF(SUM(COUNTIF(A2,"*"&{"珠江郦城"}&"*")),"珠江郦城",""))))，也是说将C列单元格中的汉字直接引用到函数中，而不是手工输入。

Answer 1

可以用数组公式，不用一个一个的IF判断，例子：

输入的公式：

=INDEX(C:C,SUM(IF(ISERROR(FIND($C$2:$C$4,A2)),0,ROW($C$2:$C$4))))

输入完毕后按CTRL+SHIFT+ENTER，公式前后自动产生{}，然后下拉公式即可。

Answer 2

=IF(SUM(COUNTIF(A2,"*"&C2&"*")),C2,IF(SUM(COUNTIF(A2,"*"&C3&"*")),C3,IF(SUM(COUNTIF(A2,"*"&C4&"*")),C4,IF(SUM(COUNTIF(A2,"*"&C5&"*")),C5,""))))