EXCLE有關(guān)查找問(wèn)題請教

理論上這類(lèi)問(wèn)題應該由宏的循環(huán)來(lái)完成,如果用公式來(lái)做的話(huà),會(huì )很不但麻煩而且繁多.幫你用公式做了一個(gè),希望能派上用處 ^_^

??=IF(ISERROR(VLOOKUP(MID(D5,1,1),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,1,1),$A$2:$B$10,2))&IF(ISERROR(VLOOKUP(MID(D5,2,2),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,2,2),$A$2:$B$10,2))&IF(ISERROR(VLOOKUP(MID(D5,3,3),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,3,3),$A$2:$B$10,2))&IF(ISERROR(VLOOKUP(MID(D5,4,4),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,4,4),$A$2:$B$10,2))&IF(ISERROR(VLOOKUP(MID(D5,5,5),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,5,5),$A$2:$B$10,2))&IF(ISERROR(VLOOKUP(MID(D5,6,6),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,6,6),$A$2:$B$10,2))&IF(ISERROR(VLOOKUP(MID(D5,7,7),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,7,7),$A$2:$B$10,2))&IF(ISERROR(VLOOKUP(MID(D5,8,8),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,8,8),$A$2:$B$10,2))&IF(ISERROR(VLOOKUP(MID(D5,9,9),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,9,9),$A$2:$B$10,2))

用上述代碼就可以了,如果你有一百個(gè)值的話(huà),就請再加&IF(ISERROR(VLOOKUP(MID(D5,9,9),$A$2:$B$10,2))=TRUE,"",VLOOKUP(MID(D5,9,9),$A$2:$B$10,2)),"9"改成要的數據就行了,多一個(gè),加一個(gè),就OK

如果編號有100個(gè),問(wèn)題不是太多（設計了十個(gè)），那么申請原因列中都能出現對應的值。

因為你E列公式只設置了4次查找,所以5個(gè)數字組合就無(wú)效了.