Pull from list based on multiple criteria

**chitownsox14** · 04-20-2011, 10:29 AM

I have to set up a spreadsheet that has a list of inputs. I need to compare those inputs to certain criteria and combine them as output. I attached an excel sheet with three tabs to illustrate what I need done. I know I could have a bunch of logic statements, but the criteria list is much bigger than what I posted in the example. Is there an easy way to do this or am I stuck with logic statements? Thanks!

**NBVC** · 04-20-2011, 11:37 AM

In the Criteria tab, insert 2 column to separate the material from the size chart.

In new column B, enter materials.

In new column C, enter the maximum values only.

Then in the Outputs sheet:

in A2 enter:

=IF(Inputs!A2="","",Inputs!A2)

copied down as far as you want and across 4 columns.

in E2 enter:

=IF($A2="","",INDEX('Criteria Table'!D$2:D$11,MATCH(1,INDEX(('Criteria Table'!$B$2:$B$11=$D2)*('Criteria Table'!$C$2:$C$11>=MAX($B2:$C2)),0),0)))

copied down as far as you did with the first part and across 5 columns.

**chitownsox14** · 04-20-2011, 11:48 AM

Thank you so much for the quick reply! I did this and I am trying to figure out how exactly this works. If you are only putting in the max of the range, how will it know to only pull the values from that range? (ie "up thru 24" vs "25 thru 36"). What if my max dimension is 18. how does it know to pull from the "up thru 24" row as opposed to any other row?

**NBVC** · 04-20-2011, 12:12 PM

The MATCH function looks for the first match according to criteria set...

so I am looking for the first time that the material is matched and at the same time the Max column value is greater than or equal to the lookup value (your max dimension).

The first time the Max column is greater than or equal to 18 is at the 24 cell. The first time the Max column is greater than or equal to say 30, is at the 36 cell, etc...

This whole part of the formula:

MATCH(1,INDEX(('Criteria Table'!$B$2:$B$11=$D2)*('Criteria Table'!$C$2:$C$11>=MAX($B2:$C2)),0),0)

iniitally creates 2 arrays of TRUEs and FALSEs, one for each of the 2 conditions stated. The 2 arrays get multiplied together to form one array of 1's and 0's (where TRUE*TRUE=1 and all other combinations yield 0). So at all points where the array 1 condition is True and the array 2 condition is True, you have a match.... then MATCH() looks for the first '1' and returns that position, to be indexed against the INDEX array to get the correct item.