索引并匹配Excel中的多个标准-IndexandMatchMultipleCriteriainExcel

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.

在第二个表我想填写“X”列的值从第一个表,这样给行)的所有分类值完全匹配表一和B)温度和光线值接近(但不会确切)匹配的值在表1中。

For example, if in Table 2 I have:

例如，如果在表2中我有:

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:

我希望函数在X列中给出4。我熟悉在一个列中索引一个值，在另一个列中找到一个最接近的匹配值，但我在将所有这些标准放在一起时遇到了麻烦:

=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.

事先谢谢你的帮助。

1 个解决方案

=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))