单元格A1有三个值:优、良、合格,B1也有相同的三个值,现将两个单元格相连:A1&B1,可生成9种结果:优优、优良、优合格,良优……合格合格,要给每种结果生成一种与之相对应文字评语(如:你的表现太好了,今后再接再厉。不要骄傲哦!),用IF函数如何突破7层嵌套?如果用CHOOSE函数或者LOOKUP函数,具体如何操作?
=CHOOSE(MATCH(E8&F8,"优优","优良","优合格","良优","良良","良合格","合格优","合格良","合格合格"),评语!$A$1,评语!$A$2,评语!$A$3,评语!$A$4,评语!$A$5,评语!$A$6,评语!$A$7,评语!$A$8,评语!$A$9,)
这个公式错在哪里?
=IF(E8&F8="优优",评语!$A$1,IF(E8&F8="优良",评语!$A$2,IF(E8&F8="良优",评语!$A$3,IF(E8&F8="良良",评语!$A$4,IF(E8&F8="良合格",评语!$A$5,IF(E8&F8="合格良",评语!$A$6,IF(E8&F8="合格合格",评语!$A$7)))))))+IF(E8&F8="优合格",评语!$A$8,IF(E8&F8="合格优",评语!$A$9))
这条也不能显示结果呀!
对问题补充的回答:
CHOOSE函数中的MATCH函数少{}和匹配类型(此例中应为精确匹配,即0),正确的如下:
=CHOOSE(MATCH(E:E&F:F,{"优优","优良","优合格","良优","良良","良合格","合格优","合格良","合格合格"},0),评语!$A$1,评语!$A$2,评语!$A$3,评语!$A$4,评语!$A$5,评语!$A$6,评语!$A$7,评语!$A$8,评语!$A$9)
另:
如果采用如下图所示的评语表结构,还可以得到更简洁的公式:
=OFFSET(评语!$B$1,MATCH(E:E&F:F,评语!$A$1:$A$9,0)-1,0)
————————————————————————
把9种结果和对应评语都写到另一Sheet中,如Sheet2,再在C列中输入VLOOKUP公式如下:
=VLOOKUP(A:A&B:B,Sheet2!$A$1:$B$9,2,0)