index函数多条件查找,indexmatch函数多条件组合 收藏 0

用Small、If、Index、Indirect、Row、Char、Match实现一对多查找

一.应用实例：

1.要求：通过G2单元格的下拉菜单选择不同的班级，在右侧单元格区域可以查找出对应班级所有的学生的姓名和对应的成绩。（返回查找结果区域的表头可以与原表头区域不对应）

2.话不多少，先给朋友们上照效果图看看是不是你们想要的操作。

二.操作步骤：

1.在H2单元格输入公式

=INDEX(INDIRECT(CHAR(64+MATCH(H$1,$A$1:$E$1,0)) & \”:\”& CHAR(64+MATCH(H$1,$A$1:$E$1,0))),SMALL(IF($A$2:$A$9=$G$2,ROW($A$2:$A$9),10000),ROW(C1)))&\”\”

2.公式解析：

（1）CHAR(64+MATCH(H$1,$A$1:$E$1,0))返回要查找字段在原始表头的列的位置用大写字母ABC…表示,例如查找数学时返回大写字母D。

（2）用 & \”:\”&链接（1）所讲公式返回对一列的引用。例如（1）结果返回为D时，返回D:D。

（3）如果直接把（2）的结果作为对列的引用或返回错误，利用Indirect函数可以正确的返回列的引用。经过这三步上述部分公式等价于直接对一列的引用。

（4）IF($A$2:$A$9=$G$2,ROW($A$2:$A$9),10000)判断每一个要班级是否等于要查找的内容，如果等于返回这个数据所在单元格的行号，如果不等于返回一个比较大的数值（在这里设置为10000，只要保证这行没有数据即可）。这样构成了一个行号和这个比较大的数值所构成的数组。

（5）利用small（步骤4构成的数组，row（A1））,分别提取第一、二、三小的数值。Row（A1）向下拖动时返回一个1、2、 3的数字序列，最终构成的是查找区域等于查找值的行和较大值10000构成的数组。

（6）利用Index函数提取（3）对列的引用，（5）所返回行构成的数组所对应的值。

（7）公式最后 &””，是因为10000行的内容是空白，公式会返回数字0。&””可以避免0的出现。

三.注意事项：

1.在这里一定要注意单元格的引用方式。

2.这是一个函数比较多的综合应用。

3.数组公式输入时要以Ctrl+Shift+Enter结束。

Vlookup函数的新用法，查询合并单元格，很多Excel高手都不知道

今天跟大家分享下我们如何在带有合并单元格的表格中进行数据查询，这样的问题相信很多人都遇到过，在带有合并单元格的表格中使用函数进行数据查询，往往是找不到正确的结果的，今天就跟大家分享会出现这种情况的原因以及解决方法。

想要从零学习Excel，这里↑↑↑

这个是因为函数公式看到的数据与我们看到的数据是不同的，如下图所示，右侧的就是函数公式看到的数据，它其实就是将合并单元格取消之后的格式，这样的话仅仅只有每个班级的第一个姓名会对应班级，其余的数据是没有对应班级的，所以就是造成函数识别错误，那么对于这样的情况我们应该如何解决呢？分为2种情况

如果你的数据源是可以更改的，最简单的方法就是将合并单元格取消掉，然后为每个姓名填充它们对应的班级，这个过程是可以批量设置的，操作也非常的简单

首先需要把合并单元格取消掉，然后按下【F5】调出定位点击【定位条件】选择【空值】点击确定，随后在编辑栏中输入等于=A2（就是第一个班级的位置）按下快捷键【Ctrl+回车】即可批量填充姓名

姓名填充完毕后，它就是一个简单的多条件查询了，在这里不建议使用vlookup，更建议大家使用index+match进行数据查询

公式为：=INDEX($C$2:$C$13,MATCH(F3&G3,$A$2:$A$13&$B$2:$B$14,0))

这个公式之前跟大家介绍过很多次了，在这里就不再多做介绍了，就是index+match多条件查询的常规用法

第二种情况是数据源不能更改，必须保持合并单元格的格式，这种情况就比较复杂了，我需要借助indirect函数，这个函数的使用方法前几天跟大家分享过，如果想要了解我会将文章链接放在最后。我们可以使用indirect函数来构建一个动态的数据查询区域，来达到数据查询的效果

