龘龘221167238

當決定使用哪種垂直查詢公式時，大多數Excel專家認為INDEX MATCH是比VLOOKUP更好的公式。然而，許多人仍然使用VLOOKUP，因為它是一個更簡單的公式。這個問題的一個主要驅動因素是，大多數人仍然不能完全瞭解從VLOOKUP換到INDEX MATCH的好處，沒有這樣的理解，他們不願意花時間去學習更復雜的公式。這篇文章的目的是詳細描述使用INDEX MATCH的所有好處，並說服你應該使用INDEX MATCH專門用於所有的垂直查找。

如果您不知道如何使用INDEX MATCH，請學習相關的知識。

動態列引用可以避免更少的錯誤

INDEX MATCH和VLOOKUP之間的關鍵區別在於VLOOKUP需要靜態列引用，而INDEX MATCH使用動態列引用。使用VLOOKUP，大多數人將輸入一個特定的靜態數字，以指示他們要從哪個列返回。當您使用INDEX MATCH時，該公式允許您手動選擇要從哪個列中返回數據。

導致犯更少錯誤的原因是因為當您遵循INDEX MATCH語法時，您可以直接單擊包含要返回的值的字段

使用VLOOKUP，您必須指定查找範圍表數組，然後指定列引用（就是返回列的數值），以指明要從中提取數據的列。

當你有一個很大的表數組，在拖動查找範圍的時候，你會看到數據範圍有幾行幾列，你可以看到你想返回的數據列在數據範圍的那一列，然後記下來，再填寫公式裡面，這是一個小小的差異，但這個額外的步驟無疑會有可能導致錯誤，當您使用INDEX MATCH時，不需要這樣的計數。

插入列不影響結果

使用INDEX MATCH超越VLOOKUP的最大好處是，使用INDEX MATCH，您可以在表數組中插入列，而不會影響使您的查找結果。任何時候使用大型數據集，您很可能需要返回編輯，並有可能插入一個新的列。使用VLOOKUP，任何插入或刪除列將改變公式的結果。

以下列VLOOKUP示例。在這裡，我們設置了從數據表中返回State值的公式。因為它是一個VLOOKUP公式，我們指定返回第4列的數據。

如果我們在table數組的中間插入一個列，那麼新的結果就是“Seattle”;我們不再能從State列提取正確的值，所以必須更改列引用。

再來看下插入一列，用INDEX MATCH公式是否還能得到正確的解決，答案是肯定的，沒問題。

INDEX MATCH具有插入列免疫功能，因此您可以插入和刪除列，而不用再去更新每個關聯的查找公式。

更容易拖動和複製

在使用大型數據集時，很少只需編寫一個查詢公式就完事了;您可能需要將公式拖放到多個單元格以執行多個查找。例如，假設對於特定的ID，我想從表中返回一系列與表中顯示相同的值。當我嘗試拖動並複製標準的VLOOKUP公式（在查找值和表數組上都有一個引用鎖）時，查找不起作用，因為它只為每個條目提取相同的值。這個缺陷再次是由VLOOKUP引起的，需要一個特定的列引用輸入作為你的返回值，你需要為city指定列引用為3，state指定列引用為4.

使用INDEX MATCH，因為您可以將返回列設置為float（不用美元符號鎖定它），當您複製公式時，返回列將移動，為您顯示不同的字段。

沒有數組限制

VLOOKUP的另一個關鍵限制是，它需要您指定一個方形表數組，其中列引用不能超越，一般當您將新的字段附加到您的原始數據表之外的數據集時會遇到這種情況，來看下面的例子：

如果我在這個數據集追加一個新列，就不能用我原來的VLOOKUP公式來自返回新加列的值。如果我把列引用改為“6”，但是我的原來表數組寬5列，所以返回一個錯誤。為了使公式正常的工作，就不得不更新每次添加新列後指定新的表數組，這樣操作起來比較麻煩。

用Index+Match 公式，就沒有這個問題。

自右向左查找（反向查找）

使用INDEX MATCH的主要優點之一是能夠在數據表的右側創建查找鍵。 通常通過在原始數據集中的字段上運行計算來創建新的查找鍵。 這些鍵代表INDEX MATCH語法中的查找列。 當使用INDEX MATCH時，您可以將這些新的查找鍵附加到表的右側，並執行從右到左的查找來拉取要返回的值。 INDEX MATCH語法不關心您的查找列是否位於返回列的左側或右側。

