CODEWITHSUNDEEP
excelindexingmatchcriteria
Plantapus
Plantapus

Reputation: 153

Index and Match Multiple Criteria in Excel

I have two tables of data with the same columns. The first is populated with experimental data:

`Treatment  Species TimeofDay   Temp    Light   X
     M        B       AM         25      25     2
     M        B       PM         26      50     3
     M        B       PM         27      150    4
     M        C       AM         25      25     5
     M        C       AM         26      150    6
     M        C       PM         27      500    7
     M        C       PM         28      800    9
     A        B       AM         25       25    2
     A        B       PM         26       50    3
     A        B       PM         27       150   4
     A        C       AM         25       25    5
     A        C       AM         26      150    6
     A        C       PM         27      500    7
     A        C       PM         28      800    9

In the second table I want to fill in the "X" column with values from the first table so that in the given row A) all of the categorical values match exactly those from table one AND B) Temp and Light values are closest (but won't be exact) to matching values in table 1.

For example, if in Table 2 I have:

Treatment  Species  TimeofDay  Temp  Light  X
    M         B        PM       30    200

I would want the function to give me 4 in the X column. I am familiar with indexing a value in one column given a match in another and with finding a closest matching value in a column, but I am having trouble putting all these criteria together:

=INDEX(Table1!F$2:F$20,
MATCH(Table2!A$2,Table1!A$2:A$20,0))
MATCH(Table2!B$2,Table1!B$2:B$20,0))
MATCH(Table2!C$2,Table1!C$2:C$20,0))
MATCH(MIN(ABS(Table1!D$2:D$20-Table2!D$2)),ABS(Table1!D$2:D$20-Table2!D$2), 0))
MATCH(MIN(ABS(Table1!E$2:E$20-Table2!E$2)),ABS(Table1!E$2:E$20-Table2!E$2),0))

Thank you in advance for your help.

Upvotes: 1

Views: 706

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

This formula is working, put in F2:

=INDEX(Table1!F:F,MATCH(MIN(IF(A2=Table1!A:A,IF(B2 = Table1!B:B,IF(C2 = Table1!C:C,ABS((Table1!D:D+Table1!E:E)-(D2+E2)))))),IF(A2=Table1!A:A,IF(B2 = Table1!B:B,IF(C2 = Table1!C:C,ABS((Table1!D:D+Table1!E:E)-(D2+E2))))),0))

This is an array formula and must be confirmed with Ctrl-Shift-Enter when exiting edit mode. Then copy down.

As to your other comment the priority if the difference is the same will be the first in order.

Upvotes: 1

Related Questions