solution for multiple varialbles required

[bubba622]

I am attempting to create a formula for selecting the correct labor hours for a piping project...there are 4 criteria that have to be met or matched in order for the correct labor to be selected...(size, sch, mtl shop/fld,)...an abbreviated version of the table is as below...

SIZE	SCH	MTL	SHOP/FLD	LABOR
36	STD	CS	S	18
30	STD	CS	S	12
26	STD	CS	S	9.5
36	XH	CS	S	32
30	XH	CS	S	18
26	XH	CS	S	12.5
36	10S	SS	S	140
30	10S	SS	S	125
26	10S	SS	F	100

<tbody>
</tbody>

Ideally what I would like to happen, on a different worksheet, is to create a formula in a cell, that says, for example, if size=36, sch=std, mtl=cs, shop/fld=s then labor (in that cell) =18 ...is there a function in excel that will determine that all criteria has been met and select the correct labor figure...any help would be appreciated....thanks

 

[Taul]

Hi,
Try this

Excel 2010

	A	B	C	D	E
1	SIZE	SCH	MTL	SHOP/FLD	LABOR
2	36	STD	CS	S	18
3	30	STD	CS	S	12
4	26	STD	CS	S	9.5
5	36	XH	CS	S	32
6	30	XH	CS	S	18
7	26	XH	CS	S	12.5
8	36	10S	SS	S	140
9	30	10S	SS	S	125
10	26	10S	SS	F	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Excel 2010
	A	B	C	D	E
1	SIZE	SCH	MTL	SHOP/FLD	LABOR
2	26	Std	CS	S	9.5
Sheet1

formula in cell E2

=IFERROR(INDEX(Sheet2!E$2:E$10,MATCH(1,INDEX((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2)*(Sheet2!C2:C10=C2)*(Sheet2!D2:D10=D2),),FALSE)),"not listed")

Paul.

 

[Trevor_S]

Assuming your example data is in columns A to E, and the result you want will always be a number, try sumifs (like a sumif, but allowing multiple criteria)
=sumifs(E:E,A:A,36,B:B,"std",C:C,"cs",D:D,"s")
Remember to add the data table sheet name to the ranges if you're putting the formula in a different sheet.

 

[mikerickson]

How about =SUMIFS(E:E, A:A, 36, B:B, "std", C:C, "cs", D:D, "s")

Replacing the constants with their respective input cells.