Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique values - Pivot Table | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Charts and Charting in Excel | |||
how do I create a pivot table based on multiple sheets | Excel Discussion (Misc queries) | |||
Pivot Table - Multiple consolidation Range | Excel Worksheet Functions | |||
Multiple table lookup | Excel Discussion (Misc queries) |