使用VLOOKUP，因為您只能執行從左到右的查找，所添加的任何新查找鍵必須位於原始表數組的左側。 因此，每次添加新的查找鍵時，必須將整個數據集向右移動一列。 這不僅令人討厭，而且還會影響您在電子表格中創建的現有公式和計算，當然Vlookup可以執行從左到右的查找，會用到數組公式的公式。

可以有HLOOKUP的功能

這可能對於大多數用戶不是多大的事，但它仍然值得一提。 VLOOKUP限制您僅在表數組上執行垂直查找。 例如，我無法在數據集頂部查找值，並執行水平查找。 為了可以水平查找，我得用HLOOKUP。但是使用INDEX MATCH完全可以做水平查找。

較低的處理需求

使用INDEX MATCH的處理優勢在最新版本的Excel中被邊緣化，因為軟件的處理能力已經大大擴展。但是給每個人一個簡短的歷史課，我會詳細說明這個好處。在某些Excel的舊版本中，該軟件具有明顯的處理限制，如果您構建了一個巨大的數據集，那將是顯而易見的。在某些情況下，我需要查找數千行的值，以便我可以在一個大表中附加一個新列。一旦我添加了所有這些公式，軟件將凍結並花費幾分鐘計算返回值。我最終不得不使用INDEX MATCH替換我的VLOOKUP公式，以加快計算速度。

這個差異的原因其實很簡單。 VLOOKUP需要更多的Excel處理能力，因為它需要評估您選擇的整個表格數組。使用INDEX MATCH，Excel只需要考慮查找列和返回列。使用較少的絕對單元格來考慮，Excel可以更快地處理這個公式。

再次，請注意，由於Excel的處理能力進行了改進，這種好處可能不再顯著。但是，如果由於任何原因需要在電子表格中運行數千個查詢公式，請確保使用INDEX MATCH。

結論

使用INDEX MATCH的一個缺點是顯然難以學習，更難記住。 但是，通過閱讀有關如何使用公式的簡單教程，可以輕鬆解決這一障礙。

我現在使用INDEX MATCH專用於我的所有垂直查找。 不是因為它是一個更好的公式，而是它幫助我記住更復雜的語法。 一旦你做開始用並且體驗到使用INDEX MATCH的好處，我保證你不會回到VLOOKUP。

Excel學習筆記

Excel函數公式：最強查找引用INDEX+MATCH實用技巧解讀

VLOOKUP雖然是大家最常用的查找引用函數，但在很多場景下都不足以滿足我們的實際工作要求，例如從右向左查找，多條件查找等，此時需要其他函數的配合來完成。

Excel函數公式：最強查找引用INDEX+MATCH實用技巧解讀

INDEX+MATCH法：

Excel函數公式：最強查找引用INDEX+MATCH實用技巧解讀

Excel函數公式：最強查找引用INDEX+MATCH實用技巧解讀

Excel函數公式：最強查找引用INDEX+MATCH實用技巧解讀

Excel函數公式：最強查找引用INDEX+MATCH實用技巧解讀

Excel函數公式：最強查找引用INDEX+MATCH實用技巧解讀

Excel函數公式：最強查找引用INDEX+MATCH實用技巧解讀

Excel函數公式

剛學Excel函數時，相信大部分表親，都奉Vlookup為神函數，最基礎，也最常用，甚至天天用。確實，Vlookup在Excel中的地位是不可否認的，不會用，那就別說你用過Excel函數！

只是，在很多情況下，Vlookup用起來不夠靈活，也略顯麻煩。而，Index+Match就比Vlookup靈活多了！

Index+Match用法

用法：

INDEX(區域, 第幾行);

MATCH(找什麼, 在哪裡找, 查找方式)；

組合起來用，也不難，就是一個函數鑲嵌而已，如下所示：

邏輯也不難，用match函數找到符合條件的單元格位置，然後用index找出該位置上的其他單元格。

查找多維區域，照樣不難，如下：

兩個match函數定位出行列，然後一個index搞定。

發現沒，Index+Match的靈活，就在於，TA們不在乎你的目標單元格是在條件列的左側還是右側，而，Vlookup函數，目標值應該在右側，若是找左側單元格，則需要使用數組，這樣就略顯麻煩了。

普及下Vlookup函數基礎用法

用法：vlookup( 找什麼, 查找的範圍, 返回哪一個值, 精確查找還是模糊查找 )

