indexing - Index and Match Multiple Criteria in Excel -
i have 2 tables of data same columns. first populated experimental data:
`treatment species timeofday temp light x m b 25 25 2 m b pm 26 50 3 m b pm 27 150 4 m c 25 25 5 m c 26 150 6 m c pm 27 500 7 m c pm 28 800 9 b 25 25 2 b pm 26 50 3 b pm 27 150 4 c 25 25 5 c 26 150 6 c pm 27 500 7 c pm 28 800 9 in second table want fill in "x" column values first table in given row a) of categorical values match table 1 , b) temp , light values closest (but won't exact) matching values in table 1.
for example, if in table 2 have:
treatment species timeofday temp light x m b pm 30 200 i want function give me 4 in x column. familiar indexing value in 1 column given match in , finding closest matching value in column, having trouble putting 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 in advance help.
this formula 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 array formula , must confirmed ctrl-shift-enter when exiting edit mode. copy down.
as other comment priority if difference same first in order.
Comments
Post a Comment