EXCEL中公式错误

来源：百度知道 编辑：UC知道 时间：2024/05/28 02:12:14
请各位大侠帮忙,看看这个公式错在哪里,谢谢!=IF(IF(ISNA(VLOOKUP($B$2,Sheet2!$A:$F,2,FALSE)),VLOOKUP($B$2,Sheet3!$A:$F,2,FALSE),IF(ISNA(VLOOKUP($B$2,Sheet3!$A:$F,2,FALSE)),VLOOKUP($B$2,Sheet4!$A:$F,2,FALSE),IF(ISNA(VLOOKUP($B$2,Sheet4!$A:$F,2,FALSE)),VLOOKUP($B$2,Sheet5!$A:$F,2,FALSE)))))

公式的含义是，如果在Sheet2未找到，而不管是否能在Sheet3找到，就取Sheet3的数，而如果找到了，却不处理。公式改为：
=IF(ISNA(VLOOKUP($B$2,Sheet2!$A:$F,2,FALSE)),IF(ISNA(VLOOKUP($B$2,Sheet3!$A:$F,2,FALSE)),IF(ISNA(VLOOKUP($B$2,Sheet4!$A:$F,2,FALSE)),VLOOKUP($B$2,Sheet5!$A:$F,2,FALSE),VLOOKUP($B$2,Sheet4!$A:$F,2,FALSE)),VLOOKUP($B$2,Sheet3!$A:$F,2,FALSE)),VLOOKUP($B$2,Sheet2!$A:$F,2,FALSE))

而公式中用ISNA(VLOOKUP($B$2,Sheet2!$A:$F,2,FALSE))来判断是否存在并不好，改为：
=IF(COUNTIF(Sheet2!$A:$A,$B$2),VLOOKUP($B$2,Sheet2!$A:$F,2,),IF(COUNTIF(Sheet3!$A:$A,$B$2),VLOOKUP($B$2,Sheet3!$A:$F,2,),IF(COUNTIF(Sheet4!$A:$A,$B$2),VLOOKUP($B$2,Sheet4!$A:$F,2,),IF(COUNTIF(Sheet5!$A:$A,$B$2),VLOOKUP($B$2,Sheet5!$A:$F,2,),""))))

!=IF(IF(ISNA(VLOOKUP($B$2),Sheet2!$A:$F,2,FALSE)),VLOOKUP($B$2,Sheet3!$A:$F,2,FALSE),IF(ISNA(VLOOKUP($B$2,Sheet3!$A:$F,2,FALSE)),VLOOKUP($B$2,Sheet4!$A:$F,2,FALSE),IF(ISNA(VLOOKUP($B$2,Sheet4!$A:$F,2,FALSE)),VLOOKUP($B$2,Sheet5!$A:$F,2,FALSE)))))))))