VLOOKUP函數多表查找套路

2021-02-13 Excel之家ExcelHome

查找引用數據是常見的工作需求，但絕大多數人都只會從指定的位置查找數據，當數據分散在多張工作表時便束手無策了.

今天來傳授一招多表查找技術，可以讓VLOOKUP在多張工作表內統一查找，十分便利。

應用場景和數據結構

如下圖所示，某企業的原材料來自多個供應商，每個供應商的採購記錄放置在單獨的工作表中，現在要求按照原材料，查詢所有供應商的單價、數量以及採購員信息。

如果手動一個個寫公式的話，因為數據源位於不同工作表，VLOOKUP的第二參數也需要分別變更，工作量很大，效率低下。

其實只要掌握下面的技術，就可以輕鬆搞定多表查找了。

公式解法

下面先告訴大家這個公式怎麼寫，再看演示效果。

在多表查找的C2單元格輸入以下公式，向右、向下填充公式

=VLOOKUP($A2,INDIRECT($B2&"!a:d"),COLUMN(B1),0)

效果演示


為了方便大家清晰、直觀地查看效果，我更改分表數據，大家查看公式結果。

可見這個公式完全支持數據源變動後結果自動更新，非常方便。

原理解析


=VLOOKUP($A2,INDIRECT($B2&"!a:d"),COLUMN(B1),0)

1、藉助INDIRECT函數實現跨表引用，B2引用工作表名稱，單元格區域引用A列至D列。

2、利用混合引用實現當公式向右填充時，引用的原材料名稱和工作表名稱不變。

3、將跨表引用的區域傳遞給VLOOKUP進行查找，返回對應供應商數據。

本文由公眾號 Excel函數與公式 友情推薦

易學寶微視頻教程，1290個Office技巧精粹，每個技巧都與實際工作密切相關。輕鬆學習技巧，練就職場達人，淘寶搜索關鍵字：ExcelHome易學寶

