在日常表格制作過程中，經(jīng)常會(huì)找到參考相關(guān)數(shù)據(jù)的任務(wù)。

事實(shí)上，這是大多數(shù)小伙伴接觸最多的事情之一。

例如，以員工姓名為基準(zhǔn)查找相應(yīng)的業(yè)績(jī)，以商品的產(chǎn)品編號(hào)為基準(zhǔn)查找相應(yīng)的銷售量，等等。

今天我要介紹一個(gè)非常強(qiáng)大的函數(shù)組合來(lái)實(shí)現(xiàn)這個(gè)功能。

這就是“索引矩陣組合”(INDEX MATCH combination)。

具體的應(yīng)用方案如下：

01正向查找

其中正向是從左到右查找參考大象值。

也就是說(shuō)，查找值在左側(cè)，返回值在右側(cè)。

下圖是第一季度公司職員的業(yè)績(jī)表。我們現(xiàn)在想以名字找到相應(yīng)的業(yè)績(jī)。

在[F2]單元格中寫入以下公式：

=index (c23360 C7，match (E2，b2: B7，0))

解析公式：

首先，使用MATCH函數(shù)找到儲(chǔ)存格[B2:B7]中彈性的位置，然后傳回?cái)?shù)字2。

然后使用此數(shù)字2作為INDEX函數(shù)的第二個(gè)參數(shù)。也就是說(shuō)，返回INDEX的第一個(gè)參數(shù)[C2:C7]單元格區(qū)域中第二個(gè)位置單元格的值。

MATCH函數(shù)有三個(gè)參數(shù)：

MATCH(查找值、查找區(qū)域、查找類型)

MATCH函數(shù)用于查找區(qū)域或數(shù)組中的值、位置和數(shù)字。

通常，返回的這個(gè)數(shù)字用作其他函數(shù)的參數(shù)。

返回的數(shù)字用作INDEX的參數(shù)。

第三個(gè)參數(shù)的查尋類型為0表示精確查詢。

INDEX通常有三個(gè)參數(shù)：

索引(返回的區(qū)域、行號(hào)、列號(hào))

第一個(gè)參數(shù)是單行或單列，因此第三個(gè)參數(shù)也可以省略。

如果不省略，公式也可以這樣寫。

=index (c23360 C7、match (E2、b2: B7、0)、1)

02反向查找

其中反向是指從右到左查找參考大象值。

也就是說(shuō)，查找值在右側(cè)，返回值在左側(cè)。

以上面的例子為例。例如，我們想找到遺囑的職員號(hào)碼是多少。

公式如下：

=index(a 23360 a 7、match (E2、b2:b7、0))

解析公式：

這個(gè)公式和正向查詢的公式具有相似的意義。只需更改INDEX函數(shù)的第一個(gè)參數(shù)，將返回的區(qū)域更改為[A2:A7]區(qū)域。

因此，無(wú)論是正向還是反向查詢，思維方式都是一樣的。

只需將INDEX函數(shù)第一個(gè)參數(shù)的區(qū)域更改為需要返回的區(qū)域。

03雙向查詢

其中，雙向表示在水平和垂直方向上查找參照大象值。

下圖：第一季度員工工資表摘要?，F(xiàn)在要找到遺囑三月的工資是多少。

bbdc05c?from=article.detail&_iz=31825&index=3" width="640" height="286"/>

公式如下：

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

公式解析：

先用第一個(gè) MATCH 函數(shù)查找劉彥在【A3:A8】這個(gè)單元格中所在的垂直位置，返回?cái)?shù)字 2；

然后使用第二個(gè) MATCH 函數(shù)查找 3 月在【B2:D2】這個(gè)區(qū)域中所在的水平位置，返回?cái)?shù)字 3；

最后將這兩個(gè)數(shù)字分別作為 INDEX 函數(shù)的第二和第三參數(shù)，意思是返回 INDEX 第一參數(shù)【B3:D8】區(qū)域中的第 2 行第 3 列這個(gè)相交叉單元格中的值，即：8606。

