Excel教程:Vlookup一對多查找大法,EXCEL神技巧!

2021-02-24 Excel教程自學平臺

提示：APP長按圖片識別下載

會員免費學習全站23套教程，也包括以後更新發布的

 【解鎖會員】 

支持微信公眾號+小程序+APP+PC網站多平臺學習

常規情況下利用Vlookup查找，當數據源有多條結果時，Vlookup也只返回第一條查詢結果。

這是為什麼呢？首先來看一下原廠說明書中 Vlookup 的語法及參數簡釋

    語法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    參數簡釋

    lookup_value  必需。要在表格或區域的第一列中搜索的值。（也就是第一參數查找的值要位於參數二table_array的第一列中）

    table_array  必需。包含數據的單元格區域。（第一列中的值是 lookup_value 搜索的值）

col_index_num  必需。返回參數 table_array 中第幾列的值。

    range_lookup  可選。一個邏輯值，指定希望 VLOOKUP 查找精確匹配值還是近似匹配值。

    要點：如果 table_array 的第一列中有兩個或更多值與 lookup_value 匹配，則使用第一個找到的值。

說明書中要點裡有說明第一列中有兩個或更多值與查找值匹配時，則使用第一個找到的值。

SO：當遇上一對多查詢時很多小夥伴就不淡定了

網上搜來的一對多查找

Index+Small+If+Row公式實在太難理解，公式辣麼辣麼長還得三鍵結束。問題是我還看不懂啊！

有沒有簡單易懂又不用數組的方法呢？

答案肯定是有的

今天小編就來給大家分享一種不用數組公式的一對多查詢

Vlookup+輔助列

如下圖所示，根據姓名查找對應的每筆銷售明細

首先，我們在姓名前面加一列輔助列

輸入公式=B2&COUNTIF($B$2:B2,B2)

公式COUNTIF($B$2:B2,B2)對指定區域$B$2:B2中指定條件的單元格B2計數，

這樣相當於給姓名編了個號（讓每個姓名都唯一），第一個$B$2 使用了絕對引用，目的在於下拉的時候起點不變，終點一直變，這樣就能統計出區域中有幾個一樣的名字了。如圖中第一次出現的阿文就是阿文1，第二次出現的阿文就是阿文2

不明白的童鞋自己動手試一試哦！可不可以1阿文，2阿文呢？

然後在G2中使用公式

=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,3,0)

往下拉到足夠多行（比如最大10條數據，你可以拉15行，備用5行）

效果如下圖：

通過顏色對比，我們可以校驗一下返回的結果對不對

公式中函數 Row(A1) 返回A1所在的行號，作用是給查詢值加上一個序號，比如：F2為雨夜時，公式下過程中查找值變成了找雨夜1、雨夜2。。。實現了每行查詢的都是唯一值。

第一列日期「沒問題」了，就開始寫第二列銷售量的公式，它和和一列的區別僅僅是返回的數據列不一樣，

H2中輸入公式

=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,4,0) 看一下效果

我們把兩個公式放一起看看區別吧

=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,3,0)

=VLOOKUP($F$2&ROW(A1),$A$1:$D$20,4,0)

明顯的區別就是參數3 裡的一個是3，一個是4，如果還需要5，6，7，8列的話就複製粘貼接著改。

有的小夥伴看到這裡是不是在偷笑了，小編原來還在複製粘貼改數字呀。要是遇上列數很多腫麼辦？

我們先把難看的 #N/A 給解決掉,回頭再來處理它。

公式外套個IFERROR容錯函數來屏蔽錯誤值，找不到時返回空

 =IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$20,3,0),"")

一起來看公式的效果圖

怎麼樣，是不是「完美」了呢？

    

接下來改參數3，這裡可以用column函數,把參數3換成 column(C$1) 因為C列是第3列,因此該函數會返回3,當我們右拉一列它會變成column(D$1)，這正好返回4 ，真是太巧了，正好是我需要的3和4。

公式如下

=IFERROR(VLOOKUP($F$2&ROW(A1),$A$1:$D$20,COLUMN(C$1),0),"")

咱們來看看效果

可有的小夥伴還會有疑問，要是查找時返回的列號是不規律的，這要怎麼辦呢？？

那就得請到我們的Match來幫忙數數了，感興趣的同學自己動手試試吧。（可以參考阿文老師的高大上的動態圖表中MATCHT的用法哦）

今天的分享就到這，如果教程對大家有用，希望大家多多分享點讚支持小編哦！

VIP隨心暢學

新春活動進行中

需要報名趕緊啦^_^

【 愛知趣，充電吧！同學】📢

越努力，越幸運

學習是自我增值的最佳途徑 

全站會員

78元年會員抄底價格

隨心暢學

同時贈送學習資源包

（安裝包+海量模板+濾鏡插件+商用字體包等）

學習官網所有課程

也包括以後更新!