公式为：=VLOOKUP(F4,INDIRECT(\”B\”&MATCH(E4,A:A,0)&\”:C14\”),2,0)，跟大家简单的介绍下查询原理，先来了解下他的参数

第一参数：F4，查找表中姓名的位置

第二参数：\”B\”&MATCH(E4,A:A,0)&\”:C14\”)，数据查询区域，它是一个动态的区域

第三参数：2，查找的结果在第二参数的第二列

第四参数：0，表示精确匹配

关键是Vlookup函数的第二参数，如下图所示，在这里B就是列标号，MATCH(E4,A:A,0)它的作用是查找班级班A列的位置，C14是表格的结尾位置

这样的话就会定位到各自班级对应的姓名，并且这个班级的姓名永远是在最前面的，当我们将班级设置为2班，Vlookup就会以下图黄色区域为查找区域进行数据查询

以上就是今天分享的全部内容，对于合并单元格的数据查询，第二种方法是通用的，它适用于任何查找函数，不仅限于Vlookup函数，大家可以动手试一下，可能很多人都会觉得比较难，但是我觉得这个算是比较简单的方法了。

我是Excel从零到一，关注我，持续分享更多Excel技巧

7个Excel多条件查找公式，谁才是你心中的NO.1，我最喜欢第6个

今天我们来盘点下Excel表格中多条件查询的解决方法，Excel发展到今天，多条件查询的方式非常多，究竟哪个才是你心中的NO.1呢？大家可以留言讨论下

公式：=VLOOKUP(G3&H3,IF({1,0},A2:A9&B2:B9,E2:E9),2,0)

Vlookup可以说是家喻户晓，但是它的多条件查询我觉得是最麻烦的，需要借助IF来构建二维数组，不仅难，还容易出错，很多新手小白都学不会

推荐指数：⭐

公式：=INDEX(E2:E9,MATCH(G3&H3,A2:A9&B2:B9,0))

这是一个组合函数，需要先使用MATCH来查询到位置，最后使用INDEX来返回对应的结果，相较于Vlookup更加简单一点点，只需使用连接符来进行数据链即可

推荐指数：⭐⭐

公式：=LOOKUP(1,0/((A2:A9=G3)*(B2:B9=H3)),E2:E9)

相较于前两种方法，我觉得这个方法更加适合新手小白使用，我们只需要记得公式的书写规则即可，有几个条件就在括号中乘几个条件即可

=LOOKUP(1,0/((条件1)*(条件2)),结果列)

推荐指数：⭐⭐⭐

公式：=DGET(A1:E9,I2,G2:H3)

这个函数是一个数据库函数，数据库函数是根据表头字段的名称来进行数据匹配的，所以在设置参数的时候，必须要包含表头字段的名称，这个函数相信大家都很陌生，简单的说下它的语法

语法：=DGET(要查询的表头,返回结果的表头名称,查找的条件)

推荐指数：⭐⭐⭐⭐⭐

公式：=SUMIFS(E2:E9,A2:A9,G3,B2:B9,H3)

如果你要查找的结果恰好是一个数字，用SUMIFS函数也是可以，在这里查找的条件是唯一的，求和就相当于查找，因为结果只有一个。用它只能查找数字，只能给3颗星了

推荐指数：⭐⭐⭐

公式：=XLOOKUP(G3&H3,A2:A9&B2:B9,E2:E9)

如果你的软件版本支持XLOOKUP，强烈建议使用它来进行多条件查询，它的操作简单，不容易出错，只需使用连接符来进行数据的连接即可

推荐指数：⭐⭐⭐⭐⭐

公式：=FILTER(E2:E9,(A2:A9=G3)*(B2:B9=H3))

FILTER本质是用来做数据筛选的，但是如果结果是唯一的，筛选与查询本质都是一样的，在这里我们让两个条件相乘就能得到对应的结果啦，操作也是比较简单的

推荐指数：⭐⭐⭐⭐

以上就是常见的多条件查询的7种方式，我们就不再列举其他方式了，解决这个问题，选择任意一个就是可以的。

或者你还知道其他的方式吗，也可以留言讨论下

如果你想要提高工作效率，不想再求同事帮你解决各种Excel问题，可以了解下我的专栏，WPS用户也能使用，讲解了函数、图表、透视表、数据看板等常用功能，带你快速成为Excel高手

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com