excel函數技巧：兩個查詢函數的用法比較 下

2020-10-20 部落窩教育BLW

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

—————————————————

VLOOKUP和LOOKUP這對高頻函數的較量註定是場持久戰。在前兩個回合的較量中，VLOOKUP佔據上風，此番更要乘勝追擊。新一輪較量，即刻開戰！

***ROUND 03 交叉查詢

什麼是交叉查詢？我們可以通過一個查找值查找多個欄位。如果被查找的多個欄位的排列順序與查找區域中對應欄位的順序不一致，我們稱之為交叉查詢。如下，我們要從數據源中查找「阿普」的多個欄位「綽號」「能力」「職位」，很顯然被查找欄位與數據源中欄位「職位」「能力」「綽號」的排列順序不一致，這就是交叉查詢，要怎麼做呢？

最基礎的做法就為每一個查找欄位單獨設置公式。

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)

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

VLOOKUP：經天緯地，拿手好戲

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

公式說明

以B17公式為例，「職位」出現在A1:E1的第三個位置，所以MATCH的返回值為3。

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

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

公式說明

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

套路的基本要點如下：

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

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

LOOKUP：數組形式，劍走偏鋒

說實話，交叉查詢，LOOKUP同樣無法單幹，需要找幫手組團行動，譬如 LOOKUP+MATCH+OFFSET。

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

公式說明

