好办法没想到,给个最笨的方法,全名单读取,把左图里的所有“合格”改成数字“1”
=IF((VLOOKUP($A$2,$A$1:$G$7,MATCH(K$1,$A$1:$G$1,0),0)+VLOOKUP($A$2,$A$1:$G$7,MATCH($J2,$A$1:$G$1,0),0))=2,$A$2&CHAR(10),"")&IF((VLOOKUP($A$3,$A$1:$G$7,MATCH(K$1,$A$1:$G$1,0),0)+VLOOKUP($A$3,$A$1:$G$7,MATCH($J2,$A$1:$G$1,0),0))=2,$A$3&CHAR(10),"")&IF((VLOOKUP($A$4,$A$1:$G$7,MATCH(K$1,$A$1:$G$1,0),0)+VLOOKUP($A$4,$A$1:$G$7,MATCH($J2,$A$1:$G$1,0),0))=2,$A$4&CHAR(10),"")&IF((VLOOKUP($A$5,$A$1:$G$7,MATCH(K$1,$A$1:$G$1,0),0)+VLOOKUP($A$5,$A$1:$G$7,MATCH($J2,$A$1:$G$1,0),0))=2,$A$5&CHAR(10),"")&IF((VLOOKUP($A$6,$A$1:$G$7,MATCH(K$1,$A$1:$G$1,0),0)+VLOOKUP($A$6,$A$1:$G$7,MATCH($J2,$A$1:$G$1,0),0))=2,$A$6&CHAR(10),"")&IF((VLOOKUP($A$7,$A$1:$G$7,MATCH(K$1,$A$1:$G$1,0),0)+VLOOKUP($A$7,$A$1:$G$7,MATCH($J2,$A$1:$G$1,0),0))=2,$A$7&CHAR(10),"")
写在k2的格子里,横拉,下拉,
记得设置单元格格式为:自动换行。
如果是2个表,方法一样的,无非跨表格引用而已。
温馨提示:答案为网友推荐,仅供参考