Vlookup with multiple coloums and criteria

YamahaG22

New Member
Joined
Aug 13, 2019
Messages
7
I have set up a table with 5 columns such as below;

DIAMETERWALL
Low ØHI ØLOHIOD ±
0.00012.7400.5101.2400.100
12.70025.4000.5101.6500.130
12.70025.4001.6603.4000.250
25.40038.1000.6401.6500.200
25.40038.1001.6603.4000.250
38.10050.8000.6401.2400.250
38.10050.8001.2502.1100.280
50.80063.5002.1203.7800.300
50.80063.5000.8101.6500.300
50.80063.5001.6602.7700.330
63.50088.9002.7804.1900.360
63.50088.9000.8104.1900.510
88.900127.0004.19010.0000.510
88.900127.0000.8904.1900.640
127.000190.5001.2406.3500.640
127.000190.5006.36010.0000.760

<tbody>
</tbody>

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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Last edited:
Upvote 0
Please assume the overlaps are errors in the data table. There should only be one return for the query given diam. and wall.
 
Upvote 0
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)}
 
Upvote 0
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....

DIAMETERWALL90.11.2#VALUE!
Low ØHI ØLOHIOD ±
0.0012.740.511.240.10
12.7025.400.511.650.13
12.7025.401.663.400.25
25.4038.100.641.650.20
25.4038.101.663.400.25
38.1050.800.641.240.25
38.1050.801.252.110.28
50.8063.502.123.780.30
50.8063.500.811.650.30
50.8063.501.662.770.33
63.5088.902.784.190.36
63.5088.900.814.190.51
88.90127.004.2010.000.51
88.90127.000.894.190.64
127.00190.501.246.350.64
127.00190.506.3610.000.76

<colgroup><col span="9"></colgroup><tbody>
</tbody>

With the errors in the table corrected and the supplied formula I failed to achieve a correct output.
 
Upvote 0
Thanks for you suggestion, however....

DIAMETERWALL90.11.2#VALUE!
Low ØHI ØLOHIOD ±
0.0012.740.511.240.10
12.7025.400.511.650.13
12.7025.401.663.400.25
25.4038.100.641.650.20
25.4038.101.663.400.25
38.1050.800.641.240.25
38.1050.801.252.110.28
50.8063.502.123.780.30
50.8063.500.811.650.30
50.8063.501.662.770.33
63.5088.902.784.190.36
63.5088.900.814.190.51
88.90127.004.2010.000.51
88.90127.000.894.190.64
127.00190.501.246.350.64
127.00190.506.3610.000.76

<tbody>
</tbody>

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. :)
 
Upvote 0
Glad it worked ... Thanks for the feedback
 
Upvote 0
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)
 
Upvote 0
Thanks very much for that option Peter ... Appreciate it
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top