編按：

今天是VLOOKUP與LOOKUP雙雄戰(zhàn)的第三回合。在前兩個回合中，VLOOKUP旗開得勝，連勝兩局。第三回合是交叉查詢，LOOKUP能否展開反擊止住頹勢呢？

VLOOKUP和LOOKUP這對高頻函數(shù)的較量注定是場持久戰(zhàn)。在前兩個回合的較量中，VLOOKUP占據(jù)上風(fēng)，此番更要乘勝追擊。新一輪較量，即刻開戰(zhàn)！

ROUND 03 交叉查詢

什么是交叉查詢？我們可以通過一個查找值查找多個字段。如果被查找的多個字段的排列順序與查找區(qū)域中對應(yīng)字段的順序不一致，我們稱之為交叉查詢。如下，我們要從數(shù)據(jù)源中查找“阿普”的多個字段“綽號”“能力”“職位”，很顯然被查找字段與數(shù)據(jù)源中字段“職位”“能力”“綽號”的排列順序不一致，這就是交叉查詢，要怎么做呢？

圖3.1：交叉查詢

最基礎(chǔ)的做法就為每一個查找字段單獨設(shè)置公式。

H3公式 =VLOOKUP($G3,$A$1:$E$12,5,0)

I3公式 =VLOOKUP($G3,$A$1:$E$12,4,0)

J3公式 =VLOOKUP($G3,$A$1:$E$12,3,0)

圖3.2： 逐一設(shè)置查詢公式

這種逐一設(shè)置公式的做法很笨拙，除了需要重復(fù)輸入類似的公式外，還需要人工判別每一個單元格的返回列值。如果查找字段很多，估計會逼瘋不少表親。下面看看小花是如何使用VLOOKUP和LOOKUP做交叉查詢的。

VLOOKUP：經(jīng)天緯地，拿手好戲

VLOOKUP和MATCH這對函數(shù)組合正是為交叉查詢而生。VLOOKUP通過MATCH函數(shù)的協(xié)助，自動判斷出返回列值。MATCH函數(shù)用于返回查找值在某一行/列中的位置，它的語法是MATCH(查找值，查找行/列，查找方式）。此處我們用到的查找方式是精確查找，第三個參數(shù)用FALSE或0表示。

圖3.3： MATCH的基本用法

公式說明

以B17公式為例，“職位”出現(xiàn)在A1:E1的第三個位置，所以MATCH的返回值為3。

介紹完MATCH函數(shù)的基本用法后，隆重介紹EXCEL函數(shù)中一種使用頻率最高的函數(shù)組合——VLOOKUP+MATCH。

=VLOOKUP($G3,$A$1:$E$12,MATCH(H$2,$A$1:$E$1,0),0)

圖3.4：VLOOKUP+MATCH交叉查詢

公式說明

VLOOKUP+MATCH組合的基本套路是=VLOOKUP(查找值,查找區(qū)域,MATCH(查找字段,字段區(qū)域,0),0)。它是在VLOOKUP的基本用法上，將第三個參數(shù)返回值列序用MATCH替換，通過匹配，自動返回目標(biāo)字段在查找區(qū)域的列序。

套路的基本要點如下：

1.MATCH的查找值必須與VLOOKUP查找區(qū)域標(biāo)題行中的某個單元格完全一致。這是高頻錯誤點，需注意空格的干擾！

2.為了使公式可以拖動填充，VLOOKUP的第一個參數(shù)通常鎖定列，如$G3，第二個參數(shù)通常鎖定行和列，如$A$1:$E$12；MATCH的第一個參數(shù)通常鎖定行，如H$2，第二個參數(shù)通常鎖定行和列，如$A$1:$E$1。公式最后是“,0),0)”這樣的結(jié)構(gòu)，分別表示MATCH函數(shù)和VLOOKUP函數(shù)都執(zhí)行精確匹配。這些細(xì)節(jié)都是小白容易忽略、出錯的地方。

LOOKUP：數(shù)組形式，劍走偏鋒

說實話，交叉查詢，LOOKUP同樣無法單干，需要找?guī)褪纸M團(tuán)行動，譬如 LOOKUP+MATCH+OFFSET。

=LOOKUP($G3,OFFSET($A$1:$A$12,,,,MATCH(H$2,$A$1:$E$1,0)))

圖3.5： LOOKUP的數(shù)組形式

公式說明

該公式使用了LOOKUP的數(shù)組形式=LOOKUP(查找值，查找區(qū)域），表示在查找區(qū)域的首行/列中進(jìn)行匹配，返回查找區(qū)域末行/列中與之對應(yīng)的值。于是問題的重點就變成了如何使查找區(qū)域的末列自動變?yōu)榉祷刂档乃诹?/span>。我們用OFFSET函數(shù)和MATCH函數(shù)來解決。

OFFSET函數(shù)是一個偏移函數(shù)，它根據(jù)給定的偏移行數(shù)和列數(shù)從初始位置偏移至指定區(qū)域，并返回指定大小的區(qū)域，它的語法是:=OFFSET(初始區(qū)域,偏移行數(shù)，偏移列數(shù)，[返回區(qū)域的行數(shù)]，[返回區(qū)域的列數(shù)])

此處我們的初始區(qū)域為A1:A12，返回區(qū)域仍然是以A1:A12為首列的區(qū)域，行、列偏移量皆為0，返回區(qū)域的行數(shù)也與初始區(qū)域一致，因此這三個參數(shù)直接用逗號占位，不填數(shù)字。最后我們通過MATCH返回匹配列序數(shù)，從而確定OFFSET返回區(qū)域的列數(shù)。公式最終返回以A列為首列、以MATCH返回值為末列，包含1-12行的區(qū)域。以H3中的公式為例，MATCH返回5，則OFFSET返回結(jié)果是以A1:A12為首列的5列區(qū)域即A1:E12。把A1:E12作為LOOKUP數(shù)組形式的第二個參數(shù)，LOOKUP將查找值$G3在區(qū)域A1:E12的首列A1:A12中進(jìn)行匹配，返回查找區(qū)域A1:E12的末列E1:E12中與之對應(yīng)的值，從而完成交叉查詢。

第三回合，在處理交叉查詢問題時，VLOOKUP和LOOKUP都能應(yīng)對自如。

但VLOOKUP的用法較為簡單，只需借助MATCH函數(shù)即可完成，而LOOKUP函數(shù)則需要MATCH和OFFSET兩個函數(shù)和它配合才能實現(xiàn)。綜合看來，后者不如前者簡單易學(xué)。

結(jié)束語

VLOOKUP+MATCH是查詢函數(shù)中非常經(jīng)典的套路，LOOKUP的數(shù)組形式在實戰(zhàn)中也非常實用，兩者都是查詢函數(shù)學(xué)習(xí)的重中之重。希望小伙伴們不只是做VLOOKUP和LOOKUP較量中的吃瓜群眾，還要能深入了解其原理，掌握用法，提升能力。

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

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

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

相關(guān)推薦：

VLOOKUP與LOOKUP的比較①《VLOOKUP&LOOKUP雙雄戰(zhàn)（一）：VLOOKU的漂亮開局》

VLOOKUP函數(shù)中的日期問題《破除日期迷惑，多條件查找就用Vlookup！》

用LOOKUP解決四舍五入《一個四舍五入的問題竟然連LOOKUP都用上了，你們也太會玩了……》