認識這對搭檔，解決90%的查詢問題

在excel裡，對于“查找”的實作，vlookup絕對是使用得最為頻繁的一個函數。

但是，遇到下面問題，vlookup就沒用了。

下面的表格記錄了員工的資訊，現在想通過“姓名”查找對應的“工号”。如圖所示，通過輸入不同的姓名，就會傳回對應的工号。

在原資料裡，“工号”在A列，“姓名”在B列，如果是通過工号來查詢對應的姓名，用vlookup函數就能秒殺。但現在是通過B列來查詢對應的A列的内容，是發向查詢。

vlookup函數在查詢的時候隻能從左往右查詢，且查詢對象所在的列，必須要在查詢區域的第一列，也就是說，隻能通過A列來查詢B列或其它列，而不能通過B列來反向查詢A列。

你可能會說了，把原資料裡的A列和B列調換一下順序不就輕易地避開了上面的問題嗎？但是，這樣做原始資料就發生了改變，在工作中很多時候我們拿到的表資料，标題中是會有合并單元格的，這就更限制了列的移動。

像這種反向查詢問題，就必須祭出我們的“大殺器”了：index+match組合，你就可以更靈活地實作查詢。

1.什麼是index？

index函數能根據指定的行号和列号來傳回一個值。文法規則如下：

index(單元格區域,第幾行,第幾列)

單元格區域：就是要查找的資料範圍；

第幾行：在查找範圍的第幾行；

第幾列：在查找範圍的第幾列。（其中“第幾列”是可以省略的。）

對index函數有了基本的認識後，下面通過案例來看下如何使用。

沿用上面案例中的員工資訊表，現在想要查詢員工“猴子大大”的工号。

要找的工号在A列，姓名“猴子大大”在第7行，是以輸入公式=index($A$2:$A$11 , 7)。

也就是告訴index函數，我們要查找的是A2:A11這個區域的第7行的資訊，于是傳回了正确的工号（A2002）。

聰明的你肯定發現了端倪：我在這兒是用肉眼來看，然後用手指頭戳着一個一個數，最後才知道猴子大大位于第7航。

那麼，問題就來了。如果資料量非常大，或者要查找的員工非常多，難道我還要靠肉眼來看靠手指來數數嗎？

是以，這時候就得要有一個函數來告訴index，讓它去取第幾個。下面隆重請出index的最佳搭檔：match函數。

2.什麼是match？

match在英文中是比對的意思，是以大家應該很自然就能想到它的作用就是進行資料比對。

比對什麼呢？

就是拿你要查找的值，去指定的區域進行比對。比對上了，就會傳回目标值所在的單元格位置。它的文法規則是：

match(要查找的值,在哪裡找,是否精确比對)

要查找的值：就是我們想比對的值。在這個案例中是姓名“猴子大大”；

在哪裡找：去哪個區域找想比對的值。在這個案例中就是去哪裡找姓名“猴子大大”，就是要去“姓名”列中找；

是否精确比對：它有三個選項，-1，0，1。其中，0代表的是精确比對。在這個案例中是要在“姓名”列精确比對“猴子大大”，是以選擇的值是0。-1表示查找大于等于“要查找的值”。1表示查找小于等于“要查找的值”。

這個案例中，我們愉快地就寫下了這樣的公式

=match("猴子大大",$B$2:$B$11,0)

傳回結果是7，表示比對到“猴子大大”在姓名列的第7行。

在案例示範中，我們把要查找的猴子大大，放在了單元格H2,是以上面的公式也可以改為：

=match(H2,$B$2:$B$11,0)

傳回的結果同樣也是7。

3.index+mathch搭檔

在最開始的時候，我們靠肉眼來查找來數數。但是現在有了match函數，我們就把這個查找的任務丢給它，讓它來傳遞。是以原本的公式

=index($A$2:$A$11 , 7)

就可以把公式中的7修改成math函數

=index($A$2:$A$11 , match(H2,$B$2:$B$11,0) )

也就是說，使用mathch函數來為index函數的第二個參數提供值,告訴index要傳回的是第幾個值。

是以，通過index+match函數的組合，我們就可以打造一個下面這樣的查詢系統啦。

無論你要查找的範圍有多大，要查找的量有多少，都是秒秒間就能有結果啦。

（動圖中所示的下拉菜單是用“資料驗證”來實作的，有關這個功能的用法，可戳連結詳細了解）

4.如何實作多條件查找？

上面小試牛刀之後，我們再來進階一下。index+match的最強大的之處是，它們能實作多條件查找。

上面案例示範中，我們先match出猴子大大在B列的位置，然後再用index傳回A列對應的值，得出了對應的工号。但是，如果我還想查詢出猴子大大的其它資訊呢？如下圖：

除了工号，我還想查其對應的“基本工資”“部門”“籍貫”資訊。而且，這些資訊與資料源的順序是不一緻的。

怎麼寫公式呢？

有人說，那我就用案例一查詢其工号的方法呗，依葫蘆畫瓢分别再寫三個公式，一一來查“基本工資”“部門”“籍貫”資訊。

這是一個方法，卻是一個很笨的方法。實際工作中，我們面對的可能是很龐大的資料，要查詢的列會很多，手動地一個列對應一個公式的寫下來，不僅效率低下，還容易出錯。

那有沒有辦法可以隻寫一次公式，就能傳回所有列的結果嗎？辦法當然是有的。

首先，我們來理清一下：要用index函數來傳回值，我們就得告訴它，我們要在指定區域的哪裡去找。如要查詢“工号”，就得告訴它，要去第1列查找；要查找“基本工資”，就得告訴它，要去第6列查找；要查“部門”，就得告訴它，要去第5列查找。那誰來告訴它呢？用match來告訴它。

match不是最擅長比對嗎？好，就用它來定位位置資訊。我們要查詢“工号”，我們就用match來比對，定位到“工号”在資料源裡，它是位于第1列；要查詢“基本工資”，我們就用match來比對，定位到“基本工資”在資料源裡，它是位于第6列。

把match得到的位置資訊就存儲起來，然後傳給index，index收到定位資訊後，就去指定區域對應的位置查詢，于是傳回對應值，查詢結束。

好了，思路清楚後，我們在I2單元格寫下公式吧：

=index($A$2:$F$11,

match($H2,$B$2:$B$11,0),
         match(I1,$A$1:$F$1,0))
           

公式解讀：我們要在A2:F11這個區域查找，區域這麼大，在哪裡找呢？給個定位資訊吧。好，讓match來告訴你橫坐标、縱坐标。

根據單元格H2的引用值，用match來比對“猴子大大”，定位到他所在的行，為第7行，作為橫坐标。

那縱坐标呢？因為要查他的工号，是以，再用match對“工号”進行比對，“工号”在資料源A1:A11裡，位于第1列，作為縱坐标。

好了，在A2:F11的這個區域裡第7行第1列交叉處的單元格的值，就為猴子大大的工号資訊。對于 “基本工資”的查找，同理，在指定區域的第7行第6列查找；其它資訊，依次類推。

因為公式還要往右填充，是以，要把單元格H2進行列的鎖定，防止公式在填充過程中發生了列的變化。是以，得出了上面的公式。

再通過“資料驗證”使得H2的單元格内容自由選擇，就能打造一個查詢系統了：

想查詢哪個員工的哪些資訊，也就隻是眨眼的功夫。

5.總結

通過index+match這對搭檔，我們可以靈活自如地解決90%的查詢問題。match用來定位，index根據定位來傳回指定位置的值，你學會了嗎？

推薦：人工智能時代的必學技能