Question

EXCEL公式如果A1含有A则返回张三、如果A1含有B则返回李四、如果A1含有C则返回王五

这个公式=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"A","B","C",},C4)),{"张三","李四","王五"}),"")可以正常运行，但是我把其中的A替换为1”（读作1寸），张三替换为2”（读作2寸），公式就运行错误了。求大神指导怎么改，谢谢！！！

Answer 1

原公式有精简余地，外层IFERROR没必要，可写为
=LOOKUP(,0/FIND({"","A","B","C"},C4),{"","张三","李四","王五"})
改为1不对，可能是写法问题，输入C4中输入的是数字1等，公式应改为
=LOOKUP(,0/FIND({"",1,"B","C"},C4),{"",2,"李四","王五"})
就是1外面不能有引号，有引号说明是文本类数字。

追问

感谢大神指教，还请教一个问题，现在我一个文档有两个表格分别为Sheet1和Sheet2，我现将Sheet1表格的A1=Sheet2表格的A2，直接在Sheet1表格的A1中输入“=Sheet2！A2”这样是没问题的，但是如果Sheet2表格A2是空白没数据的话，则返回的Sheet1表格的A1是“0”，我想让如果Sheet2表格A2是空白的话则什么都不返回，不让“0”出现，请问怎么操作，谢谢！

追答

外面再套个IF
=IF(LOOKUP(,0/FIND({"",1,"B","C"},C4),{"",2,"李四","王五"})="","",LOOKUP(,0/FIND({"",1,"B","C"},C4),{"",2,"李四","王五"}))

追问

解决了，公式尾部加后缀&""就可以了

Answer 2

=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"A","B","C"},C4)),{"1“","2”","3“"}),"")

Answer 3

假设你说的是A1单元格,则另一单元格写入公式
=IF(COUNT(FIND({"a","b"},A1)),"含","不含")

Answer 4

修改公式为：
=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"1""","B","C"},C4)),{"2""","李四","王五"}),"")

Answer 5

=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"1”","B","C"},C4)),{"2”","李四","王五"}),"")