Excel函數學習之查詢函數INDEX()的使用方法

本篇文章帶大家瞭解一下INDEX函數！INDEX也是查詢家族的一員，由於他強大的座標定位功能，有時候VLOOKUP都是要靠邊站呢！

一、認識INDEX函數

Index函數：在給定的單元格區域中，返回特定行列交叉處單元格的值或參照。

函數結構：index（單元格區域,行號,列號）

區域，行號，列號，很像通過座標瞄準打靶呀。就像下面動圖這樣，找到列，找到行，單擊就打中了！

我們想找到「囡」，可以看到其座標是行4，列3。

所以公式：=INDEX(B2:G11,4,3)就能得到「囡」。

二、INDEX函數基礎用法

1.單行、單列中提取數值：只需一個座標值

如果給定的區域是單行或者單列，那座標就不需要兩個數位了，只需要一個即可。

譬如我們現在需要在F17中從A17:A21中獲得「李惠」。

輸入公式：=INDEX(A17:A21,2)即可。

又譬如我們需要在G17中從A18:D18中取得李惠的基本工資。

輸入公式：= INDEX(A18:D18,4)即可。

2.從一個多行多列區域提取數值：必須行列兩個座標值

這點就不列舉了。前方找「囡」字就是這樣的。

從上面的例子可以看出，INDEX通過座標返回數值，像精確制導的導彈，指哪打哪（返回哪）。不過，純粹的人工查座標再輸入座標，太不符合「現代化」了。實際操作中，資料往往都是幾十列，幾十行甚至上萬行的都有，這個時候我們再根據需要人工去查座標輸入座標，就太不現實了。所以INDEX需要助手，需要組團才能打天下。

三、INDEX實戰用法

1.與小助手COLUMN和ROW組團：實現半自動查詢取值

（1）與COLUMN組團可以連續返回同行多個資料

譬如我們需要從表中連續獲取工號C23的姓名、年齡、入職時間。

在資料區域A17:E21中，工號C23位於第3行，姓名、年齡、入職時間的列數從左到右是分別是2、3、4。我們可以用COLUMN（B1）來取代2、3、4實現半自動效果。公式如下：

=INDEX($A17:$E21,3,COLUMN(B1))

然後右拉填充即可。

得到的入職時間是數位，修改格式為短日期即可。

（2）與ROW組團可以連續返回同列多個資料

譬如下面，我們用公式：=INDEX(A$17:E$21,ROW(A3),2)下拉填充獲得三個工號的姓名。

（3）與COLUMN和ROW同時組團

譬如我們可以用公式：=INDEX($A$17:$E$21,ROW(A3),COLUMN(B1))右拉下拉填充獲得工號C23、C08、C10的姓名、年齡、入職時間。

通過與COLUMN和ROW組團，實現了半自動效果。只要是連續、有規律的取值，都可以用INDEX+ROW+COLUMN實現。

譬如我們需要隔行隔列取值，獲得工號C15、C23、C10的姓名、入職時間。公式是：

=INDEX($A$17:$E$21,ROW(A1)*2-1,COLUMN(A1)*2)

然後右拉下拉填充即可。

半自動比完全的人工查座標輸入座標簡便多了，但之所以叫半自動那就是還需要人工去尋找資料的規律。如果取值的資料規律複雜或者沒有規律，我們就無法半自動了。這個時候，就需要與大助手MATCH組團進行全自動工作。

2.與大助手MATCH組團：實現全自動查詢取值

（1）INDEX+MATCH組團

下面的資料查詢規律是亂的，我們不用自己去找規律，把一切都交給MATCH就好了。

在C28中輸入公式：

=INDEX($A$17:$E$21,MATCH($B28,$A$17:$A$21,0),MATCH(C$27,$A$16:$E$16,0))

然後右下下拉填充公式即可。

用MATCH函數根據條件在固定區域中查詢行、列位置完全取代了人工查詢座標或者資料規律，實現了全自動。

（2）INDEX+MATCH與VLOOKUP+MATCH的區別

輸入公式：

=VLOOKUP($B28,$A$17:$E$21,MATCH(C$27,$A$16:$E$16,0),0)

右拉下拉填充即可。

從公式長度來說，VLOOKUP+MATCH比INDEX+MATCH簡潔。那我們為何還需要INDEX+MATCH呢？原因就在於INDEX函數只要收到行列座標值就可以查到資料，根本不存在什麼正向查詢、反向查詢的區別。VLOOKUP就不行了，預設情況下它只能實現正向查詢，也就是在查詢區域裡只能是從左往右查詢，而不能從右往左查詢。VLOOKUP要想實現從右往左的反向查詢，就需要藉助IF函數或者CHOOSE函數構建新的查詢區域。

譬如我們需要通過姓名查工號，如下：

採用INDEX+MATCH組合直接寫公式：=INDEX(A$17:B$21,MATCH(G17,B$17:B$21,0),1)，然後下拉即可

如果用VLOOKUP查詢，因為是反向查詢，就需要用IF函數重新構建查詢區域，公式就變成：

=VLOOKUP(G17,IF({1,0},B$17:B$21,A$17:A$21),2,0)

所以，比較起來，正向查詢的時候，用INDEX+MATCH和VLOOKUP+MATCH都可以，VLOOKUP+MATCH相對更簡潔；反向查詢的時候，則用INDEX+MATCH最簡潔，尤其是反向查詢區域有三列、四列資料的時候，INDEX+MATCH是最佳選擇。

好了，回答了函數課堂2中的問題後，我們繼續看INDEX的實用組團。

3.與特邀嘉賓SMALL和IF加上大小助手共同組團：實現一對多查詢

組團後的公式格式是=INDEX(查詢區域,SMALL(IF(),ROW()),MATCH())

譬如下方的動圖所展示的那樣：

公式很長：

=INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0))

套上防錯的IFERROR函數，就更長了：

=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0)),"")

這樣的組合公式，我們又常稱它為萬金油公式，主要用於一對多的查詢。

Ok，INDEX的實戰用法我們就介紹這麼多。INDEX函數具有利用座標精確取值的優勢，但自身缺少根據條件自動查詢座標的功能，是個瘸子，所以實戰中它需要助手協助來查詢座標。它是函數中的精確制導導彈，它是瘸子，一個強大的瘸子！

相關學習推薦：

以上就是Excel函數學習之查詢函數INDEX()的使用方法的詳細內容，更多請關注TW511.COM其它相關文章！