這就是我更喜歡Index+Match組合的原因。

希望這組函數的用法能幫到你！

Excel技巧精選

首先，該問題有哇眾取寵之嫌，在日常工作中，因為VLookup函數更加簡單、易用、直接，實際上VLookup函數的應用廣泛程度是Index+Match函數組合的10倍。

說Index+Match函數比VLOOKUP函數好用，是因為Index+Match組合比單純使用VLookup函數更加靈活，功能更加強大，而且在多列數據匹配查找時能通過合適的方法來改善計算效率。

關於這兩個函數怎麼使用，前面的答者給出了非常詳細的描述，在此不再贅述。以下我從兩個方面來進一步說明Index+Match函數組合比VLookup函數好用的地方。

一、結合“表格”（超級表），跨表引用巨輕鬆——那些你曾經看不懂的公式，原來是這麼好用！

大海 Excel到PowerBI

很多時候，我們在寫公式時，都需要進行跨表的數據引用，比如要通過VLookup函數引用其他表的數據參與計算，或做匹配等，經常要拿著鼠標到處找需要引用的那一列或那個區域，尤其當數據表很大、列數很多的時候，找起來更是費勁，但自從你將普通的錶轉成了"表格"（超級表：見文章《用了Excel這麼久，還有辣麼多人不知道“表格”！》），你就輕鬆了——因為你可以直接在寫公式的時候得到相應的提示！

以下將通過一個簡單的例子來見證"奇蹟的時刻"。

• Step01-在成績表裡插入新的列"學生姓名"

只要在成績表的右邊，標題行上輸入"學生姓名"，然後回車，Excel將自動生成一個新的列，如下圖所示：

• Step02-輸入公式，根據提示快速選擇表

接下來我們開始輸入公式"=index(stu……"

See？student表隨著公式的輸入出來了！

Excel就是這麼牛B，直接給你提示！

此時，如果還有多個表的話，我們可以通過鍵盤的上下箭頭進行表的選擇，當選到我們需要的表時，按Tab鍵即選中該表進入公式。

• Step03-在公式中快速選擇要引用的列

引用表後，我們還要指定要引用的列，這時，我們在表名後面輸入"["——真正見證奇蹟的時候！student表中所有的列名都出來了！

此時，同樣地，如果列很多的話，我們可以直接輸列名，或者可以通過鍵盤的上下箭頭進行列的選擇，當選到我們需要的列時，按Tab鍵即選中該列進入公式，然後輸入"]"完成列的引用。

• Step04-在公式中僅引用某列的當前行

為完成"學生姓名"的提取，我們繼續，到match的時候，我們的lookup_value可是要用當前行的值，怎麼辦？——當然沒問題，在"["後再輸入"@"，提示還在！是的，在Excel的"表格"中，對"[列名]"表示對整列的引用，"[@列名]"表示對該列當前行的引用，如下圖所示：

• Step05-公式的自動填充

公式全部輸入完畢後，回車，該公式將自動填充到該列的所有單元格中，不需要再動鼠標了——就是這麼方便！如下圖所示：

至此，通過在公式輸入時得到的提示，快速地實現了跨表的引用，當你開始習慣了這種輸入的方法後，你將會發現原來通過鼠標到處找數據的過程是多麼的痛苦，尤其是表很多、列很多的時候！

二、用Index+Match函數提升多列大量數據匹配查詢效率

VLookup是Excel中進行數據匹配查詢用得最廣泛的函數，但是，隨著企業數據量的不斷增加，分析需求越來越複雜，越來越多的朋友明顯感覺到VLookup函數在進行批量性的數據匹配過程中出現的卡頓問題也越來越嚴重。

那麼，在數據量較大，需要批量進行數據匹配查找的情況下，是否有辦法進行適當的改善，以提高數據的匹配查找效率呢？

以下用一個例子，分別對比了四種常用的數據匹配查找的方法，並在借鑑PowerQuery的合併查詢思路的基礎上，提出一個簡單的公式改進思路，供大家參考。

一、測試數據

本次測試涉及數據概況及要求如下：

• 訂單表21581行（含標題）

• 訂單明細表17257行（含標題）

• 要求將訂單表中的“訂單ID”、“客戶”、“僱員”、“訂購日期”、“到貨日期”、“發貨日期”等6列數據匹配到訂單明細表中。

如下圖所示：

二、4種數據匹配查找方法

