small用于不连续数组_用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结束。