相關焦點

  • VLOOKUP函數多表查找的通用套路
    雖然單元格顯示的是年月形式，實質上並不是，針對這種，可以用TEXT函數轉換。=TEXT(B1,"e年m月")&"!A:L"不過這個並不是真正的區域，還需要嵌套INDIRECT函數，才能間接轉換成區域。=INDIRECT(TEXT(B$1,"e年m月")&"!
  • VLOOKUP函數多表查找的通用公式
    無意看到VIP學員在引用每個客戶的截止本期末應收款為：，也就是每個表的金額。
  • Indirect搭配Vlookup函數輕鬆實現多表查詢.
    生產搬運分享Excel基礎技能用1%的Excel基礎搞定99%的日常工作做一個有文藝範的Excel公眾號Excel是門手藝 玩轉需要勇氣‍以前覺得掙錢不重要現在オ知道星辰和大海都需要門票詩和遠方的路費都很貴我們都知道vlookup
  • Vlookup函數最難的6個查找公式+12種常見錯誤
    一、Vlookup函數最難的6個查找公式最近有很多同學在微信後臺提問，vlookup函數如何實現模糊查詢和批量查找
  • excel查找函數-vlookup
    大家都會想到 VLOOKUP、LOOKUP等熟悉的查詢函數，但是對於剛入接觸或學習excel的新手不知道怎麼操作，今天分享關於查詢問題通過下面例子進行演示，如下：關鍵詞：vlookup、IFERROR、反方向查找=VLOOKUP（查找目標，查找範圍，返回值的列數，精確OR模糊查找) =IFERROR(值,錯誤值)=VLOOKUP(E3,$A$3:$C
  • Vlookup最牛的一對多查找用法
    其實，有幫手的Vlookup函數，比前兩者還要牛×。今天我們用Vlookup完成超高難度的一對多查找。商品入庫明細表要實現的合併效果：（把某個商品所有進貨記錄放在一個單元格裡並除重複）2、生成連續結果在下表中用vlookup函數直接從上表中查找返回輔助列的結果，最後還要設置為自動換行。
  • 只會Vlookup函數Out了!Excel所有查找公式全在這兒!
    =LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)根據銷量從右表中查找提成比率。=LOOKUP(A2,$D$2:$E$5)根據銷量和比率完成情況，從表中查找返利。，估計你搜遍網絡也找不到，這也是蘭色首次編寫跨多文件查找公式。
  • LOOKUP函數多表查找其實很簡單
    點擊上方藍字「Excel不加班」關注，看下一篇很多粉絲提到多表心理怕得要死現在要在匯總表，查找每個表的F列合計值，也就是F列最後一個數字。在進行多表查找之前，先來了解LOOKUP函數的查找原理。查找一列中最後一個數字，可以用LOOKUP函數進行查找。=LOOKUP(9E+307,A:A)
  • vlookup函數實現多條件查找的3種方法,最後一種你肯定沒見過
    vlookup函數一般情況下，只能查找第一個符合條件的。
  • 15個Excel函數公式的套路,可直接套用,收藏備用吧
    今天跟大家分享15個Excel函數公式的套路，如果遇到類似的問題，直接套用即可，話不多說我們開始吧1. 查找重複值公式：=IF(COUNTIF(A$2:A2,A2)=1,"","重複")首選我們利用countif函數進行條件計數，然後使用if函數進行判斷當其結果等於1時代表不重複，當不等於1時候代表重複2.
  • Vlookup函數中最難的6個查找公式,幫你整理齊了
    最近有很多同學在微信後臺提問，vlookup函數如何實現模糊查詢和批量查找。
  • vlookup函數在excel函數中的應用
    excel的功能十分強大,vlookup函數的作用也很強大,是excel函數中最重要的函數之一,可以幫助我們在很多數據中找到我們想要的答案,那這個函數該怎麼用呢?有沒有實例可以參考?有!excel中vlookup函數的使用方法請看下面實例!打開帶有數據的表格,我就隨便找了個數據作為例子,如圖所示,至少包含兩行數據,這樣才能保證有數據可以引用.
  • Excel自定義函數實現多值查找
    Excel中，很多人都用過vlookup這個函數，它可以實現單值查找，即找到每一個匹配的值。
  • Excel中查找函數vlookup和index—match使用方法詳細介紹
    在Excel中，談到查找函數推薦最多的就是vlookup函數和index—match函數，很多人把兩個函數作為判斷Excel水平的重要指標，可想而知這兩個函數在
  • VLOOKUP函數跨表、跨工作簿查找,你會嗎?
    跨表、跨工作簿又如何用？1.同一個工作表查找每次，盧子都曬出這張經典的VLOOKUP函數說明圖。明細表查找表問題：根據姓名，查找明細表對應的工資。=VLOOKUP(A2,明細表!B:F,5,0)3.跨工作簿查找實際工作上，明細表跟查找表有的時候被放在兩個工作簿中。希望達到的效果是這樣的，就是設置公式以後，以後可以不用打開明細表也可以查找到對應值。
  • 工作中萬能函數vlookup的12種用法,該有的全在裡面
    二、如何與match函數搭配使用F2單元格公式=VLOOKUP(B2,$B$14:$F$22,MATCH(F$1,$B$13:$F$13,0))公式說明：用Match函數查找表1的標題在表四、如何用vlookup函數製作工資條=VLOOKUP($G96,$A$96:$E$104,COLUMN(B1),)公式說明：根據序號從工資表中查找對應的信息
  • Vlookup 函數這些＂新＂用法,90%的人還不會!
    vlookup函數教程很多，都是介紹它的用法。
  • 對比Excel,學習如何在Python中寫Vlookup函數
    今天我就帶著大家對比學習一下，怎麼分別在Excel和Python中使用Vlookup函數。數據源介紹 如圖所示，有一個「vlookup.xlsx」文件，「A1:F11」是我們的數據源區域，「K1:L5」是我們的查找源區域。我們的目的就是要在數據源區域的G列加一列數據，查找出不同類型下名稱表示。
  • vlookup函數的使用方法(實例版)
    通俗表述就是VLOOKUP(查找值，查找範圍，查找列數，精確匹配或者近似匹配） 在此，老菜鳥告訴大家，在我們的工作中，幾乎都使用精確匹配，該項的參數一定要選擇為false。否則返回值會出乎你的意料。VLOOKUP使用示範vlookup就是豎直查找，即列查找。
  • Excel常用函數大全之match、index、vlookup篇
    1 match(查詢值,區域,查詢類型)：該函數可以查詢某個文本在特定區域中的具體位置。查詢類型一般情況下為0，意思是精確查找。例如下圖：查詢「郭襄同學」在所有同學中的位置，可在C2單元格中輸入如下公式：        2 index(區域,第幾行,第幾列)：該函數的意思是返回區域的指定位置。