EXCEL公式如果A1含有A则返回张三、如果A1含有B则返回李四、如果A1含有C则返回王五
这个公式=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"A","B","C",},C4)),{"张三","李四","王五"}),"")可以正常运行,...
这个公式=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"A","B","C",},C4)),{"张三","李四","王五"}),"")可以正常运行,但是我把其中的A替换为1”(读作1寸),张三替换为2”(读作2寸),公式就运行错误了。求大神指导怎么改,谢谢!!!
展开
展开全部
原公式有精简余地,外层IFERROR没必要,可写为
=LOOKUP(,0/FIND({"","A","B","C"},C4),{"","张三","李四","王五"})
改为1不对,可能是写法问题,输入C4中输入的是数字1等,公式应改为
=LOOKUP(,0/FIND({"",1,"B","C"},C4),{"",2,"李四","王五"})
就是1外面不能有引号,有引号说明是文本类数字。
=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,"李四","王五"}))
展开全部
=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"A","B","C"},C4)),{"1“","2”","3“"}),"")
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
假设你说的是A1单元格,则另一单元格写入公式
=IF(COUNT(FIND({"a","b"},A1)),"含","不含")
=IF(COUNT(FIND({"a","b"},A1)),"含","不含")
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
修改公式为:
=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"1""","B","C"},C4)),{"2""","李四","王五"}),"")
=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"1""","B","C"},C4)),{"2""","李四","王五"}),"")
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
=IFERROR(LOOKUP(,0/ISNUMBER(FIND({"1”","B","C"},C4)),{"2”","李四","王五"}),"")
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询