多个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屋!