Question

excel使用SMALL加INDEX引用的问题？

sheet1里的表格要引用sheet2里的表格数据，结果上面的结果是对的，下面的结果和上面的数据重复了，能否用small和index和row函数解决这个问题？具体怎么写？





Answer 1

你的B2公式：

=INDEX(Sheet2!$B$2:$G$10,MATCH($A2,Sheet2!$A$2:$A$10,0),MATCH(B$1,Sheet2!$B$1:$G$1,0))

引用的是Sheet2!$B$2:$G$10区域，引用条件是MATCH($A2,Sheet2!$A$2:$A$10,0)的行号（列号暂略）。而MATCH($A2,Sheet2!$A$2:$A$10,0)从B2向下填充到B10，匹配条件是A2~A10。既然A10=A2，返回的行号当然也是一样。

你是不是要这样的：

B2输入

=IFERROR(INDEX(Sheet2!$B$2:$G$10,SMALL(IF(Sheet2!$A$2:$A$10=$A2,ROW($1:$9),4^8),COUNTIF($A$2:$A2,$A2)),SMALL(IF(Sheet2!$B$1:$G$1=B$1,COLUMN($A:$F),4^4),COUNTIF($B$1:B$1,B$1))),"")

数组公式，输入后先不要回车，按Ctrl+Shift+Enter结束计算，再向右和向下填充。

Answer 2

B2用公式：

=INDEX(OFFSET(Sheet2!$A$1:$A$10,,MATCH(B$1,Sheet2!$B$1:$G$1,)),SMALL(IF(Sheet2!$A$2:$A$10=$A2,ROW($2:$10)),COUNTIF($A$2:$A2,$A2)))
数组公式，按组合键（CTRL+SHIFT+回车）完成公式，右拉，下拉；
还可以用公式：
=HLOOKUP(B$1,Sheet2!$A:$G,SMALL(IF(Sheet2!$A$2:$A$10=$A2,ROW($2:$10)),COUNTIF($A$2:$A2,$A2)),)
数组公式，按组合键（CTRL+SHIFT+回车）完成公式，右拉，下拉。

Answer 3

你的index中第1个查找行的部分——MATCH($A2,Sheet2!$A$2:$A$10,0)——就是精确匹配A2与Sheet2中A列的数据，当找到与A2完全一样的数据，就返回所在的位置，所以它总是返回最先找到的。试想一下，同样去找0，第1次你要它返回第1行，第2次却要它返回第9行，而给的数据没有任何一点区别，叫它如何判断？假设老板到某个地方去找个人，告诉你了名字，你就把他找来了，结果老板说不对，因为那里还有其他人也叫同一样名字。不告诉你其他不同信息，你能知道是要找哪一个吗？
所以，关键是的要把数据不同特点列出来才能便于查找！

追问

那么有又没其它公式能解决这个问题呢？谢谢

追答

如果你是要按行的顺序来取值的话，好像你这个问题提过两次，在另一个提问中，胡须客 的评论给你了公式，不妨去看看。

追问

我看到有人用SMALL加IF加ROW加INDEX可以解决这个问题，我就是不知道具体公式怎么输，你会吗？

追答

既然看到了，就写出来看看，是否合适。
我只知道，要分别提取，就必须有识别它们不同的特点。