在我們日常制表過程中，經常會用到查找引用相關數據的工作。

其實，這也是大部分小夥伴接觸最多的工作之一。

比如：根據員工姓名查找對應的業績、根據某一商品的貨号查找對應的銷量等等。

今天，我就給大家介紹一對非常強大的函數組合來實現這個功能。

它們就是 INDEX MATCH 組合。

具體的應用場景如下：

這裡的正向指的是：從左向右查找引用目标值。

也就是查找值在左邊，返回值在右邊。

如下圖，是一季度公司員工的業績表，我們現在想根據姓名查找對應的業績。

在【F2】單元格寫入如下公式：

=INDEX(C2:C7,MATCH(E2,B2:B7,0))

公式解析：

先用 MATCH 函數查找劉彥在【B2:B7】這個單元格中所在的位置，返回數字 2；

然後将這個數字 2 作為 INDEX 函數的第二個參數，意思是返回 INDEX 第一參數【C2:C7】單元格區域中的第 2 個位置單元格中的值。

MATCH 函數有三個參數：

MATCH（查找值，查找區域，查找類型）

MATCH 函數專門用于查找一個值，在一個區域或者數組中所在的位置，并返回一個數字。

通常将返回的這個數字作為其他函數的參數使用。

這裡将返回的數字作為 INDEX 的參數使用。

如果第三參數的查找類型為 0，則表示精确查找。

INDEX 通常也有三個參數：

INDEX（返回的區域，行号，列号）

因為第一參數是單行或者單列，所以第三參數也可以省略。

如果不省略的話，公式也可以寫成這樣：

=INDEX(C2:C7,MATCH(E2,B2:B7,0)，1)

這裡的反向指的是：從右向左查找引用目标值。

也就是查找值在右邊，返回值在左邊。

還以上面的例子為例，比如，我們想查找劉彥的員工編号是多少。

公式如下：

=INDEX(A2:A7,MATCH(E2,B2:B7,0))

公式解析：

這個公式的意思跟正向查找的公式差不多，隻是更改了 INDEX 函數的第一參數，将返回的區域變成【A2:A7】區域。

由此可見，無論是正向查找還是反向查找，思路是一樣的。

隻是把 INDEX 函數的第一參數的區域更改成需要返回的區域即可。

這裡的雙向指的是：從水平和垂直兩個方向查找引用目标值。

如下圖：是一季度員工工資表彙總，現在需要查找劉彥 3 月的工資是多少。

公式如下：

=INDEX(B3:D8,MATCH(G3,A3:A8,0),MATCH(H2,B2:D2,0))

公式解析：

先用第一個 MATCH 函數查找劉彥在【A3:A8】這個單元格中所在的垂直位置，返回數字 2；

然後使用第二個 MATCH 函數查找 3 月在【B2:D2】這個區域中所在的水平位置，返回數字 3；

最後将這兩個數字分别作為 INDEX 函數的第二和第三參數，意思是返回 INDEX 第一參數【B3:D8】區域中的第 2 行第 3 列這個相交叉單元格中的值，即：8606。

因為這裡的雙向查找需要涉及兩個方向上的位置，故我們在這裡使用兩個 MATCH 函數來分别定位行和列的位置。

多條件查找是指：根據 2 個或者更多的條件來查找返回對應的目标值，這個在我們日常工作中也經常會用到。

如下圖：是一張各地區各貨号的銷量表，我們想查找南京地區貨号為 BH003 的銷售量是多少。

條件一：南京；

條件二：貨号為 BH003。

在【G2】單元格寫入如下公式：

=INDEX(C2:C7,MATCH(E2&F2,A2:A7&B2:B7,0))

公式解析：

公式的基本框架跟上面一樣，隻是先用「&」文本連接符将兩個條件連接成一個新的條件形式；

然後按照上面正向查找的思路進行查找，并返回【C2:C7】區域中的銷售量。

MATCH 函數查找「南京 BH003」在新生成的數組中的位置：

MATCH("南京 BH003",{"南京 BH001";"南京 BH002";"南京 BH003";"上海 BH001";"上海 BH002";"北京 BH001"},0)

返回數字 3，然後将這個數字 3，作為 INDEX 函數的第二參數，意思是返回 INDEX 第一參數【C2:C7】單元格區域中的第 3 個位置這個單元格中的值，也就是 72。

因為這個是數組公式，所以該公式需要按【Ctrl Shift Enter】三鍵結束。公式外面的大括号會自動生成。對于使用 Office 365 的小夥伴們可以不用按三鍵結束。

當然這裡的多條件查找，也可以同時應用在水平和垂直兩個方向上。

另外，不論有多少個條件，隻要先用「&」文本連接符将多個條件連接一個新的條件，然後應用上面的套路查找就行。

區間查找指的是：如果查找值處于某一個區間内，就返回對應的目标值。

如下圖，需要根據業績區間來計算員工的獎金數。

在【G2】單元格寫入如下公式：

=INDEX(J2:J5,MATCH(F2,{0;41;81;121},1))

公式解析：

這個公式的特别之處在于 MATCH 函數的第二和第三參數的應用。

如果第三參數是 1 或者省略, MATCH 函數将查找小于或等于第一參數的最大值，并且第二參數中的值必須以升序排序。

這裡查找小于 65 的最大值是 41，41 在這個常量數組（{0;41;81;121}）中的位置是 2，即返回數字 2，然後作為 INDEX 第二參數，最終返回結果 2000。

當然，這裡我們也可以将這個常量數組寫在一個輔助列裡面，可能會更好理解。

如下圖：

模糊查找就是通過結合使用通配符（「？」問号和「*」星号）來實現查找目标值。

如下圖：我們想查找姓徐開頭的，并且是三個字符的員工編号是多少。

公式如下：

=INDEX(A2:A7,MATCH(E2&"??",B2:B7,0))

公式解析：

這裡關鍵在于 MATCH 函數支持通配符的用法。

大家一定要注意下：

「？」問号通配符，代表任意單個字符。

「*」星号通配符，代表匹配任意多個字符。

這裡我們用了兩個「？？」問号，就代表任意兩個字符。

PS：這裡的問号和星号都是英文半角的噢！

今天，我們一起學習了兩個強大的函數組合（INDEX MATCH）來實現工作中常用的 6 種查找引用功能：

❶ 正向查找；

❷ 反（逆）向查找；

❸ 雙向查找；

❹ 多條件查找；

❹ 區間查找；

❺ 模糊查找。

你平時用得最多的一種查找是什麼呢？你有哪些關于 Vlookup 的疑問呢？

歡迎給我們留言哦~

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!