excel中一對多查找問題解決方法匯總（乾貨！）

06-02

公式模型一：預備知識：數組，邏輯值的概念，if函數的較好掌握，small函數的基本用法，index的基本用法。

f3的公式：

=IFERROR(INDEX($C$3:$C$11,SMALL(IF($B$3:$B$11=$E3,ROW($1:$9),99),COLUMN(A1))),"")

數組公式，輸入或者複製公式後，按住ctrl和shift鍵按回車，公式自動出現大括弧，向右向下拉即可。

結構分析：這個公式的核心結構是index（結果範圍，small（if（條件範圍=條件值，row（），一個超過結果範圍個數的數字），column（））），加了個iferror是為了將錯誤值顯示為空。

必須理解這個公式的思路，才能夠靈活套用這個結構去解決問題。拿這個例子來說，b列是條件列，c列是結果列，e列是具體的條件，最終的結果需要橫著拉（針對同一產品而言）。

這個公式的核心是if數組的運用，$B$3:$B$11=$E3可以得到一系列的邏輯值，而僅當b列中為"A"的項目對應的項才是true，從而返回ROW($1:$9)這個數組的對應數字，而對於其他不是"A"的項目，對應的就是99，具體來看，這裡的if的結果就是{1;99;99;4;99;99;99;99;9}，可以發現，這個數組裡不是99的只有3個，1,、4和9，而這三個數字對應在$B$3:$B$11這個範圍里，正好就是"A"對應的位置，為了依次得到這三個數字，就需要small這個函數了，因為公式需要橫向拉動，所以用了column作為small的第二參數。到這一步，index的結果就不需要多講了。

通過這個思路的分析，需要特別注意的幾個地方就是數組維度的對應，也就是if裡面的第一參數這個條件數組不需要多說，第二個參數的row包含的個數應該和條件範圍是一樣多的（並且應該絕對引用），第三參數這個在本例來說用10都可以，因為數據源只有9個，如果多的話可以用9^9（9的9次方=387420489）這個應該是足夠大了。當然在if裡面用column效果也是一樣的，只不過row看起來簡潔一點，千萬不要和small的第二參數混淆了。

思路解釋起來是很繞口的，更好的方法是模擬一個少一點的數據源，不超過10行的都可以，然後使用公式求值或者f9功能一步一步看看公式結果是怎麼變化的，從而去了解公式的計算過程。

公式模型一：INDEX(返回區域,SMALL(IF(查找區域=查找值,ROW(查找區域),99,COLUMN(A1)))

套用時需要看清案例一的特點：查找值在查找區域內是間隔出現的。

公式模型二：預備知識：IF、ROW、COUNTIF、OFFSET、MATCH等函數的基本用法。

首先觀察這個題目和第一個例子有什麼區別？查找值在查找區域是連續的，結果是向下拉的，也許還有別的差異。那麼可以用公式模型一來完成嗎？答案是肯定的，E2公式如下：

=INDEX($B$3:$B$19,SMALL(IF($A$3:$A$19=$D$3,ROW($1:$17),99),ROW(A1)))，下拉即可，注意還是數組的，可以結合這個例子再去領會一下公式模型一的思路。

其實這個例子完全可以不用數組公式來處理，因為和例一最重要的一個差別就是a列的重複值是連續的，可以看做例一的特殊情況。

把上面這個公式下拉多行會發現有亂碼出現，如果要排錯的話，還是可以用iferror，當然也可以用if countif來實現，排錯公式如下（都是數組哦）：

=IFERROR(INDEX($B$3:$B$19,SMALL(IF($A$3:$A$19=$D$3,ROW($1:$17),99),ROW(A1))),"")或者

=IF(ROW(A1)>COUNTIF($A$3:$A$19,$D$3),"",INDEX($B$3:$B$19,SMALL(IF($A$3:$A$19=$D$3,ROW($1:$17),99),ROW(A1))))

用if countif來排錯看起來是長一點，但是這個思路需要明白，關鍵就是row和countif的值做比較，而這個應用也是很有用的，下面就來看公式模型二，這個公式不需要數組哦：

=IF(ROW(A1)>COUNTIF(A:A,D$3),"",OFFSET(B$2,MATCH(D$3,A:A,0)-3 ROW(A1),))

其實這個公式的核心在於offset的作用了，尤其是offset的第二個參數，使用了match和row來共同得出行的偏移量，如果明白了offset在這裡的作用，那麼公式模型二也就沒什麼難以理解的了。

公式模型二：IF(ROW()>COUNTIF(),"",OFFSET(,MATCH(),,,) 關鍵在於offset第二參數的構造。

注意:此公式不好之處在於查找區域必須排序,好處在於是普通公式運算速度快。

公式模型三：

對於一對多查找的問題，基本就幾種情況，如果不用輔助列的話，使用公式模型一都可以解決，如果可以排序的話，推薦使用非數組的模型二來解決，但是方法不僅限於這兩種，如果可以使用輔助列的話，僅用vlookup都可以實現，而且無需數組，還是用實例二的數據，但是在數據源里我們加一個輔助列，如下圖：

輔助列的公式為，A3：=COUNTIF($B$3:B3,$E$3)，

結果列公式為，F3：=IFERROR(VLOOKUP(ROW(A1),$A$3:$C$19,3,0),"")

無需排序，無需數組，會用vlookup就能解決這類問題，推薦初學者學習掌握。

公式模型四：

還是用模型二里用到的例子，在介紹一種方法，使用INDIRECT、SMALL、IF、ROW這幾個函數來完成。

=INDIRECT("b"&SMALL(IF($A$3:$A$19=$D$3,ROW($3:$19),99),ROW(A1)))

數據源不需要排序，是數組公式，這裡是把模型一的index換成了indirect函數，了解兩個函數引用區別的話自然也就明白模型四的原理了。

這裡需要介紹另一種排除錯誤的方法，就是t函數，上面的公式可以變成

=T(INDIRECT("b"&SMALL(IF($A$3:$A$19=$D$3,ROW($3:$19),99),ROW(A1))))三鍵結束下拉即可

=INDIRECT("b"&SMALL(IF($A$3:$A$19=$D$3,ROW($3:$19),99),ROW(A1)))&""而在公式後面加&""也是一種排錯的方法。

可以在前面的公式里使用一下這兩種排錯方法，需要說明一點的是，一般是結果為文本型的這兩個方法時候可以用。

當然，對於發燒友級別的朋友來說，還有其他的思路，不過相對就很難理解了，這裡不去一一介紹了，能夠理解並且能夠套用上面說的一個或者幾個公式模型就已經很不錯了，由於水平有限，以上解釋中難免有不足之處，對於發現的問題希望各位表友可以予以指正，這裡不勝感激。同時對於解釋不甚理解的朋友也可以加入討論群進行詢問。

有任何疑問歡迎加qq群交流：EXCEL基礎學習群 259921244