Vlookup with multiple coloums and criteria

[YamahaG22]

I have set up a table with 5 columns such as below;

[TABLE="width: 460"]
<tbody>[TR]
[TD]DIAMETER[/TD]
[TD][/TD]
[TD]WALL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Low Ø[/TD]
[TD]HI Ø[/TD]
[TD]LO[/TD]
[TD]HI[/TD]
[TD]OD ±[/TD]
[/TR]
[TR]
[TD]0.000[/TD]
[TD]12.740[/TD]
[TD]0.510[/TD]
[TD]1.240[/TD]
[TD]0.100[/TD]
[/TR]
[TR]
[TD]12.700[/TD]
[TD]25.400[/TD]
[TD]0.510[/TD]
[TD]1.650[/TD]
[TD]0.130[/TD]
[/TR]
[TR]
[TD]12.700[/TD]
[TD]25.400[/TD]
[TD]1.660[/TD]
[TD]3.400[/TD]
[TD]0.250[/TD]
[/TR]
[TR]
[TD]25.400[/TD]
[TD]38.100[/TD]
[TD]0.640[/TD]
[TD]1.650[/TD]
[TD]0.200[/TD]
[/TR]
[TR]
[TD]25.400[/TD]
[TD]38.100[/TD]
[TD]1.660[/TD]
[TD]3.400[/TD]
[TD]0.250[/TD]
[/TR]
[TR]
[TD]38.100[/TD]
[TD]50.800[/TD]
[TD]0.640[/TD]
[TD]1.240[/TD]
[TD]0.250[/TD]
[/TR]
[TR]
[TD]38.100[/TD]
[TD]50.800[/TD]
[TD]1.250[/TD]
[TD]2.110[/TD]
[TD]0.280[/TD]
[/TR]
[TR]
[TD]50.800[/TD]
[TD]63.500[/TD]
[TD]2.120[/TD]
[TD]3.780[/TD]
[TD]0.300[/TD]
[/TR]
[TR]
[TD]50.800[/TD]
[TD]63.500[/TD]
[TD]0.810[/TD]
[TD]1.650[/TD]
[TD]0.300[/TD]
[/TR]
[TR]
[TD]50.800[/TD]
[TD]63.500[/TD]
[TD]1.660[/TD]
[TD]2.770[/TD]
[TD]0.330[/TD]
[/TR]
[TR]
[TD]63.500[/TD]
[TD]88.900[/TD]
[TD]2.780[/TD]
[TD]4.190[/TD]
[TD]0.360[/TD]
[/TR]
[TR]
[TD]63.500[/TD]
[TD]88.900[/TD]
[TD]0.810[/TD]
[TD]4.190[/TD]
[TD]0.510[/TD]
[/TR]
[TR]
[TD]88.900[/TD]
[TD]127.000[/TD]
[TD]4.190[/TD]
[TD]10.000[/TD]
[TD]0.510[/TD]
[/TR]
[TR]
[TD]88.900[/TD]
[TD]127.000[/TD]
[TD]0.890[/TD]
[TD]4.190[/TD]
[TD]0.640[/TD]
[/TR]
[TR]
[TD]127.000[/TD]
[TD]190.500[/TD]
[TD]1.240[/TD]
[TD]6.350[/TD]
[TD]0.640[/TD]
[/TR]
[TR]
[TD]127.000[/TD]
[TD]190.500[/TD]
[TD]6.360[/TD]
[TD]10.000[/TD]
[TD]0.760[/TD]
[/TR]
</tbody>[/TABLE]

I would like to input the diameter and the wall thickness to achieve an output of the corresponding tolerance in the 5th column.

Ex. If diameter = 90.1 & Wall thickness = 1.2 THEN Tol = 0.640

Can you provide a VLOOKUP or correct formula to do so?

Thanks Glen

 

[Toadstool]

Glen,

What do you want to do with the overlaps?

