資料查詢匹配每個職場人的必備的技能，大眾情人 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("關羽",A1:A10,0)，它就會告訴你關羽在第幾行，武力屬性在第三列，透過公式INDEX(A1:D10,MATCH("關羽",A1:A10,0),3)告訴EXCEL你需要的資料在哪裡，它就會返回給你。

以上是INDEX+MATCH函式的核心思想，瞭解了原理，你對這對搭檔是否映像清晰了呢？下面來看具體的操作吧！“道”與“術”結合，修為自然增長快！

INDEX函式

功能：返回引用中指定單元格或單元格區域的引用（官腔聽不懂吧？微軟工程師內心：就喜歡你們這種看不懂還認為我很厲害的樣子），通俗來說，就是告訴EXCEL你需要第幾行第幾列的資料，它給你找出來拿給你！

語法：INDEX(引用資料區，第幾行，[第幾列]，[哪個區域])

後面兩個引數為可選引數

例如，引用關羽的武力值：INDEX(A1:D10,3,3)

MATCH函式

功能：查詢指定元素在陣列中的位置

語法：MATCH(找什麼，在哪找，[精確找還是差不多就行])

第三個引數可省略，有三個選項-1、0、1，經常選擇0，表示精確查詢

例如，查詢關羽在第幾行：MATCH("關羽",A1:A10,0)

INDEX+MATCH組合應用

我們將MATCH找到的行號，作為INDEX函式的第二個引數，就可以動態獲取某個將軍的武力值了

例1：查詢某個將軍的武力值

=INDEX(A1:D10,MATCH(H2,A1:A10,0),3)

在實際應用中，我們可能還會遇到列號不確定的情況，這時，分別用兩個MATCH函式作為INDEX的第二第三個引數，就可以 動態獲取我們需要的資訊

例2：動態獲取某月某項 費用

INDEX(A1:N7,MATCH(A11,A1:A7,0),MATCH(B10,A1:N1,0))

有時候，我們的資料來源可能不止一個，這時候，第四個引數可以上場了，將match函式作為index函式的第四個引數，還可動態獲取某公司某月某項費用

例3：動態獲取某公司某月某項費用

=INDEX((A1:N7,A8:N14,A15:N22),MATCH(P2,A1:A7,0),MATCH(Q1,A1:N1,0),MATCH(P1,P19:P21,0))

在工作中，經常會遇到反向查詢的情況，這時，index+match就能派上大用場了

例4、反向查詢

=INDEX(A1:A10,MATCH(G2,B1:B10,0))

注意事項

1、這裡為了便於理解，將INDEX的陣列和引用形式統一為引用形式，兩者之間有細微區別，實際運用中，無需深究陣列和引用形式的區別，能夠解決問題即可

2、上面例舉的幾個例子是這個組合函式最常見的運用，使用起來非常靈活，理解了這個函式，那麼OFFSET+MATCH的組合也是差不多的，其他更多應用場景，請關注EXCEL精選技巧後續文章吧！

關注 EXCEL精選技巧，每天學習3分鐘，堅持一個月，你將大不同！

請問各路大神，如何用公式設定，當a1＞b1的時候，c1＝3，d1＝0 反過來a1＜b1時候，c1等於0，d1＝3 a1＝b1時候，c1和d1都＝1 請大神賜教，不好意思，請問，以上問題呢，有人懂嗎？

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

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

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

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

INDEX(array, row_num, [column_num])

MATCH(lookup_value, lookup_array, [match_type])

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Step05-公式的自動填充

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

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

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函式好用的地方，您可以根據實際情況選擇使用。

【60+函式彙總案例】

【資料透視基礎精選10篇】

【Power Query入門到實戰80篇】

【Power Pivot 基礎精選15篇】

強大的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中MATCH函式可以返回指定內容所在的位置，而INDEX又可以根據指定位置查詢到位置所對應的資料，各取其優點，我們可以返回指定位置相關聯的資料。

1.MATCH函式(返回指定內容所在的位置）

MATCH(lookup-value,lookup-array,match-type)

lookup-value:表示要在區域或陣列中查詢的值，可以是直接輸入的陣列或單元格引用。

lookup-array:表示可能包含所要查詢的數值的連續單元格區域，應為陣列或陣列引用。

match-type:表示查詢方式，用於指定精確查詢（查詢區域無序排列）或模糊查詢（查詢區域升序排列）。取值為-1、1、0 。其中0為精確查詢。

2.INDEX函式（返回制定位置中的內容）

INDEX(array,row-num,column-num)

array:要返回值的單元格區域或陣列。

row-num:返回值所在的行號。

column-num:返回值所在的列號。

建立如圖所示的Excel表格，其中座標為資料區域，右邊為查詢方式。

選擇F3單元格，在單元格中輸入：=MATCH("二月",A2:A13,0)，回車可以看到二月所對應的行數為2.

選擇G5單元格，在單元格中輸入：=INDEX(A2:B13,2,2)，回車可以看到A2:B13區域中2行2列交叉對應的值.

選擇G7單元格，在單元格中輸入：=INDEX(B2:B13,MATCH(F7,A2:A13,0))，用MATCH函式返回F7單元格中的之所對應的行，再利用ATCH函式返回的行號用INDEX函式查詢出月份對應的金額。

用同樣的方法再H7單元格中輸入：=INDEX(C2:C13,MATCH(F7,A2:A13,0))，即可查詢出月份對應的年齡。

為了方便起見，我們可以在F7單元格中製作下拉列表，這樣可以方便查詢月份。

其實對於表格函式而言，完成目標，簡單有效最重要，沒有什麼好用多少倍的說法，完全因人而異，在熟悉的領域用簡單的函式完成目標，如果有一天發現掌握的函式不能滿足要求，再尋找新的解決辦法，說句實在的，會函式的哪個不是因為懶才搞的。

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

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

用法：

INDEX(區域, 第幾行);

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

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

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

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

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

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

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

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

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函式索引出此行對應的數值。

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

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