求解EXCEL函數多欄比對回傳對應數值?

excel函數 excel

johannachang1004 2021-04-15 11:22:10 ‧ 1389 瀏覽

分享至

求解EXCEL函數多欄比對回傳對應數值?試過IF,OR,AND,VLOOKUP都跑不出來麻煩高手指教，想要H欄初次上機壓力?拜託謝謝

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2021-04-15 12:19:57

最佳解答

H3=IF(ISNA(INDEX($C$3:$C$11, MATCH($G3,$B$3:$B$11,0), 0)),"", INDEX($C$3:$C$11, MATCH($G3,$B$3:$B$11,0), 0)) & IF(ISNA(INDEX($E$3:$E$11, MATCH($G3,$D$3:$D$11,0), 0)),"", INDEX($E$3:$E$11, MATCH($G3,$D$3:$D$11,0), 0))
H4=IF(ISNA(INDEX($C$3:$C$11, MATCH($G4,$B$3:$B$11,0), 0)),"", INDEX($C$3:$C$11, MATCH($G4,$B$3:$B$11,0), 0)) & IF(ISNA(INDEX($E$3:$E$11, MATCH($G4,$D$3:$D$11,0), 0)),"", INDEX($E$3:$E$11, MATCH($G4,$D$3:$D$11,0), 0))
H5=IF(ISNA(INDEX($C$3:$C$11, MATCH($G5,$B$3:$B$11,0), 0)),"", INDEX($C$3:$C$11, MATCH($G5,$B$3:$B$11,0), 0)) & IF(ISNA(INDEX($E$3:$E$11, MATCH($G5,$D$3:$D$11,0), 0)),"", INDEX($E$3:$E$11, MATCH($G5,$D$3:$D$11,0), 0))
H6=IF(ISNA(INDEX($C$3:$C$11, MATCH($G6,$B$3:$B$11,0), 0)),"", INDEX($C$3:$C$11, MATCH($G6,$B$3:$B$11,0), 0)) & IF(ISNA(INDEX($E$3:$E$11, MATCH($G6,$D$3:$D$11,0), 0)),"", INDEX($E$3:$E$11, MATCH($G6,$D$3:$D$11,0), 0))