Value Lookup based on Multiple Criteria

**vanharca** · 11-01-2012, 08:35 AM

I am putting a spreadsheet together that will need to select appropriate steel beam sizes based on given criteria. I have a sheet that lists all available sizes and the related properties of each. I would like to pick the lightest size that meets minimum criteria of two or three properties. An additional benefit would be to list the 5 lightest options from the list.

I am trying to use the index, match, and small functions together, but I am struggleing to get it to work. Any advice would be greatly appreciated.

Thanks,

Craig

**kbkumar** · 11-01-2012, 08:42 AM

Hi Craig,

Welcome to the forum. Please upload a sample workbook to help see how the dataset is organized and what you are trying to achieve here.

Thanks.

**vanharca** · 11-01-2012, 08:50 AM

Attached is the workbook I am working on. The data is in the "Beam Section Properties" tab and the formula will be entered on the "Underhung Runway Generator" Tab. On the URG tab, the two values on the top of column F are what I want to use as the criteria. I need to find the value from the data set with a minimum weight (Column B), a value in Column H that is equal to or greater than cell F3 on the URG tab, and a value in Column N equal to or greater than cell F4 on the URG tab.

Let me know if I can provide any more informaion to help.

Thanks,

Craig

**vanharca** · 11-01-2012, 11:22 AM

I am pretty sure I solved this problem with the following formula:

=INDEX('Beam Section Properties'!$A$2:$N$320,MATCH(SMALL(IF('Beam Section Properties'!$H$2:$H$320>'Underhung Runway Generator'!$F$3,IF('Beam Section Properties'!$N$2:$N$320>'Underhung Runway Generator'!$F$4,'Beam Section Properties'!$C$2:$C$320)),ROW(A1)),'Beam Section Properties'!$C$2:$C$320,0),1)

Craig