Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup against pivot table with multiple instances | Excel Worksheet Functions | |||
lookup and choose wih multiple tables of unequal column lengths | Excel Discussion (Misc queries) | |||
Multiple criteria LOOKUP | Excel Worksheet Functions | |||
lookup multiple occurrences of a value excel | Excel Worksheet Functions | |||
Lookup Table | Excel Worksheet Functions |