因?yàn)檫@里的雙向查找需要涉及兩個(gè)方向上的位置，故我們?cè)谶@里使用兩個(gè) MATCH 函數(shù)來(lái)分別定位行和列的位置。

04 多條件查找

多條件查找是指：根據(jù) 2 個(gè)或者更多的條件來(lái)查找返回對(duì)應(yīng)的目標(biāo)值，這個(gè)在我們?nèi)粘９ぷ髦幸步?jīng)常會(huì)用到。

如下圖：是一張各地區(qū)各貨號(hào)的銷量表，我們想查找南京地區(qū)貨號(hào)為 BH003 的銷售量是多少。

條件一：南京；

條件二：貨號(hào)為 BH003。

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

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

公式解析：

公式的基本框架跟上面一樣，只是先用「&」文本連接符將兩個(gè)條件連接成一個(gè)新的條件形式；

然后按照上面正向查找的思路進(jìn)行查找，并返回【C2:C7】區(qū)域中的銷售量。

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

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

返回?cái)?shù)字 3，然后將這個(gè)數(shù)字 3，作為 INDEX 函數(shù)的第二參數(shù)，意思是返回 INDEX 第一參數(shù)【C2:C7】單元格區(qū)域中的第 3 個(gè)位置這個(gè)單元格中的值，也就是 72。

因?yàn)檫@個(gè)是數(shù)組公式，所以該公式需要按【Ctrl+Shift+Enter】三鍵結(jié)束。公式外面的大括號(hào)會(huì)自動(dòng)生成。對(duì)于使用 Office 365 的小伙伴們可以不用按三鍵結(jié)束。

當(dāng)然這里的多條件查找，也可以同時(shí)應(yīng)用在水平和垂直兩個(gè)方向上。

另外，不論有多少個(gè)條件，只要先用「&」文本連接符將多個(gè)條件連接一個(gè)新的條件，然后應(yīng)用上面的套路查找就行。

05 區(qū)間查找

區(qū)間查找指的是：如果查找值處于某一個(gè)區(qū)間內(nèi)，就返回對(duì)應(yīng)的目標(biāo)值。

如下圖，需要根據(jù)業(yè)績(jī)區(qū)間來(lái)計(jì)算員工的獎(jiǎng)金數(shù)。

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

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

公式解析：

這個(gè)公式的特別之處在于 MATCH 函數(shù)的第二和第三參數(shù)的應(yīng)用。

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

這里查找小于 65 的最大值是 41，41 在這個(gè)常量數(shù)組（{0;41;81;121}）中的位置是 2，即返回?cái)?shù)字 2，然后作為 INDEX 第二參數(shù)，最終返回結(jié)果 2000。

當(dāng)然，這里我們也可以將這個(gè)常量數(shù)組寫在一個(gè)輔助列里面，可能會(huì)更好理解。

如下圖：

06 模糊查找

模糊查找就是通過結(jié)合使用通配符（「？」問號(hào)和「*」星號(hào)）來(lái)實(shí)現(xiàn)查找目標(biāo)值。

如下圖：我們想查找姓徐開頭的，并且是三個(gè)字符的員工編號(hào)是多少。

公式如下：

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

公式解析：

這里關(guān)鍵在于 MATCH 函數(shù)支持通配符的用法。

大家一定要注意下：

「？」問號(hào)通配符，代表任意單個(gè)字符。

「*」星號(hào)通配符，代表匹配任意多個(gè)字符。

這里我們用了兩個(gè)「？？」問號(hào)，就代表任意兩個(gè)字符。

PS：這里的問號(hào)和星號(hào)都是英文半角的噢！

07 總結(jié)

今天，我們一起學(xué)習(xí)了兩個(gè)強(qiáng)大的函數(shù)組合（INDEX+MATCH）來(lái)實(shí)現(xiàn)工作中常用的 6 種查找引用功能：

? 正向查找；

? 反（逆）向查找；

? 雙向查找；

? 多條件查找；

? 區(qū)間查找；

? 模糊查找。

你平時(shí)用得最多的一種查找是什么呢？你有哪些關(guān)于 Vlookup 的疑問呢？

歡迎給我們留言哦~