INDEX SMALL IF row 多条件返回多结果问题

大脸是个好同志

各位老师请教下：我在网上搜了下INDEX SMALL IF row 函数进行多条件多结果匹配，结果只能正确匹配到三行数据，下面的全部为0不知道什么原因，还请帮忙看看，感谢！


说明：通过南区表中的项目编号、设备型号、地点、开始日期四个条件匹配到DATABASE中序列号

TomingTang

改成这样？

1. =INDEX(DATABASE!Z:Z,SMALL(IF((南区!F2=DATABASE!$F$3:$F$42864)*(南区!L2=DATABASE!$L$3:$L$42864)*(南区!T2=DATABASE!$Q$3:$Q$42864)*(DATABASE!$T$3:$T$42864=南区!Y2),ROW($3:$35656),4^8),1),)

复制代码

其实可以不用这样来的。

大脸是个好同志

TomingTang 发表于 2015-1-13 13:06
改成这样？其实可以不用这样来的。

感谢答复，但是好像还是不能正常实现。因为四个相同的条件应该能配对到很多不同的序列号，改成这样只能重复引用一个序列号。
还有其他的方法么？如果有也请告诉我下，能实现序列号按条件引用过来就行，不一定要用这个方法。
我的excel仅限于会用vlookup这个水准，这个也是在网上搜到的依样画瓢，结果研究半天还是不通%>_<%

wangjguo44

公式中A\SMALL的第二参数不对，你现在用的是ROW(A1)，下拉到第四行后，变成ROW(A4)，即求符合条件的第四小的数，但是实际上到了第四行，满足IF条件的（F5、L5、T5、Y5）只有两行数据，求第四小得到的4^8，放回空值（0）。越往下，ROW()变得越大，结果越不对！

大脸是个好同志

wangjguo44 发表于 2015-1-13 13:19
公式中A\SMALL的第二参数不对，你现在用的是ROW(A1)，下拉到第四行后，变成ROW(A4)，即求符合条件的第四小的 ...

(⊙o⊙)…原来如此，理解了~
那需要怎么改呢？可否帮忙改改？

wangjguo44

大脸是个好同志 发表于 2015-1-13 15:35
(⊙o⊙)…原来如此，理解了~
那需要怎么改呢？可否帮忙改改？

改，应该可以，但是规则不太明白，比如DATABASE表L列型号同样是X3650，Q列同样式重庆，Z列序列号修订却有6个不同值，它们对应的AB列也是不一样，光以原来公式中判断的四条件(南区!F2=DATABASE!$F$3:$F$139)*(南区!L2=DATABASE!$L$3:$L$139)*(南区!T2=DATABASE!$Q$3:$Q$139)*(DATABASE!$T$3:$T$139=南区!Y2)，也无法区分AB列的No等其他字段。
是不是不管这些区别，只要考虑这四条件？这四条件有几行，就取DATABASE表Z列前几个？（南区表符合四条件的有6行，DATABASE表Z列6个全取出，只有5行，就取前5个，最后一个留着不取，若有7行，第7个出错？）

大脸是个好同志

wangjguo44 发表于 2015-1-13 15:54
改，应该可以，但是规则不太明白，比如DATABASE表L列型号同样是X3650，Q列同样式重庆，Z列序列号修订却有 ...

对的，就是这个意思，只要这四个条件一致就返回DATABASE里的序列号,其他字段可以不用考虑。
麻烦了，谢谢！！

wangjguo44

大脸是个好同志 发表于 2015-1-13 16:15
对的，就是这个意思，只要这四个条件一致就返回DATABASE里的序列号,其他字段可以不用考虑。
麻烦了，谢谢 ...

请先验证一下，对不对
EH(WJG)c.rar (217.08 KB, 下载次数: 299)

2015-1-13 17:03 上传

点击文件名下载附件

大脸是个好同志

wangjguo44 发表于 2015-1-13 17:03
请先验证一下，对不对

放到我的原始数据里测试了下，非常完美，太感谢啦！！大大，求拜师！
另：能否告知下用SUMPRODUCT的原理？？知道它是用于算乘积和，但是在这里为什么用这个就有点稀里糊涂了。

wangjguo44

大脸是个好同志 发表于 2015-1-13 18:31
放到我的原始数据里测试了下，非常完美，太感谢啦！！大大，求拜师！
另：能否告知下用SUMPRODUCT的原理 ...

=INDEX(DATABASE!Z:Z,SMALL(IF((F2=DATABASE!$F$3:$F$139)*(L2=DATABASE!$L$3:$L$139)*(T2=DATABASE!$Q$3:$Q$139)*(DATABASE!$T$3:$T$139=Y2),ROW($3:$139),4^8),SUMPRODUCT((F$2:F2=F2)*(L$2:L2=L2)*(T$2:T2=T2)*(Y$2:Y2=Y2))))这里的SUMPRODUCT((F$2:F2=F2)*(L$2:L2=L2)*(T$2:T2=T2)*(Y$2:Y2=Y2))也是“求和”，只是另一种形式的“求和”：

对单条件的重复数据引用，往往用countif，比如有的引用相同成绩（假定B2:B4是相同）而不同姓名时，多用countif，出现第一个成绩（B2），由small(……,countif(B$2:B2,B2))，返回第一个姓名；出现第二个成绩（B3），下拉，由small(……,countif(B$2:B3,B3))，返回第二个姓名；出现第三个成绩（B4），下拉由small(……,countif(B$2:B4,B4))，返回第三个姓名；……

你这里四条件不在一列，就不能用countif，用SUMPRODUCT来得到第几个相同数据：SUMPRODUCT((F$2:F2=F2)*(L$2:L2=L2)*(T$2:T2=T2)*(Y$2:Y2=Y2))——假定F2:F5、L2:L5、T2:T5、Y2:Y5分别都是相同，那么SUMPRODUCT((F$2:F2=F2)*(L$2:L2=L2)*(T$2:T2=T2)*(Y$2:Y2=Y2))=1，与SMALL搭配返回第一小，下拉成SUMPRODUCT((F$2:F3=F3)*(L$2:L3=L3)*(T$2:T3=T3)*(Y$2:Y3=Y3))得到2，返回第二小；再下拉，成SUMPRODUCT((F$2:F4=F4)*(L$2:L4=L4)*(T$2:T4=T4)*(Y$2:Y4=Y4))得到3，返回第三小……