該公式使用了LOOKUP的數組形式=LOOKUP(查找值，查找區域），表示在查找區域的首行/列中進行匹配，返回查找區域末行/列中與之對應的值。於是問題的重點就變成了如何使查找區域的末列自動變為返回值的所在列。我們用OFFSET函數和MATCH函數來解決。

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

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

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

但VLOOKUP的用法較為簡單，只需藉助MATCH函數即可完成，而LOOKUP函數則需要MATCH和OFFSET兩個函數和它配合才能實現。綜合看來，後者不如前者簡單易學。

***結束語：

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

＊＊＊＊部落窩教育－excel查詢函數技巧＊＊＊＊

原創：小花/部落窩教育（未經同意，請勿轉載）

相關焦點

  • excel函數技巧:兩個查詢函數的用法比較 續
    在使用這兩個函數之前，我們必須按下圖所示，對各區間及對應值進行升序排列：1.將數值區間的分界值按升序依次填入連續的單元格，即從最小值到最大值，自上而下填入同一列單元格中。LOOKUP的二分法LOOKUP總是將查詢範圍視為一組從小到大排列的有序數組，通過將目標值與查詢區域的二分位值進行比較，從而確定目標值出現在查詢區域的上半區細分範圍(目標值小於二分位值)還是下半區細分範圍(目標值大於二分位值)。
  • excel函數技巧：兩個查詢函數的用法比較 續
    在使用這兩個函數之前，我們必須按下圖所示，對各區間及對應值進行升序排列：1.將數值區間的分界值按升序依次填入連續的單元格，即從最小值到最大值，自上而下填入同一列單元格中。2.各區間分界值採用區間下界值，例如，「及格」區間數值60，「優秀」區間數值85。
  • excel函數技巧：兩個查詢函數的用法比較 終
    但是沒有多條件查詢函數VLOOKUPS和LOOKUPS，這是為什麼呢？因為這兩個函數本身就能實現多條件查詢！！！如下例，我們需要通過職位和性別來找到名單中唯一的女性船長並返回她的明細，該怎麼做？這就是為什麼有人說，如果要評價函數圈最大的發現，那LOOKUP的兩分法絕對能獨佔鰲頭！誠然！＊＊＊結束語本文，小花介紹了包含查詢和多條件查詢這兩個難度較高的應用情境，至於多值查詢、批量查詢等諸多查詢高難度用法，還有待小夥伴們進一步去挖掘、去深究！
  • excel函數技巧：兩個查詢函數的用法比較 上
    PS：與VLOOKUP的遍歷查詢法不同，LOOKUP的查詢原理是二分法，LOOKUP陷阱正與二分法有關，感興趣的小夥伴可以看看往期教程《LOOKUP函數用法全解（下）——LOOKUP　　由此觀之，在基本用法的較量中，LOOKUP的語句較複雜且需要對查找區域進行升序排列，不如VLOOKUP函數簡潔實用。　　　　第一回合，函數基本用法，VLOOKUP小勝！！！
  • excel函數技巧：兩個查詢函數的用法比較 續二
    VLOOKUP：內有賢臣，外有強援面對LOOKUP的挑釁，不可一世的VLOOKUP函數絕不輕易認輸，橫向查詢硬上也要上！請出轉置函數TRANSPOSE來幫忙。=HLOOKUP(B7,$A$2:$K$3,2,0)公式說明HLOOKUP函數是VLOOKUP函數的孿生兄弟，其功能和用法與VLOOKUP如出一轍，差別僅在於
  • excel函數應用技巧：求和函數SUM的進階用法
    平時我們用SUM函數一般都是處理一些簡單的求和問題，今天我們要給大家分享幾招SUM函數的進階用法：快速對交叉區域、應收款、小計行自動求和。Excel函數家族樹大根深，枝繁葉茂，但若要按使用頻率高低排個序，那唯一能和IF函數一爭高下的，恐怕只有SUM了。
  • excel函數應用技巧:那些名不副實的函數列舉
    說到「廢柴」一詞，相信小夥伴們很難把它和excel中各類神通廣大的函數聯繫在一起。但是隨著excel版本的不斷更新，不少函數逐漸被取代、淘汰，我們把這樣的函數稱為「廢柴」函數。學習更多技巧，請收藏部落窩教育excel圖文教程。
  • excel查找函數應用:vlookup多種情景的運用技巧
    VLOOKUP可算得上是查詢函數界的大明星。但如何用它同時在兩張工作表，甚至多張，如三張、四張工作表中查詢需要的數據呢？下面這篇文章就給大家揭曉答案！ 學習更多技巧，請收藏關注部落窩教育excel圖文教程。
  • excel if函數 if函數嵌套用法
    if函數必須的條件：每一個excel if函數必須使用英文的括號括起來；括號內為三個數據，第一個數據是條件（如上例中的a2>60），第二數據為滿足第一個數據後返回的結果，通常使用英文的引號括起來，第三個數據是不滿足第一個數據時需要返回的結果；（如果不輸入第三個數據可以嗎，當然可以，返回什麼結果自己試試吧）
  • excel函數公式實戰：文本函數TEXT常用技巧匯總
    要說在excel中最特別的文本函數，那必定非TEXT函數莫屬，外界給它的稱號不計其數「文本之王」「整容大師」「千面鬼才」等等，由此可看出對它的喜愛。下面小花就和大家匯總了TEXT函數5種最實用的用法，趕緊來看看吧！學習更多技巧，請收藏。
  • excel函數應用技巧：這個銷售統計模板，能動態變色查詢
    該模板支持動態查詢功能，並且在查詢的時候，相應數據會變色，如此，查詢結果一目了然。統計模板將使用SUM、AND、COLUMN、MATCH、OFFSET函數並結合條件格式和數據驗證。學習更多技巧，請收藏關注部落窩教育excel圖文教程。
  • 有了這個函數寶典,你也能成為excel大神
    ，但是真正的excel高手都是比較善用excel中的「宏」和「函數」。小編今天就給大家安利一個功能超級強大的「excel函數寶典」，讓你輕鬆學會excel中的各類函數。「excel函數寶典」是一個excel文件，總共包含十一類excel函數。
  • Excel函數應用篇:INDEX函數
    在使用INDEX()函數時，第二、三參數一般情況與MATCH()函數配合使用，以實現動態查找引用的目的。第一：index函數用法解釋　　INDEX函數的用法是返回列表或數組中的元素值，此元素由行序號和列序號的索引值確定。即行列交叉點所在的引用。
  • excel中vlookup函數的用法筆記
    本篇將介紹excel中vlookup函數的用法，有興趣的朋友可以了解一下！一、前言excel是我們工作中很常用的表格製作工具，它不僅僅只是用來製作表格，還能對數據進行處理（如：排序、運算等）。excel中還給我們提供了很多實用的函數，今天小編要介紹的就是其中一個，也是比較常用的一個函數，即vlookup函數。vlookup函數的功能其實就是用來找東西的，只要是找excel表格中的某個數據，大家都會想到使用vlookup函數。vlookup函數怎麼用呢？相信是很多人關心的問題，接下來就跟著小編一起來學習excel vlookup函數的用法吧！
  • excel數據處理技巧:組合函數統計產品批號
    最近小編收到一位群友的求助，他說自己被excel中的編號問題給難住了。這是這麼回事呢？編號不就是1、2、3、4、5嗎，直接下拉單元格就能搞定，這有何難？一起來看看下面這篇文章中excel數據處理技巧。近日看到一個群友的求助，覺得比較有意思，想和大家分享一下。學習更多技巧，請收藏部落窩教育excel圖文教程。
  • excel if函數的詳細教程(基本用法和嵌套用法)
    本篇將介紹excel if函數的詳細教程，有興趣的朋友可以了解一下！excel是我們工作中很常用的表格製作工具，它不僅僅只是用來製作表格，還能幫助我們處理數據（比如：運算、篩選、排序等等）。今天小編要介紹的是excel if函數的詳細用法，希望對大家有所幫助！
  • excel查找技巧：單個函數在區間查找中的應用解析
    就拿excel中的區間查找來說，在我們的工作中隨時都會用到，比如等級評定，績效考核等等。所以我們將推出關於區間取值的系列教程，該系列教程共分為3篇，分別是常規函數篇、經典嵌套函數篇、數組函數篇，將為小夥伴分享9種區間取值的方法，希望能豐富小夥伴們的excel知識。
  • Excel小技巧:讓你秒懂得lookup函數區間查找星座
    2、但是我們都知道關於文本型數字排序後並不是我們想要的結果，lookup函數和vlookup函數的模糊查找要求源數據是需要【按照升序排列】，因為根據日期區間查詢的，所有選擇這兩個函數來查找！3、所以我們就要將轉換後值轉數值，將C2公式改為=TEXT(B2,"mdd")*1或者在公式前加兩個負號即=--TEXT(B2,"mdd")這樣數據源就可以正常排序了，今天的查詢實際上就是利用Lookup函數的向量基本用法實現的！
  • lookup函數的第1種用法:在升序條件下進行條件查詢!
    在excel函數裡面，lookup函數的出現率是非常高的。在貼吧、論壇的求助帖裡面，大部分內容都可以用lookup函數進行解決。它的用法也有多種，這節課我們講lookup函數第1種用法：在升序條件下進行條件查詢！如圖中案例表格，已知員工的工號、姓名、手機號，現在需要通過輸入工號，來查詢該員工的手機號。
  • excel數據處理技巧：組合函數統計產品批號
    最近小編收到一位群友的求助，他說自己被excel中的編號問題給難住了。這是這麼回事呢？編號不就是1、2、3、4、5嗎，直接下拉單元格就能搞定，這有何難？一起來看看下面這篇文章中excel數據處理技巧。近日看到一個群友的求助，覺得比較有意思，想和大家分享一下。