目前包括23門課程

平面設計：PS，CDR，P圖，AI ，淘寶美工，產品精修
影視後期：AE ，PR
攝影后期：攝影，精修人物，影樓後期調色
室內設計：CAD,3DMAX
程式語言：Python
繪畫教程：轉手繪，Q版，漫畫，水彩，素描支持微信公眾號+小程序+APP+PC網站多平臺學習！！！

如果還有什麼需要諮詢的，聯繫微信客服18074746783


點擊閱讀原文一鍵登錄官網，海量視頻vip任意學！(可試看)

相關焦點

  • 值得學習的excel操作小技巧,利用vlookup函數實現一對多查詢
    我們在實際工作中，我們經常使用excel表格對數據進行處理和分析，我們都清楚excel具有強大的excel函數和數據處理工具，我們可以憑藉這些工具盒函數對數據進行快速處理，這次我們還是要講解一下有關vlookup函數的相關內容，我們知道vlookup函數是一個查找函數，我們這次講解的是利用
  • Excel vlookup篩選兩列的重複項與查找兩個表格相同數據
    二、Excel vlookup查找兩個表格相同數據有兩張有重複數據的服裝銷量表（一張在「excel教程.xlsx」中，另一張在「clothingSales.xlsx」中）（見圖2），需要把重複記錄找出來
  • excel數據的模糊查找,vlookup函數與通配符的搭配使用
    今天我們要分享一個比較實用的excel數據的查找技巧，就是通過簡稱來查找全稱，這樣的查找方式叫做模糊查找，我們之前學習過了幾個查找函數，比較常見是vlookup函數和lookup函數以及choose函數等，我們知道這些查找函數各自有各自的優點，今天我們要講解的是vlookup函數與通配符的搭配使用
  • Excel教程:Vlookup一對多查找大法
    會員免費學習全站23套教程，也包括以後更新發布的一個邏輯值，指定希望 VLOOKUP 查找精確匹配值還是近似匹配值。    要點：如果 table_array 的第一列中有兩個或更多值與 lookup_value 匹配，則使用第一個找到的值。說明書中要點裡有說明第一列中有兩個或更多值與查找值匹配時，則使用第一個找到的值。
  • excel查找數據就是如此簡單,vlookup函數的模糊查找
    我們在實際工作中，我們經常使用excel表格處理數據，處理數據的方法有很多種，查找數據應該是我們在日常工作中使用頻率比較高的操作，這次我們還是分享查找數據的小技巧，這次是對數據進行模糊查找，我們使用vlookup函數對數據進行模糊查找，下面我們就以實例結合視頻的形式將詳細的操作步驟展示出來
  • Excel教程：excel查找合併單元格操作技巧
    Excel教程查找合併單元格是一項比較實用的excel操作技巧。比如一個excel工作表，有很多合併單元格，如何一次性選中所有合併單元格，然後取消合併單元格操作。您會嗎？　　excel查找合併單元格操作是這樣的：先任意合併兩個單元格，編輯菜單-查找（或直接按ctrl+f），在查找對話框中點「選項」，然後從單元格中選取格式，然後點全部查找就會找到所有合併過的單元格，如果要全選，可以按快捷鍵ctrl+a。 這樣就可以一次性選中所有的excel表格裡面的合併單元格，然後點擊取消合併單元格即可。
  • 比VLOOKUP好用10倍,一對多查找,用它太簡單了!【excel教程】
    說到excel中的篩選，想必大家早已是了如指掌，不過增強版的篩選，你聽說過嗎？它可比普通的篩選厲害多了，不僅能實現excel中的一對多查找，就連複雜的多對多查找也不在話下！趕緊來看看吧！說起一對多查找，大家首先想到的就是萬金油公式，以前也分享過一篇相關的教程《熬夜加班髮際線後移？誰讓你不會Excel萬金油公式！》。
  • 【Excel使用技巧】vlookup函數的使用方法
    四、 返回多行多列的查找結果公式：=VLOOKUP($F3,$A$2:$D$13,MATCH(H$2,$A$2:$D$2,0),FALSE)在這裡我們在vlookup中嵌套一個match函數來獲取表頭在數據表中的列號
  • excel多條件查找方法:lookup、vlookup、indexmatch多條件查找
    多條件查找函數方法，分別是：lookup多條件查詢、vlookup多條件查找、indexmatch多條件查找。那麼我們就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是將正確結果用0表示，其他的變成錯誤值，利用函數查找忽略錯誤這個特點完成查找。總結：本函數由於使用了二分法原理查找，所以如果數據量較大時運算會很慢。第二 vlookup多條件查找vlookup函數是我們最常用的函數，vlookup函數主要用於垂直方向上向右查找。
  • excel查找函數-vlookup
    在我們日常表格數據處理中，經常遇到數據查詢等問題，比如根據產品編號查詢單價或根據產品名稱反方向查找產品編碼等。大家都會想到 VLOOKUP、LOOKUP等熟悉的查詢函數，但是對於剛入接觸或學習excel的新手不知道怎麼操作，今天分享關於查詢問題通過下面例子進行演示，如下：關鍵詞：vlookup、IFERROR、反方向查找函數使用格式=VLOOKUP
  • excel函數技巧：一對多查找的典型案例分析
    果凍布丁同學就是其中之一，她決定改變這種狀況，向老師求助怎麼根據一個單號自動生成一張入庫單……最終通過解決一對多查詢她得到了供應商群裡的最大點讚！最近我們的果凍布丁同學遇到了一個關於入庫單的問題。入庫單：由於一個單號對應了多個物料，所以果凍布丁同學的問題就是典型的一對多查詢
  • Excel小技巧:vlookup函數合併多個工作表
    有時候會經常從同事那裡收集的工作表需要匯總在同一張工作表中，使用vlookup函數教你快速合併：首先如果我們先要查詢1月的利潤表在F5中輸入1月的公式a:b"),2,0)，快速填充公式的秘訣就是選中整個區域輸入公式後按CTRL+ENTER即可公式解釋：vlookup函數語法=VLOOKUP(查找值，查找區域，返回列數，精確/模糊匹配)第一參數：查找值就是我們的項目$E5，因為查找值是不變的，並且要向右/下邊複製公式，因此需要鎖定列號第二參數
  • excel查找函數應用:vlookup多種情景的運用技巧
    但如何用它同時在兩張工作表，甚至多張，如三張、四張工作表中查詢需要的數據呢？下面這篇文章就給大家揭曉答案！ 學習更多技巧，請收藏關注部落窩教育excel圖文教程。情形1：連續多列使用VLOOKUP有時候我們需要匹配多列數據，例如根據員工ID找到對應的姓名、所在部門和入職時間三條信息。
  • excel中vlookup函數的用法筆記
    本篇將介紹excel中vlookup函數的用法，有興趣的朋友可以了解一下！一、前言excel是我們工作中很常用的表格製作工具，它不僅僅只是用來製作表格，還能對數據進行處理（如：排序、運算等）。excel中還給我們提供了很多實用的函數，今天小編要介紹的就是其中一個，也是比較常用的一個函數，即vlookup函數。vlookup函數的功能其實就是用來找東西的，只要是找excel表格中的某個數據，大家都會想到使用vlookup函數。vlookup函數怎麼用呢？相信是很多人關心的問題，接下來就跟著小編一起來學習excel vlookup函數的用法吧！
  • Excel跨表查詢:vlookup+indirect函數組合,你都不知道有多強大
    江湖傳聞在excel查找界頗有名氣的vlookup函數即將退休，微軟官方也公布將迎來的是xlookup函數，據說功能也是強大的一批！目測感覺有些用法就是vlookup函數和lookup函數的結合體啊，但在目前形勢來看普及該函數可能還需要一段時間，因為還需要考慮各個excel版本版的兼容性！
  • EXCEL強大的vlookup
    vlookup在excel是一個使用頻率相當高的公式，也是我個人最喜歡的公式之一。vlookup公式是查找左側列中的值，如果找到匹配的項，則會在右側的另一列中返回一個信息。公式語法如下：=vlookup(A1,B:C,2,FALSE)其中：A1代表想查找什麼內容。B:C代表想在那裡查找。
  • excel中vlookup函數的使用方法
    vlookup函數是excel表格中高級的用法，通過vlookup函數我們可以調用符合條件的數據，在大量調用時可以節省我們查找複製excel數據的時間，今天我就教下大家vlookup函數的使用方法吧。vlookup函數的使用方法如圖我準備了一張員工入職時間表，員工有非常多，如果我要在這裡面一一找出張三李四王五等人的入職時間的話，可以通過查找黏貼的方式，但是這樣的效率就很低了，特別是要找的人多的話，那使用vlookup函數是最簡單的方法。
  • 【Excel函數教程】解析lookup的經典查找方式
     提示：點擊上方"excel教程"↑免費訂閱　　學習是需要技巧和經驗的
  • EXCEL公式-VLOOKUP應用
    excel的功能十分強大，vlookup函數是最基礎的函數之一，其作用非常強大可以幫助我們在眾多雜亂的數據中找到我們想要的答案。那這個函數該如何使用，下面給大家介紹一下excel中vlookup函數的使用方法。
  • 「Excel實用技巧」16大類查找公式,只會Vlookup函數是遠遠不夠的
    、多工作表查找【例10】從各部門中查找員工的基本工資，在哪一個表中不一定。a:g"),7,0)13、一對多查找【例】根據產品查找相對應的所有供應商A2 =B2&COUNTIF(B$1:B2,B2) B11=IFERROR(VLOOKUP($A11&COLUMN