一個萬金油函數(Index Small If Row)

  很多人在Excel中用函數公式做查詢的時候,都必然會遇到的一個大問題,那就是一對多的查找/查詢公式應該怎麼寫?大多數人都是從VLOOKUP、INDEX MATCH中入門的,縱然你把全部的多條件查找方法都學會了而且運用嫻熟,如VLOOKUP和&、SUMPRODUCT、LOOKUP(1,0/….,)但仍然只能對這種一對多的查詢望洋興嘆。

今天講的INDEX SMALL IF ROW的函數組合,就是解決一對多查詢的一種通式,如果你能掌握,那在Excel裡基本上就沒有什麼查詢你是做到不了的了(除了INDIRECT RC引用)。

如下圖,為示例數據和查詢要求。根據F2單元格的品名,分別查詢,訂單號和數量。

一個萬金油函數(Index Small If Row)

答案:

E5:=IFERROR(INDEX(A$2:A$15,SMALL(IF($B$2:$B$15=$F$2,ROW($1:$14),4^8),ROW(A1))),””),Ctrl Shift Enter,三鍵下拉右拉。

公式簡析:

1、最裡面一層的If函數,IF($B$2:$B$15=$F$2,ROW($1:$14),4^8),判斷源數據的B列是否等於F2,如果是就返回B2:B5區域對應的第幾行,否則,就返回4^8。就是4的8次方冪,即65,536,這在xls格式文檔中,相當於最大行號,在xlsx格式則不然。

2、Small函數,SMALL(IF($B$2:$B$15=$F$2,ROW($1:$14),4^8),ROW(A1)),取If判斷結果的第一小的值,下拉就是,取第二小的值。

3、Index函數,INDEX(A$2:A$15,SMALL(IF($B$2:$B$15=$F$2,ROW($1:$14),4^8),ROW(A1))),就是返回A2:A15區域某一行的值。

4、最外層的Iferror函數,容錯函數,如果Index取得值為錯誤值,則返回空值。

通過這個函數我們可以看下,根據不同的品名,查詢的數據。

一個萬金油函數(Index Small If Row)