Table lookup using multiple qualifiers
 

I asked this question in a previous post but it kind of fizzled. I am trying
to return values from a table using two qualifiers to obtain the data.

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" (Example = "1/2")
Cell D7 is an input cell for "strength" (Example = "138"
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). Although the size will be an exact match, the
strength will not be an exact match but must equal or exceed the input
strength.

I was given the following formula to try but it only works when there is an
exact match for "strength"...

=INDEX(B2:B20,MATCH(D6&D7,A2:A20&C2:C20,0))
which is an array formula, so commit with Ctrl-Shift-Enter.

Thanks in advance for any help I can get!
S

Table lookup using multiple qualifiers
 

Try this *array* formula:

=INDEX(B2:B20,MATCH(1,(A2:A20=D6)*(C2:C20=D7),0))
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"TechMGR" wrote in message
...
I asked this question in a previous post but it kind of fizzled. I am
trying
to return values from a table using two qualifiers to obtain the data.

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" (Example = "1/2")
Cell D7 is an input cell for "strength" (Example = "138"
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). Although the size will be an exact match, the
strength will not be an exact match but must equal or exceed the input
strength.

I was given the following formula to try but it only works when there is
an
exact match for "strength"...

=INDEX(B2:B20,MATCH(D6&D7,A2:A20&C2:C20,0))
which is an array formula, so commit with Ctrl-Shift-Enter.

Thanks in advance for any help I can get!
S