提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在多个工作表中查看值?

在Excel中,我们可以轻松应用vlookup函数来将匹配值返回到工作表的单个表中。 但是,你有没有考虑过如何在多个工作表上查看价值? 假设我有以下三个数据范围的工作表,现在我想根据这三个工作表中的条件获取相应值的一部分,请参阅截图:

DOC-VLOOKUP-多片-1 DOC-VLOOKUP-多片-2 DOC-VLOOKUP-多片-2 -2 DOC-VLOOKUP-多片-2

使用数组公式从多个工作表中查找值

使用普通公式从多个工作表中查找值


将多个工作表或csv文件组合/导入到一个工作表或工作簿中:

在您的日常工作中,将多个工作表,工作簿和csv文件合并到一个工作表或工作簿中可能是一项巨大而头痛的工作。 但是,如果你有 Kutools for Excel,其强大的功能 - 结合,您可以将多个工作表,工作簿或csv文件快速合并到一个工作表或工作簿中。

doc结合了多个工作表-1

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


使用数组公式从多个工作表中查找值


要使用这个数组公式,您应该给这三个工作表一个范围名称,请在新的工作表中列出您的工作表名称,如下面的截图所示:

DOC-VLOOKUP-多片-2

注意:如果有多个工作表名称要列入单元格, 创建图纸名称列表 的特点 Kutools for Excel 可以帮助您列出工作簿中的所有工作表名称,如下面的屏幕截图所示:

DOC-VLOOKUP-多片-10

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!

1。 为这些工作表提供一个范围名称,选择表单名称,然后在中输入一个名称 名称框 在编辑栏旁边,在这种情况下,我将输入Sheetlist作为范围名称,然后按 输入 键。

DOC-VLOOKUP-多片-2

2. 然后你可以在你的特定单元格中输入以下长公式: =VLOOKUP(A2,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A$2:$B$5"),A2)>0),0))&"'!$A$2:$B$5"),2,FALSE),然后按 Ctrl + Shift + Enter 键一起得到相应的值,看截图:

DOC-VLOOKUP-多片-2

3。 然后将填充手柄向下拖动到要应用此公式的单元格,每行的所有相对值已按如下所示返回:

DOC-VLOOKUP-多片-2

请注意:

在上面的公式中:

A2:是要返回其相对值的单元格引用;

Sheetlist:是我在step1中创建的工作表名称的范围名称;

A2:B5:是您需要搜索的工作表的数据范围;

2:表示您匹配的值返回的列号。


演示:使用数组公式从多个工作表中查找值

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


使用普通公式从多个工作表中查找值

如果你不想使范围名称和数组公式不熟悉,这里也有一个正常的公式来帮助你。

请在您需要的单元格中输入以下公式: =IFERROR(VLOOKUP($A2,Sheet1!$A$2:$B$5,2,FALSE),IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$5,2,FALSE),VLOOKUP($A2,Sheet3!$A$2:$B$5,2,FALSE))) 并按下 输入 键返回你想要的值,看截图:

DOC-VLOOKUP-多片-2

然后将填充手柄向下拖动到要包含此公式的单元格区域。

备注:

1。 在上面的公式中:

A2:是要返回其相对值的单元格引用;

Sheet1,Sheet2,Sheet3:是包含您要使用的数据的表单名称;

A2:B5:是您需要搜索的工作表的数据范围;

2:表示您匹配的值返回的列号