Diameter= 50.800 Wall= 2.5 Tolerance= either 0.300 or 0.330
Diameter= 63.500 Wall= 3 Tolerance= either 0.360 or 0.510
Diameter= 88.900 Wall= 4.190 Tolerance= 0.510 or 0.640

 

[YamahaG22]

Please assume the overlaps are errors in the data table. There should only be one return for the query given diam. and wall.

 

[JV0710]

HI

Assuming that the overlaps are errors, the following should work

Your data table is in range: A1:E18 (Including headers)

In G1 you enter 90.1 for Diameter and in H1 you enter 1.2 for Thickness

Then in i1 the following array formula should work: ( it is an array formula and must be confirmed with “Ctrl+Shift+Enter” and not just “enter”

{=INDEX(E3:E18,MATCH(1,(A3:A18<=G2)*(B3:B18>=G2)*(C3:C18<=H2)*(D3:D18>=H2),0),1)}

 

[YamahaG22]

HI

Assuming that the overlaps are errors, the following should work

Your data table is in range: A1:E18 (Including headers)

In G1 you enter 90.1 for Diameter and in H1 you enter 1.2 for Thickness

Then in i1 the following array formula should work: ( it is an array formula and must be confirmed with “Ctrl+Shift+Enter” and not just “enter”

{=INDEX(E3:E18,MATCH(1,(A3:A18<=G2)*(B3:B18>=G2)*(C3:C18<=H2)*(D3:D18>=H2),0),1)}

Thanks for you suggestion, however....

[TABLE="width: 576"]
<colgroup><col span="9"></colgroup><tbody>[TR]
[TD="colspan: 2"]DIAMETER[/TD]
[TD]WALL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]90.1[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Low Ø[/TD]
[TD]HI Ø[/TD]
[TD]LO[/TD]
[TD]HI[/TD]
[TD]OD ±[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[TD="align: right"]12.74[/TD]
[TD="align: right"]0.51[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]0.10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12.70[/TD]
[TD="align: right"]25.40[/TD]
[TD="align: right"]0.51[/TD]
[TD="align: right"]1.65[/TD]
[TD="align: right"]0.13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12.70[/TD]
[TD="align: right"]25.40[/TD]
[TD="align: right"]1.66[/TD]
[TD="align: right"]3.40[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25.40[/TD]
[TD="align: right"]38.10[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]1.65[/TD]
[TD="align: right"]0.20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25.40[/TD]
[TD="align: right"]38.10[/TD]
[TD="align: right"]1.66[/TD]
[TD="align: right"]3.40[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]38.10[/TD]
[TD="align: right"]50.80[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]38.10[/TD]
[TD="align: right"]50.80[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]2.11[/TD]
[TD="align: right"]0.28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50.80[/TD]
[TD="align: right"]63.50[/TD]
[TD="align: right"]2.12[/TD]
[TD="align: right"]3.78[/TD]
[TD="align: right"]0.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50.80[/TD]
[TD="align: right"]63.50[/TD]
[TD="align: right"]0.81[/TD]
[TD="align: right"]1.65[/TD]
[TD="align: right"]0.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50.80[/TD]
[TD="align: right"]63.50[/TD]
[TD="align: right"]1.66[/TD]
[TD="align: right"]2.77[/TD]
[TD="align: right"]0.33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]63.50[/TD]
[TD="align: right"]88.90[/TD]
[TD="align: right"]2.78[/TD]
[TD="align: right"]4.19[/TD]
[TD="align: right"]0.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]63.50[/TD]
[TD="align: right"]88.90[/TD]
[TD="align: right"]0.81[/TD]
[TD="align: right"]4.19[/TD]
[TD="align: right"]0.51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]88.90[/TD]
[TD="align: right"]127.00[/TD]
[TD="align: right"]4.20[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]0.51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]88.90[/TD]
[TD="align: right"]127.00[/TD]
[TD="align: right"]0.89[/TD]
[TD="align: right"]4.19[/TD]
[TD="align: right"]0.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127.00[/TD]
[TD="align: right"]190.50[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]6.35[/TD]
[TD="align: right"]0.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127.00[/TD]
[TD="align: right"]190.50[/TD]
[TD="align: right"]6.36[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]0.76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With the errors in the table corrected and the supplied formula I failed to achieve a correct output.

 

