look up table values with multiple conditions
 

I have a fairly basic table of bolts and their attributes...

A B C
1 SIZE LENGTH STRENGTH
2 .5 1/2 100
3 .5 3/4 150
4 .75 1/2 150
5 .75 3/4 200

Cell D6 is an input cell for size, cell D7 is an input cell for strength,
Cell D8 is the resultant lookup

I want to do a vlookup(?) that finds the correct length based upon BOTH size
(D6) AND strength (D7). I am sure there is a way to do this but I am used to
only having one variable to do the vlookup with.

=vlookup(D6, A1:C5, 2) where the result is =D7 (How do I do this?)

look up table values with multiple conditions
 

=INDEX(B2:B20,MATCH(D6&D7,A2:A20&C2:C20,0))

which is an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TechMGR" wrote in message
...
I have a fairly basic table of bolts and their attributes...

A B C
1 SIZE LENGTH STRENGTH
2 .5 1/2 100
3 .5 3/4 150
4 .75 1/2 150
5 .75 3/4 200

Cell D6 is an input cell for size, cell D7 is an input cell for strength,
Cell D8 is the resultant lookup

I want to do a vlookup(?) that finds the correct length based upon BOTH
size
(D6) AND strength (D7). I am sure there is a way to do this but I am used
to
only having one variable to do the vlookup with.

=vlookup(D6, A1:C5, 2) where the result is =D7 (How do I do this?)

look up table values with multiple conditions
 

Hi Bob,
Thanks for the quick reply. It will take me a bit to digest what all you
are doing there but I am sure it is the solution I am looking for. A couple
of questions...what do you mean by "which is an array formula, so commit with
Ctrl-Shift-Enter." Not following you there. Also, is an ampersand ok to use
in a formula? If so I definitely learn something new everyday!
Thanks again,
Shannon

"Bob Phillips" wrote:

=INDEX(B2:B20,MATCH(D6&D7,A2:A20&C2:C20,0))

which is an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TechMGR" wrote in message
...
I have a fairly basic table of bolts and their attributes...

A B C
1 SIZE LENGTH STRENGTH
2 .5 1/2 100
3 .5 3/4 150
4 .75 1/2 150
5 .75 3/4 200

Cell D6 is an input cell for size, cell D7 is an input cell for strength,
Cell D8 is the resultant lookup

I want to do a vlookup(?) that finds the correct length based upon BOTH
size
(D6) AND strength (D7). I am sure there is a way to do this but I am used
to
only having one variable to do the vlookup with.

=vlookup(D6, A1:C5, 2) where the result is =D7 (How do I do this?)

look up table values with multiple conditions
 

Now that I have had some time to digest this formula, it makes sense. The
only issue I have at this point is that I believe the formula as you have
written it will only return a reply if there is an exact match for both
column A (Size) and Column C (Strength). How do I keep the exact match for
column A (i.e. match type =0) yet choose the first strength greater than or
equal to cell D7 (i.e. match type = -1)?
Thanks again,
S

"TechMGR" wrote:

Hi Bob,
Thanks for the quick reply. It will take me a bit to digest what all you
are doing there but I am sure it is the solution I am looking for. A couple
of questions...what do you mean by "which is an array formula, so commit with
Ctrl-Shift-Enter." Not following you there. Also, is an ampersand ok to use
in a formula? If so I definitely learn something new everyday!
Thanks again,
Shannon

"Bob Phillips" wrote:

=INDEX(B2:B20,MATCH(D6&D7,A2:A20&C2:C20,0))

which is an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TechMGR" wrote in message
...
I have a fairly basic table of bolts and their attributes...

A B C
1 SIZE LENGTH STRENGTH
2 .5 1/2 100
3 .5 3/4 150
4 .75 1/2 150
5 .75 3/4 200

Cell D6 is an input cell for size, cell D7 is an input cell for strength,
Cell D8 is the resultant lookup

I want to do a vlookup(?) that finds the correct length based upon BOTH
size
(D6) AND strength (D7). I am sure there is a way to do this but I am used
to
only having one variable to do the vlookup with.

=vlookup(D6, A1:C5, 2) where the result is =D7 (How do I do this?)