多个VLOOKUP或INDEX MATCH [英] Multiple VLOOKUP or INDEX MATCH
问题描述
我正在尝试使用INDEX MATCH返回一个基于多个条件的值。有人帮助我了解一点，但我找不到一种添加额外标准的方法。我从分钟起的公式是：
I am trying to use INDEX MATCH to return a value based on multiple criteria. Someone has helped me get to a point but I cannot find a way to add additional criteria. The formula I am working from at the minute is:
=IFERROR(INDEX(DATA!$H$2:$H$2000,SMALL(IF(DATA!$K$2:$K$2000="Yes",ROW(DATA!$A$2:$A$2000)-ROW(DATA!$A$2)+1,""),ROWS(DATA!$A$2:A2))),"")
从我的列表中返回一个人的姓名基于对YES的标准的答案的数据。
This returns a person's name from my list of data based on the answer to a criteria being "YES".
我需要添加两个标准来返回较少的结果。这将基于一个月份和一年，两者都以不同的单元格显示，即 10 和 2015 。当行中的其他值为YES， 10 和 2015 时，我只想返回索引列表。
I need to add two more criteria to return fewer results. This will be based on a month and year, both displayed numerically in different cells, i.e. 10 and 2015. I only want to return my index list when the other values in the row are "YES", 10 and 2015.
推荐答案
如果您了解他们正在努力做什么，这些增量和行号就不必要了。
Those increments and row numbers are unnecessarily complicated if you understand what they are trying to do.
ROWS（DATA！$ A $ 2：A2）只需将 1，2，3 ... 你填满这可以简化为 ROW（1：1）。 $ code $ R $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ：$ 1999）只要您意识到您正在返回 H2：H2000中的位置，而不是工作表上的实际行号。请注意，没有一个简化引用使用工作表名称或列名称。他们根本不需要。
The ROWS(DATA!$A$2:A2) simply increments 1, 2, 3... as you fill down. This can be simplified to ROW(1:1). The ROW(DATA!$A$2:$A$2000)-ROW(DATA!$A$2)+1 can be simplified to ROW($1:$1999) as long as you realize that you are returning the position within H2:H2000, not the actual row number on the worksheet. Note that none of the simplified referenced use either a worksheet name or a column designation. They simply are not necessary.
此外， 15 子功能（ SMALL ）with optiona 6 （忽略错误）。通过将任何不匹配的东西转到＃DIV / 0！错误中，只剩下匹配的行。
In addition, the AGGREGATE¹ function makes quick work of conditions when you use the 15 sub-function (SMALL) with optiona 6 (ignore errors). By turning anything that doesn't match into a #DIV/0! error, you are left with only the rows that match.
XL2010及更高版本的标准公式：
A standard formula for XL2010 and higher:
=IFERROR(INDEX(DATA!$H$2:$H$2000, AGGREGATE(15, 6, ROW($1:$1999)/((DATA!$K$2:$K$2000="Yes")*(DATA!$L$2:$L$2000=10)*(DATA!$M$2:$M$2000=2015)), ROW(1:1))),"")
XL2007及更早版本的标准公式：
A standard formula for XL2007 and earlier:
=IFERROR(INDEX(DATA!$H$2:$H$2000, SMALL(INDEX(ROW($1:$1999)+((DATA!$K$2:$K$2000<>"Yes")+(DATA!$L$2:$L$2000<>10)+(DATA!$M$2:$M$2000<>2015))*1E+99, , ), ROW(1:1))),"")
¹ AGGREGATE功能与Excel 2010一起引入。它在早期版本中不可用。
¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.
这篇关于多个VLOOKUP或INDEX MATCH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