[YamahaG22]

Thanks for you suggestion, however....

[TABLE="width: 576"]
<tbody>[TR]
[TD="colspan: 2"]DIAMETER[/TD]
[TD]WALL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]90.1[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Low Ø[/TD]
[TD]HI Ø[/TD]
[TD]LO[/TD]
[TD]HI[/TD]
[TD]OD ±[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[TD="align: right"]12.74[/TD]
[TD="align: right"]0.51[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]0.10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12.70[/TD]
[TD="align: right"]25.40[/TD]
[TD="align: right"]0.51[/TD]
[TD="align: right"]1.65[/TD]
[TD="align: right"]0.13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12.70[/TD]
[TD="align: right"]25.40[/TD]
[TD="align: right"]1.66[/TD]
[TD="align: right"]3.40[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25.40[/TD]
[TD="align: right"]38.10[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]1.65[/TD]
[TD="align: right"]0.20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25.40[/TD]
[TD="align: right"]38.10[/TD]
[TD="align: right"]1.66[/TD]
[TD="align: right"]3.40[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]38.10[/TD]
[TD="align: right"]50.80[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]38.10[/TD]
[TD="align: right"]50.80[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]2.11[/TD]
[TD="align: right"]0.28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50.80[/TD]
[TD="align: right"]63.50[/TD]
[TD="align: right"]2.12[/TD]
[TD="align: right"]3.78[/TD]
[TD="align: right"]0.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50.80[/TD]
[TD="align: right"]63.50[/TD]
[TD="align: right"]0.81[/TD]
[TD="align: right"]1.65[/TD]
[TD="align: right"]0.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50.80[/TD]
[TD="align: right"]63.50[/TD]
[TD="align: right"]1.66[/TD]
[TD="align: right"]2.77[/TD]
[TD="align: right"]0.33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]63.50[/TD]
[TD="align: right"]88.90[/TD]
[TD="align: right"]2.78[/TD]
[TD="align: right"]4.19[/TD]
[TD="align: right"]0.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]63.50[/TD]
[TD="align: right"]88.90[/TD]
[TD="align: right"]0.81[/TD]
[TD="align: right"]4.19[/TD]
[TD="align: right"]0.51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]88.90[/TD]
[TD="align: right"]127.00[/TD]
[TD="align: right"]4.20[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]0.51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]88.90[/TD]
[TD="align: right"]127.00[/TD]
[TD="align: right"]0.89[/TD]
[TD="align: right"]4.19[/TD]
[TD="align: right"]0.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127.00[/TD]
[TD="align: right"]190.50[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]6.35[/TD]
[TD="align: right"]0.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127.00[/TD]
[TD="align: right"]190.50[/TD]
[TD="align: right"]6.36[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]0.76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With the errors in the table corrected and the supplied formula I failed to achieve a correct output.

Apologies, my error with the validation method of the formula... your solution does work and returns the correct output.
Thanks for your time and solution.

 

[JV0710]

Glad it worked ... Thanks for the feedback

 

[Peter_SSs]

In case you were looking for a version that did not require the C+S+E you could also try

=AGGREGATE(15,6,E3:E18/((A3:A18<=G2)*(B3:B18>=G2)*(C3:C18<=H2)*(D3:D18>=H2)),1)

 

[JV0710]

Thanks very much for that option Peter ... Appreciate it

 

[Peter_SSs]

Thanks very much for that option Peter ... Appreciate it

Cheers. Glad to contribute.