iT邦幫忙

0

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

求解EXCEL函數多欄比對回傳對應數值?試過IF,OR,AND,VLOOKUP都跑不出來麻煩高手指教,想要H欄初次上機壓力?拜託 謝謝
https://ithelp.ithome.com.tw/upload/images/20210415/20136704A5Iu8kZ2Ut.png

1 個回答

1
海綿寶寶
iT邦大神 1 級 ‧ 2021-04-15 12:19:57
最佳解答

https://ithelp.ithome.com.tw/upload/images/20210415/20001787FOtflGY7Mj.png

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))

我要發表回答

立即登入回答