5 column problem.

[DRAKEGL]

I am trying to get to the value in the fifth column (col.). Each col. before it may have duplicate info. col "a" is spec, "b" is width inches, "c" is length mini inches, "d" is max length inches, and "e" is the vaule I need. I have tried match, index, vlookkup, greater than & less than, if(and, iserror, etc. I realize the answer is a mixture of the above, I just can't figure it out. All help would be appreciated. Example is below.

I have pipe, spec of "Q30", it's width is "47.5 inches", its length is 300 inches. I need to know what order I can apply the pipe to. Row 8 should be the correct one, giving me the vaulue of 123456.

a1: spec b1:width c1: min length d1: max length e1: order
a2: q10 b2: 47.5 c2: 290 d2: 330 e2: 543218
a3: q45 b3: 44.0 c3: 260 d3: 290 e3: 987656
a4: q30 b4: 46.5 c4: 365 d4: 380 e4: 543214
a5: q15 b5: 44.5 c5: 280 d5: 300 e5: 675842
a6: q50 b6: 30.5 c6: 300 d6: 320 e6: 123456
a7: q90 b7: 60.0 c7: 260 d7: 290 e7: 543210
a8: q30 b8: 47.5 c8: 290 d8: 320 e8: 123456
a9: q45 b9: 30.0 c9: 365 d9: 380 e9: 985421
a10: q10 b10: 45.5 c10: 290 d10: 320 e10: 234589

 

[njimack]

Note: the below approach will give incorrect results if more than one row meets all the criteria:

Excel Workbook
	A	B	C	D	E	F	G	H
1	spec	width	min length	max length	order
2	q10	47.5	290	330	543218		SPEC:	Q30
3	q45	44	260	290	987656		Width:	47.5
4	q30	46.5	365	380	543214		Length	300
5	q15	44.5	280	300	675842
6	q50	30.5	300	320	123456			123456
7	q90	60	260	290	543210
8	q30	47.5	290	320	123456
9	q45	30	365	380	985421
10	q10	45.5	290	320	234589
Sheet1

 

[shg]

Good application for Advanced Filter

<TABLE style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=245 x:str><COLGROUP><COL style="WIDTH: 37pt" span=5 width=49><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16 width=49>Spec</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=49>Wid</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=49>MinLen</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=49>MaxLen</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; WIDTH: 37pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=49> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>Q30</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>47.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29><=300</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29>>=300</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16 width=49>Spec</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=49>Wid</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=49>MinLen</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=49>MaxLen</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 width=49>order</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>q10</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num>47.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>290</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>330</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>543218</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>q45</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num>44.0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>260</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>290</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>987656</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>q30</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num>46.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>365</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>380</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>543214</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>q15</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num>44.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>280</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>300</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>675842</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>q50</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num>30.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>300</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>320</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>123456</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>q90</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num>60.0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>260</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>290</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>543210</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>q30</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num>47.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>290</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>320</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>123456</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>q45</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num>30.0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>365</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>380</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>985421</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 height=16>q10</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num>45.5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>290</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>320</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl29 align=right x:num>234589</TD></TR></TBODY></TABLE>

The range at the top is the criteria range.

 

[Rick Rothstein]

Ignore... Posted in error.

 

[DRAKEGL]

I have never used sumproduct before, thank you. I do have a question, what does the "--"mean?

 

[njimack]

I have never used sumproduct before, thank you. I do have a question, what does the "--"mean?

See http://www.mcgimpsey.com/excel/formulae/doubleneg.html and http://www.xldynamic.com/source/xld.SUMPRODUCT.html