有三个标准指数/匹配 [英] Index/Match with three criteria

问题描述

我已经搜查，搜查，搜查，搜查，我只能用两个标准查找索引/匹配的解决方案。

I have searched and searched and searched and searched, I can only find solutions for index/match with two criteria.

没有任何人有索引/匹配的解决方案有三个标准是什么？

does anyone have a solution for index/match with three criteria?

我的实际数据的样本，我想索引/匹配的一年，类型和名称来查找月列中的数据

as a sample of my actual data, i would like to index/match the year, type and name to find the data in the month column

推荐答案

您可以通过使用符合标准无限数量的 SUMPRODUCT（）的找到正确的行：

You can match an unlimited number of criteria by using SUMPRODUCT() to find the proper row:

=INDEX(D2:D9,SUMPRODUCT((A2:A9=2015)*(B2:B9="Revenue")*(C2:C9="Name 1")*ROW(2:9))-1)

编辑＃1：

斯科特的评论是正确的！的advantagesof的的 SUMPRODUCT（）的做法是，它不是一个数组公式，并可以扩展到处理许多标准。其缺点是，如果有 1 匹配行只会工作。搜索结果中使用的 SUMPRODUCT（）的解释非常好位置：

Scott's comment is correct! The advantagesof the SUMPRODUCT() approach is that it is not an array formula and can be expanded to handle many criteria. The disadvantage is that it will only work if there is 1 matching row.

The use of SUMPRODUCT() is explained very well here:

xlDynamic纸

这篇关于有三个标准指数/匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！