編按：

說起查找引用，想必大家也很熟悉了，Excel里也自帶了許多查找引用的函數(shù)。今天就來給大家系統(tǒng)講講經(jīng)典的四大查找：一對一查找、一對多查找、多對一查找、多對多查找。神秘、典型的萬金油公式在多個地方大顯身手，不管是一個條件查找返回多個結(jié)果，還是多條件查找返回一個、多個結(jié)果，它都有不俗表現(xiàn)。

“查找”是使用EXCEL過程中是非常普遍的工作，所以EXCEL設(shè)計了很多各式各樣的“查詢與引用類函數(shù)”

今天就來給大家分享幾種常見的“查找引用”情況和一些經(jīng)典的嵌套函數(shù)、數(shù)組函數(shù)。

一對一查詢：

一個匹配條件，對應(yīng)一個匹配值。

這是最常見、最簡單的條件匹配，同時也是解法最多的一種。

我們給出兩種“快準(zhǔn)狠”的方案，供大家選擇：

1）=VLOOKUP($E$2,$A$1:$B$16,2,0)，沒什么可講的了，VLOOKUP函數(shù)的基礎(chǔ)用法。

2）=INDEX($B$2:$B$16,MATCH($E$2,$A$2:$A$16,0))

這個就是經(jīng)常說到的INDEX+MATCH的經(jīng)典組合了。通過MATCH函數(shù)查到匹配條件在A列中的位置序號，返回給INDEX函數(shù)作為第二參數(shù)，引出B列的內(nèi)容。

一對一查找，大家記這兩個就好了，其它的函數(shù)解法拿來學(xué)習(xí)學(xué)習(xí)思路即可。

一對多查詢：

在匹配條件列中存在，但不局限于唯一性，導(dǎo)致有多個匹配值存在。

這是一個經(jīng)典用法“萬金油”函數(shù)，這個函數(shù)的別稱充分說明它的應(yīng)用環(huán)境很廣，同時也決定了它在寫法上的變化很多。

我們列幾個寫法吧：

1）匹配條件對應(yīng)字段2中的最大值

{=MAX(IF($A$2:$A$16=$E$2,$B$2:$B$16,""))}，此函數(shù)返回13。

2）匹配條件第二次出現(xiàn)時對應(yīng)字段2的值

{=INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E$2,ROW($1:$15),99^9),2))}，此函數(shù)返回3。

3）列出所有匹配條件的值

{=IFERROR(INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E$2,ROW($1:$15),99^9),ROW(E1))),"")}，下拉填充單元格，最后形成的效果如下圖：

通過上面3個案例，我們可以看到“萬金油”函數(shù)的幾個共同點：

a、鍵入公式后，都需要按CTRL+SHIFT+ENTER三鍵結(jié)束的數(shù)組函數(shù)；

b、都是圍繞IF函數(shù)的判斷，形成新的數(shù)列，再使用SMALL或者LARGE函數(shù)來確定我們需要的序號，即可返回給INDEX函數(shù)索引出對應(yīng)的匹配值。

這就是“萬金油”的精髓，一般人我不告訴他。

多對一查找：

多個匹配條件確定一個匹配值的情況

兩列條件決定一個匹配值，我們可以使用下列函數(shù)來解決。

1）=SUMPRODUCT(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3),$C$2:$C$16)

SUMPRODUCT是多條件匹配用的最多，而且寫法最簡單的寫法，語法如下：

=SUMPRODUCT((【條件1區(qū)域】比較符【條件1】)*(【條件2區(qū)域】比較符【條件2】)*（【條件n區(qū)域】比較符【條件n】）,【匹配值列】)

2）=LOOKUP(1,0/(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3)),($C$2:$C$16))，LOOKUP是利用了二分法運算原理做的函數(shù)思路，一定要注意條件需要用括號括起來（所有條件連乘之后，再用0除），語法如下：

=LOOKUP(1,0/((【條件1區(qū)域】比較符【條件1】)*(【條件2區(qū)域】比較符【條件2】)*（【條件n區(qū)域】比較符【條件n】）)，【匹配值列】)

上面的例子是多對一查找的在一維數(shù)據(jù)上的匹配方式，還有一種特殊情況需要大家注意。

如果是下圖所示的二維數(shù)據(jù)，又該如何操作呢？

橫縱交叉點匹配值，也屬于多條件查詢，我們此時還是使用INDEX+MATCH的經(jīng)典嵌套函數(shù)解決：

=INDEX($B$19:$E$25,MATCH($H$18,$A$19:$A$25,0),MATCH($H$19,$B$18:$E$18,0))，用兩個MATCH函數(shù)分別確定橫縱匹配條件出現(xiàn)的序號，再返回給INDEX函數(shù)索引出橫縱交叉點上的值。

多對多查找：

多個查找條件，匹配多個結(jié)果。

例如上圖的案例，AN有兩組對應(yīng)值，我們需要匹配出所有的匹配值，可以使用下面的函數(shù)：

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3),ROW($1:$15),9^9),ROW(F1))),"")}

這里還是用的萬金油公式，鑒于篇幅原因，就不多解釋了。

上面就是匹配查詢問題的幾種常見解題思路，一文寫不盡整個EXCEL。

對于匹配查詢的問題，還有很多的個案，只能具體問題具體分析，但是大部分個案都可以使用“萬金油”解出，大家多多練習(xí)吧。

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

VLOOKUP&LOOKUP雙雄戰(zhàn)（四）：在橫向和逆向查詢上的血拼！

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者E圖表述；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。