1、VLookup函數，按常用全列匹配公式寫法如下圖所示：

2、Index+Match函數，按常用全列匹配公式寫法如下圖所示：

3、Lookup函數，按常用全列匹配公式寫法如下圖所示：

4、Power Query合併查詢，按常規表間合併操作如下圖所示：

三、4種方法數據匹配查找方法用時對比

經過分別對以上4中方法單獨執行多列同時填充（Power Query數據合併法單獨執行數據刷新）並計算時間，結果如下表所示：

從運行用時來看：

• VLookup函數和Index+Match函數的效率基本一樣；

• Lookup函數在大批量數據的查找中效率最低，甚至不能忍受；

• Power Query的效率非常高。

四、對公式法的改進

我們在前面用VLookup、Index+Match寫公式的思路則是對每一個需要取的值，都是一次單獨的匹配和單獨的取值。也就是說，每次為了查找到一個數據，都需要從訂單表的2萬多條數據裡搜索一遍，這種效率自然會很低。

那麼，如果我們在公式中可以做到只匹配一次，後面所需要取的數據都跟著這次匹配的結果而直接得到，那麼，效率是否會大有改善呢？

再回頭看Index+Match結合的公式，其中，Match函數用於確定所需要查找內容的位置，而Index用於提取該位置相應的值！

那麼，如果我們只用Match一次把位置先找出來，後面所有的列都直接用這個位置去提取相應的值，會怎樣？

於是，首先用Match函數構建一個輔助列，用於獲取匹配位置，如下圖所示：

然後，通過Index函數，直接根據輔助列的位置從訂單表裡讀取相應的數據，如下圖所示：

經執行公式的批量填充，結果：

• 用時約17秒，約為直接使用VLookup函數或Index+Match函數組合公式（約85秒）的五分之一！

五、結論

在批量性匹配查找多列數據的情況下，通過對Index和Match函數的分解使用，先單獨獲取所需要匹配數據的位置信息，然後再根據位置信息提取所需多列的數據，效率明顯提升，所需匹配提取的列數越多，效率提升越明顯。

當然，使用公式的方法，即使在一定程度上進行改進，和Power Query相比仍然有很大的差距。因此，在數據量較大，數據處理較為複雜的情況下，建議使用Power Query來進行。

以上提供了Index+Match函數結合超級表以及分拆使用提高效率的兩種應用方法，在很大程度上體現了Index+Match比VLookup函數好用的地方，您可以根據實際情況選擇使用。

私信“材料”直接下載系列訓練材料】

• 【Excel必備基礎小動畫】

• 【60+函數彙總案例】

• 【數據透視基礎精選10篇】

• 【Power Query入門到實戰80篇】

• 【Power Pivot 基礎精選15篇】

我是大海，微軟認證Excel專家，企業簽約Power BI顧問

讓我們一起學習，共同進步！

【您的關注和轉發鑄就我前行的動力！謝謝支持！】

Excel到PowerBI

強大的index+match函數組合，勝過VLOOKUP函數

index函數語法：

index（單元格區域，行號，列號）

match函數語法：

match（查找值，查找區域，查找方式）

1. index+match單條件查找

在I2單元格輸入公式：

=INDEX($D$2:$D$10,MATCH(H2,$B$2:$B$10,0))

公式解析：

index第一參數選擇要返回的職務區域，使用match函數查找$B$2:$B$10小螃蟹所在的行號 ，index第三個參數省略默認為1

2. index+match多條件查找

在H2單元格輸入公式：

=INDEX($C$2:$C$13,MATCH(F2&G2,$A$2:$A$13&$B$2:$B$13,0))

數組公式，按Ctrl+Shift+Enter鍵結束公式

公式解析：

使用match(F2&G2,$A$2:$A$13&$B$2:$B$13,0)查找值將小螃蟹和空調連接，查找區域將姓名列和產品列連接為一列，即可查找到對應的行號

3. 提取唯一值

在D2單元格輸入公式：

=INDEX($A$2:$A$13,SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($1:$12),ROW($1:$12),14),ROW(A1)))

數組公式，按Ctrl+Shift+Enter鍵結束公式

公式解析：

MATCH($A$2:$A$13,$A$2:$A$13,0) 因為match查找是返回第一個符合條件的值，

所以結果是{1;2;1;4;5;4;1;2;4;1;5;5}，再使用if函數後的結果{1;2;14;4;5;14;14;14;14;14;14;14} 利用SMALL函數提取第一個最小值，第二個最小值...