2。 为了更容易理解这个公式,实际上,长公式由几个vlookup函数组成,并且连接到IFERROR函数。 如果你有更多的工作表,你只需要在公式之后添加vlookup函数和IFERROE。


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    kathy · 9 months ago
    can you convert the formula into plain text
  • To post as a guest, your comment is unpublished.
    Kiran · 2 years ago
    hi,

    in multiple use sheet , iwant to value increase by serial.
    =VLOOKUP($C10,'[apri.xlsx]ahm'!$C$10:$L$10,6,FALSE)
    =VLOOKUP($C10,'[april.xlsx]ahm'!$C$10:$L$10,7,FALSE)
    autometically value can change in formula
  • To post as a guest, your comment is unpublished.
    Sunil Gyawali · 2 years ago
    Hi, I am also having problem using this formula to compile the values from multiple sheet.
  • To post as a guest, your comment is unpublished.
    usha · 2 years ago
    when i try this foirmula its not valid
  • To post as a guest, your comment is unpublished.
    usha · 2 years ago
    this formula is not valid when i am triying in my excel
    • To post as a guest, your comment is unpublished.
      malik · 2 years ago
      try using Iferror funtion icluding vlookup..
      • To post as a guest, your comment is unpublished.
        Jon · 1 years ago
        Here is an example of what that would look like. =IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),IFERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE),IFERROR(VLOOKUP(A1,Sheet3!A:B,2,FALSE),"Item Not Found!")))


        Essetially look in Sheet1 for this value, if you can't find it, look in Sheet2. If it isn't there look in Sheet3, and if after all of that it can't be found, tell me that the value could not be found.
  • To post as a guest, your comment is unpublished.
    mohd shehzaad khan · 2 years ago
    Hi Sir,

    Please help me

    I have open a excel book in which more than 50 on sheet data available but summary available at sheet one but I want pick value from every sheet.
    So please help out how can i pick value please sir do urgently. I am waiting for your response definietly I will appreciate your response.

    regard's
    Mohd Shehzaad Khan
  • To post as a guest, your comment is unpublished.
    Sam · 3 years ago
    I want to bring mutiple sheets informatiom into one sheets lke pivot table and i want them to be connect..same structures..i did by consolidation but the column department (one field)numbers are not spreading out colums wise (other fields are spread over the columns)..can anyone help plz..
  • To post as a guest, your comment is unpublished.
    Aruana R · 3 years ago
    =IFERROR(VLOOKUP($A2,Sheet1!$A$2:$B$5,2,FALSE),IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$5,2,FALSE),VLOOKUP($A2,Sheet3!$A$2:$B$5,2,FALSE)))


    in above formula, instead 2(column number) i want match criteria with main sheet.


    please help me
  • To post as a guest, your comment is unpublished.
    Grace · 3 years ago
    Hi,

    I am trying to look up multiple sheets to another sheets... can you help me?
  • To post as a guest, your comment is unpublished.
    Prakash Siddhu Gaikw · 3 years ago
    sorry guys wrongly comment on wrong site
  • To post as a guest, your comment is unpublished.
    Prakash Siddhu Gaikw · 3 years ago
    very 3rd class services of this site do not purchase any kind of product.if your a son of your father than repay may money back to my a/c.already mail sent to sajid.
  • To post as a guest, your comment is unpublished.
    Achyutanand. · 4 years ago
    Hiii Dude,

    I am trying to loookup with new worksheets to multiple sheets but i have geeting eroor with this formula please get me solutions.

    my formula is = =VLOOKUP(B17,INDIRECT("'"&INDEX(List,MATCH(1,--(COUNTIF(INDIRECT("'"&List&"[Daily Collection Report-2014-15.xlsx]Modification Pmt'!$D$2B2:AZ2000"),B17)>0),0))&"[Daily Collection Report-2014-15.xlsx]Modification Pmt'!$D$2B2:AZ2000"),2,FALSE)
    • To post as a guest, your comment is unpublished.
      Gerhard · 3 years ago
      Hi,

      I have multiple spreadsheets, I want to lookup a cell value based on matching the name of the Tab.

      can you help please
    • To post as a guest, your comment is unpublished.
      achyutanand · 3 years ago
      [quote name="Achyutanand."]Hiii Dude,

      I am trying to loookup with new worksheets to multiple sheets but i have geeting eroor with this formula please get me solutions.

      my formula is = =VLOOKUP(B17,INDIRECT("'"&INDEX(List,MATCH(1,--(COUNTIF(INDIRECT("'"&List&"[Daily Collection Report-2014-15.xlsx]Modification Pmt'!$D$2B2:AZ2000"),B17)>0),0))&"[Daily Collection Report-2014-15.xlsx]Modification Pmt'!$D$2B2:AZ2000"),2,FALSE)[/quote]
      a

      hi please give me excel sheet
      • To post as a guest, your comment is unpublished.
        kedar · 3 years ago
        =IFERROR(VLOOKUP(A2,Sheet1!$G$2:H5,2,0),IFERROR(VLOOKUP(Summary!A2,Sheet2!$G$2:H5,2,0),IFERROR(VLOOKUP(Summary!A2,Sheet3!$G$2:H5,2,0),"NOT")))
        Lookup Value Result
        A 100 100
        B 200 200
        C 300 300
        D 400 400
        D 400 400
        G 325 325
        H 425 425
        I 150 150
        A 100 100
        K 350 350
        L 450 450
        sheet1 Data
        A 100
        B 200
        C 300
        D 400
        Data 2
        E 125
        F 225
        G 325
        H 425
        Data 3
        I 150
        J 250
        K 350
        L 450