EXCEL一對多條件查找顯示多個結果（INDEX SMALL IF ROW函數組合）

【INDEX SMALL IF ROW】組合函數：

①難度：☆☆

②作用：實現查找時返回多個符合條件的結果。

③結果放在行的寫法：

INDEX(結果列,SMALL(IF(條件,ROW(條件列),4^8),COLUMN(A1)))&""

④結果放在列的寫法：

INDEX(結果列,SMALL(IF(條件,ROW(條件列),4^8),ROW(A1)))&""

⑤結束鍵：數組公式須同時按CTRL SHIFT ENTER三鍵結束。

這個組合函數在簡單的報表裡用得不頻繁，所以每到用時方恨練得少。接下來直接舉「栗子」啦，想要進階的同學多練習幾遍，能從頭到尾自己打代碼把公式寫下來才算真正掌握！

溫馨提示：書寫公式必須把輸入法調整到英文半形狀態，否則顯示出錯。

例：A、B、C列是源數據，要求在E列提取A列「省份」數據（重複的只保留一個），並查找各省份對應的城市（在B列找），把結果放到同一行不同單元格里。

01

原始表

02

結果表

03

操作過程

04

步驟分解

①在E列提取A列省份（重複的只留一個）

方法1：複製A列粘貼到E列，EXCEL2007以上版本可直接點菜單欄「數據」，然後點擊「刪除重複項」，簡單粗暴就OK了。

方法2：INDEX MATCH（上例用的是此法，詳細的下次再探討）

E2輸入公式

=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($A$2:$A$13)-1,ROW($2:$13),4^8),ROW(1:1)))&""

按CTRL SHIFT ENTER三鍵結束，下拉。

②在F2單元格里為E2省匹配B列中對應的第1個城市

F2輸入公式

=INDEX($B:$B,SMALL(IF($A$2:$A$13=$E2,ROW($A$2:$A$13),4^8),COLUMN(A1)))&""

三鍵結束。

③把F2的公式右拉，分別匹配E2省對應的第2、3、4、5個城市...直到顯示空白。

④把E2右邊存放有公式的單元格選中後下拉，為E3、E4單元格里的省份匹配到對應的各個城市。

⑤附加題：為省份匹配非省會的城市

公式的寫法同上面一樣道理，只是增加了一個條件——C列等於"否"。

那就在第②點的公式里插入一個新條件

=INDEX($B:$B,SMALL(IF(($A$2:$A$13=$E2)*($C$2:$C$13="否"),ROW($A$2:$A$13),4^8),COLUMN(A1)))&""

同樣三鍵結束。記得哦，增加條件後該增加的括弧()也得成對增加哈~

⑥同理，如有更多條件要求，比照第⑤點用*星號連接插入相關條件即可。

05

公式翻譯

先安利F9這個功能鍵：如果公式很長，在編輯欄抹黑某段公式，按F9可以得到公式結果解析。

尤其像INDEX SMALL IF ROW這種組合函數，由多個函數嵌套組合在一起，得先理解各個函數的語法結構及功能作用，才能理解整個組合函數的工作原理。上圖瞧瞧：

1-1

1-2

2-1

2-2

遇到複雜的公式，可以按F9解析難理解的中間步驟。現在先來翻譯F2單元格的公式。

INDEX($B:$B,SMALL(IF($A$2:$A$13=$E2,ROW($A$2:$A$13),4^8),COLUMN(A1)))&""

翻譯：

IF(A列省份若是「廣東」,返回那一行的行號,不是「廣東」則返回一個很大的數65536)

SMALL(IF()得到的行號數組從小到大排列,第幾小的行號)

INDEX(城市列,SMALL()得到的行信息)

由此可以得到

INDEX(城市列,A列省份是「廣東」對應的第1個城市)

INDEX(城市列,A列省份是「廣東」對應的第2個城市)

INDEX(城市列,A列省份是「廣東」對應的第3個城市)

INDEX(城市列,A列省份是「廣東」對應的第N個城市)

此文於2017.09.01首次發表，今天只是修改標題、簡介及部分排版，內容未改動。希望親愛的你有所收穫???

Cya~~

聲明
推薦閱讀：