Multiple criteria within a range lookup

[Said Marin]

Let's say we have this data:

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl77, width: 64"]Part No[/TD]
[TD="class: xl77, width: 64"]ID [/TD]
[TD="class: xl77, width: 64"] OD[/TD]
[TD="class: xl77, width: 64"] OD +0.1[/TD]
[TD="class: xl77, width: 64"] OD -0.1[/TD]
[TD="class: xl77, width: 64"] L[/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"]196305[/TD]
[TD="class: xl76, align: right"]36.0[/TD]
[TD="class: xl76, align: right"]47.0[/TD]
[TD="class: xl76, align: right"]47.1[/TD]
[TD="class: xl76, align: right"]46.9[/TD]
[TD="class: xl76, align: right"]4.2[/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"]196306[/TD]
[TD="class: xl76, align: right"]75.0[/TD]
[TD="class: xl76, align: right"]90.5[/TD]
[TD="class: xl76, align: right"]90.6[/TD]
[TD="class: xl76, align: right"]90.4[/TD]
[TD="class: xl76, align: right"]6.3[/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"]220610[/TD]
[TD="class: xl76, align: right"]160.0[/TD]
[TD="class: xl76, align: right"]175.5[/TD]
[TD="class: xl76, align: right"]175.6[/TD]
[TD="class: xl76, align: right"]175.4[/TD]
[TD="class: xl76, align: right"]6.3[/TD]
[/TR]
[TR]
[TD="class: xl75, align: right"]220611[/TD]
[TD="class: xl76, align: right"]110.0[/TD]
[TD="class: xl76, align: right"]125.5[/TD]
[TD="class: xl76, align: right"]125.6[/TD]
[TD="class: xl76, align: right"]125.4[/TD]
[TD="class: xl76, align: right"]6.3[/TD]
[/TR]
[TR]
[TD="align: right"]238627[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]260.5[/TD]
[TD="align: right"]260.6[/TD]
[TD="align: right"]260.4[/TD]
[TD="align: right"]8.1[/TD]
[/TR]
[TR]
[TD="align: right"]238628[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]220.5[/TD]
[TD="align: right"]220.6[/TD]
[TD="align: right"]220.4[/TD]
[TD="align: right"]8.1[/TD]
[/TR]
[TR]
[TD="align: right"]239005[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]195.1[/TD]
[TD="align: right"]195.2[/TD]
[TD="align: right"]195[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
[TR]
[TD="align: right"]240669[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40.7[/TD]
[TD="align: right"]40.8[/TD]
[TD="align: right"]40.6[/TD]
[TD="align: right"]4.2[/TD]
[/TR]
[TR]
[TD="align: right"]240917[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]55.1[/TD]
[TD="align: right"]55.2[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
</tbody>[/TABLE]

and we are trying to find out if there is a match (or close withing the range +/- 0.1) in the following table:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl77, width: 64"]Material [/TD]
[TD="class: xl77, width: 64"] Y_ID[/TD]
[TD="class: xl77, width: 64"] Y_OD[/TD]
[TD="class: xl77, width: 64"] Y_L[/TD]
[/TR]
[TR]
[TD]109.03512[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]55.1[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
[TR]
[TD]109.04112[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]60.1[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
[TR]
[TD]109.04114[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]71.1[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
[TR]
[TD]109.04116[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]85.1[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
[TR]
[TD]109.90727[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]260.5[/TD]
[TD="align: right"]8.1[/TD]
[/TR]
[TR]
[TD]109.39089[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]220.5[/TD]
[TD="align: right"]8.1[/TD]
[/TR]
[TR]
[TD]109.39088[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]195.1[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
[TR]
[TD]109.39314[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40.7[/TD]
[TD="align: right"]4.2[/TD]
[/TR]
[TR]
[TD]109.03512[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]55.1[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
</tbody>[/TABLE]

If there is a match or close enough validation, the formula should return the material matching the part number.

I'd tried several index match formulas but i can't find one which actually works, not sure if it is because the ranges or something else.

Can anyone help me with this?

 

[Said Marin]

sorry, i forgot to mention that the formula should compare ID, OD & L with Y_ID, Y_OD & Y_L. And i want the formula to compare within the range +/- 0.1 for OD (columns OD +0.1 and OD -0.1).

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Part No​

[/td][td]

ID​

[/td][td]

OD​

[/td][td]

OD +0.1​

[/td][td]

OD -0.1​

[/td][td]

L​

[/td][td][/td][td]

Material​

[/td][td]

Y_ID​

[/td][td]

Y_OD​

[/td][td]

Y_L​

[/td][td]

Part No​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

196305​

[/td][td]

36​

[/td][td]

47​

[/td][td]

47,1​

[/td][td]

46,9​

[/td][td]

4,2​

[/td][td][/td][td]

10.903.512​

[/td][td]

40​

[/td][td]

55​

[/td][td]

6,3​

[/td][td]

240917​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

196306​

[/td][td]

75​

[/td][td]

90,5​

[/td][td]

90,6​

[/td][td]

90,4​

[/td][td]

6,3​

[/td][td][/td][td]

10.904.112​

[/td][td]

45​

[/td][td]

60,1​

[/td][td]

6,3​

[/td][td]

Not Found​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

220610​

[/td][td]

160​

[/td][td]

175,5​

[/td][td]

175,6​

[/td][td]

175,4​

[/td][td]

6,3​

[/td][td][/td][td]

10.904.114​

[/td][td]

56​

[/td][td]

71,1​

[/td][td]

6,3​

[/td][td]

Not Found​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

220611​

[/td][td]

110​

[/td][td]

125,5​

[/td][td]

125,6​

[/td][td]

125,4​

[/td][td]

6,3​

[/td][td][/td][td]

10.904.116​

[/td][td]

70​

[/td][td]

85,1​

[/td][td]

6,3​

[/td][td]

Not Found​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

238627​

[/td][td]

240​

[/td][td]

260,5​

[/td][td]

260,6​

[/td][td]

260,4​

[/td][td]

8,1​

[/td][td][/td][td]

10.990.727​

[/td][td]

240​

[/td][td]

260,5​

[/td][td]

8,1​

[/td][td]

238627​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

238628​

[/td][td]

200​

[/td][td]

220,5​

[/td][td]

220,6​

[/td][td]

220,4​

[/td][td]

8,1​

[/td][td][/td][td]

10.939.089​

[/td][td]

200​

[/td][td]

220,5​

[/td][td]

8,1​

[/td][td]

238628​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

239005​

[/td][td]

180​

[/td][td]

195,1​

[/td][td]

195,2​

[/td][td]

195​

[/td][td]

6,3​

[/td][td][/td][td]

10.939.088​

[/td][td]

180​

[/td][td]

195,1​

[/td][td]

6,3​

[/td][td]

239005​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

240669​

[/td][td]

30​

[/td][td]

40,7​

[/td][td]

40,8​

[/td][td]

40,6​

[/td][td]

4,2​

[/td][td][/td][td]

10.939.314​

[/td][td]

30​

[/td][td]

40,7​

[/td][td]

4,2​

[/td][td]

240669​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

240917​

[/td][td]

40​

[/td][td]

55,1​

[/td][td]

55,2​

[/td][td]

55​

[/td][td]

6,3​

[/td][td][/td][td]

10.903.512​

[/td][td]

40​

[/td][td]

55,1​

[/td][td]

6,3​

[/td][td]

240917​

[/td][/tr]
[/table]

Formula in L2 copied down
=IFERROR(IF(AND(ROUND(ABS(INDEX(C$2:C$10,MATCH(I2,B$2:B$10,0))-J2),1)<=0.1,ISNUMBER(INDEX(F$2:F$10,MATCH(I2,B$2:B$10,0)))),INDEX(A$2:A$10,MATCH(I2,B$2:B$10,0)),""),"Not Found")

Hope this helps

M.