CODEWITHSUNDEEP
excelindexingexcel-formulamatchvlookup
Alex Moore
Alex Moore

Reputation: 11

Multiple VLOOKUP or 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))),"")

This returns a person's name from my list of data based on the answer to a criteria being "YES".

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.

Upvotes: 1

Views: 917

Answers (1)

user4039065
user4039065

Reputation:

Those increments and row numbers are unnecessarily complicated if you understand what they are trying to do.

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.

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.

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))),"")

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))),"")

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

Upvotes: 1

Related Questions