答案1
答案2
尝试这个公式:
=如果(和(SUMPRODUCT(($A$2:$A$12=D2)($B$2:$B$12="Y"))>0,SUMPRODUCT(($A$2:$A$12=D2)($B$2:$B$12="N"))=0),"Y",IF(AND(SUMPRODUCT(($A$2:$A$12=D2)($B$2:$B$12="Y"))=0,SUMPRODUCT(($A$2:$A$12=D2)($B$2:$B$12="N"))>0),"N",IF(AND(SUMPRODUCT(($A$2:$A$12=D2)($B$2:$B$12="Y"))>0,SUMPRODUCT(($A$2:$A$12=D2)($B$2:$B$12="N"))>0),"未知","")))
答案3
只需计算该值出现的次数,并以此来决定要做什么。例如:
=IF(COUNTIF($A$2:A$1000,$D2)>1,"Unknown",IFERROR(VLOOKUP($D2,$A$2:$B$1000,2,FALSE),"Not found"))
如果多于一个,则答案不明确,如果为 1,则 VLOOKUP 返回一个值,如果为 0,则 VLOOKUP 失败并且 IFERROR 会捕获它。