excel函數教程

都說沒用過Vlookup，那一定沒用過Excel函數。直接體現出Vlookup函數在Excel中的地位。Vlookup函數是Excel的入門函數，也是使用頻率最高的函數。

只是，Vlookup在很多時候，如：自右向左查詢、橫向查詢、動態查詢等情況下，Vlookup略顯複雜。而此時，Index+Match組合就顯得靈活多了。

Vlookup

先來看看查詢函數vlookup，這是Excel中最常用也最基礎的函數。

公式用法：VLOOKUP（找什麼，在哪找，位於區域的第幾列，精確還是模糊找）

Index+match

INDEX(array, row_num, [column_num])

MATCH(lookup_value, lookup_array, [match_type])

更多函數

更多函數的詳細用法，關注我哦！

Excel大全

VLOOKUP函數各位小夥伴應該都能熟練使用。對於數據匹配索引有很好的處理效果。最近很多小夥伴經常看到INDEX+MATCH函數組合來完成數據匹配的案例，表示想學習如何使用。你是不是也想一探究竟呢？

今天龔老師就跟大家分享一下INDEX+MATCH使用方法。

舉例

如上圖：我們需要在G列中返回商品名稱對應的數量。

VLOOKUP操作

公式：=VLOOKUP(F2,$C$1:$D$7,2,0)公式含義不用解釋了吧！

INEDX+MATCH操作

公式：=INDEX($D$1:$D$7,MATCH(F2,$C$1:$C$7,0))

含義：第一步通過MATCH函數求出商品名稱在原表中所在的行，然後通過INDEX函數索引出此行對應的數值。

Excel講堂

數據查找匹配每個職場人的必備的技能，大眾情人 VLOOKUP函數可謂運用廣泛！但是有些老司機可能會發現，這個函數在使用過程中會有諸多限制，比如，只能從左往右查詢，數據格式要求非常高等等，這些問題是否困擾你?今天，技巧君介紹INDEX+MATCH組合函數，輕鬆解決VLOOKUP函數的缺陷！

原理解析

我們先來看一個熟悉的場景，在電影院 我們是怎麼找到位置的：

如圖所示，3排7座是往後數3排，再往右數7個位置就找到了

在EXCEL中，我們也是這樣對單元格進行排"位置"的，我們把“排”稱為“行”，用數字標記，把“座”稱為列，用字母標記；

與電影院不同的是，EXCEL中"行"要遠多於“列”,並且是“列”號在前，上面所示的“3排7座”，我們用EXCEL中的標記方式就是：“G3”

上圖中屬性表中，我們可以看到，第一列是姓名，第二列是國籍，第三列是武力，第四列是智力；

如果我們要得到某個武將的武力值，由於武力固定在第三列，我們只需要知道武將在屬性表第幾行就可以了！

例如：關羽在屬性表的第3行，那麼他的武力值是C3單元格中的98。

INDEX+MATCH函數就是基於這個原理來使用的，INDEX的是你和EXCEL溝通的橋樑，告訴EXCEL你需要第幾行第幾列的數據，它返回給你；MATCH幫你找到武將的行號。

例如：關羽在第幾行我們不知道，那麼MATCH函數告訴你：MATCH("關羽\

Excel精選技巧

Index+Match組合函數替代VLOOKUP函數可以解決：當excel數據表包含的行和列比較多時，查找值時不用再去數行數或列數。

1、首先我們來看vlookup函數。

vlookup函數公式為：

=VLOOKUP（要查找的值、要在其中查找值的區域、區域中包含返回值的列號、精確匹配或近似匹配 – 指定為 0/FALSE 或 1/TRUE）。

其中第二個參數，查閱值應該始終位於所在區域的第一列。例如下面一張數據表，查找重慶的GDP。

公式為: =VLOOKUP("重慶\

Office辦公君

其實vlookup本身可以完成正常向右查找，但是很多人卻不太熟悉。都認為vlookup無法完成逆向查找，隨意才會有很多人使用index+match來完成逆向查找。個人認為index+match的好處在於不用去選大片單元格區域，只需要選中指定的列就可以。使用過程中不會有左右或者正向逆向區別，比較靈活。關於逆向查找還可以使用lookup結合2分法原則完成。

舉例：lookup(1,0/(A1=B:B),C:C)

---

分享到:

---

關鍵字: Excel 好用 職場

---