錦子老師

如果要在輸入號碼後，設定公式會自動查詢這號碼的所有代碼，並一一填入到I欄顯示出來，這公式要如何設定呢?(我在竹H2儲存格輸入一個號碼，公式會自動出現這號碼的所有代碼)

▍1.SUMPRODUCT與COUNTIF函數

1⃣：點取I2儲存格輸入公式：「=IFERROR(INDEX($A$1:$A$11,SUMPRODUCT(LARGE(($B$2:$B$11= $H$2)*ROW($A$2:$A$11),COUNTIF($B$2:$B$11,$H$2)+2-ROW())),0),"")」後，向下複製到I3:I10儲存格。

公式說明：

=IFERROR(INDEX($A$1:$A$11,SUMPRODUCT(LARGE(($B$2:$B$11=$H$2)*ROW($A$2:$A$11), COUNTIF($B$2:$B$11,$H$2)+2-ROW())),0),"")

公式一：COUNTIF($B$2:$B$11,$H$2) 統計B2:B11儲存格中含有H2儲存格內容的筆數。

公式二：LARGE(($B$2:$B$11=$H$2)*ROW($A$2:$A$11),公式一+2-ROW()) 傳回B2:B11儲存格中含有H2儲存格內容列號(也可以ROW(2:11))中第N個大的列號(公式一傳回結果減2(這是由於從第二列寫公式，且要由小列號排到大列號故減2)+加目前列號。

公式三：SUMPRODUCT(公式二) 傳回公式二的數值總和。

公式四：INDEX($A$1:$A$11,公式三,0) 傳回A1:A11儲存格中第N列(公式三傳回結果)儲存格內容。

公式五：IFERROR(公式四,"") 如果出現錯誤訊息，表示所所符合的記錄都抓取出來了，故不填入資料。

~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

更多相關影片教學：請點我

更多相關文章：請點我