Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with vlookup and return based on a formula
Hello!
I am working with sheet metal. For any given metal, I have various sizes available. I want to use vlookup my metal and then return the appropriate size based on the length of the part I need to take out of it. Example, my table looks like: METAL SIZE1 SIZE2 Stainless 36x90 48x120 PlainSteel 40x90 60x120 And the part I am building is using Stainless with a length of 80". So using the table, I want to lookup "stainless", and my formula answer would be "36x90" because that would fit my 80" part. I know how to set up vlookup, but I don't know how to incorporate a formula to assess my part length versus my sheet lengths available. Anyone know? Thanks for the help--laura |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with vlookup and return based on a formula
Instead of using labels of "Size1" and "Size2", it would be easier if you
placed your lenghts there (i.e., 90, 120, 150, etc...) Type of material in cell A8, length needed in B8, array* formula: =INDEX(B2:C3,MATCH(A8,A2:A3,0),MIN(IF(B8<=A1:C1,CO LUMN(A:C)-1,""))) Expand array sizes as needed to match your data. *Use Ctrl+Shift+Enter to confirm, not just enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "laurakberg" wrote: Hello! I am working with sheet metal. For any given metal, I have various sizes available. I want to use vlookup my metal and then return the appropriate size based on the length of the part I need to take out of it. Example, my table looks like: METAL SIZE1 SIZE2 Stainless 36x90 48x120 PlainSteel 40x90 60x120 And the part I am building is using Stainless with a length of 80". So using the table, I want to lookup "stainless", and my formula answer would be "36x90" because that would fit my 80" part. I know how to set up vlookup, but I don't know how to incorporate a formula to assess my part length versus my sheet lengths available. Anyone know? Thanks for the help--laura |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with vlookup and return based on a formula
Assuming your data in A2:C6
Criteria in D2: holds metal, E2 holds size In F2: =INDEX(A2:C6,MATCH(D2,A2:A6,0),MIN(IF(RIGHT(B2:C6, LEN(B2:C6)-FIND("x",B2:C6))+0=E2,COLUMN(B2:C6)))) ctrl+shift+enter, not just enter "laurakberg" wrote: Hello! I am working with sheet metal. For any given metal, I have various sizes available. I want to use vlookup my metal and then return the appropriate size based on the length of the part I need to take out of it. Example, my table looks like: METAL SIZE1 SIZE2 Stainless 36x90 48x120 PlainSteel 40x90 60x120 And the part I am building is using Stainless with a length of 80". So using the table, I want to lookup "stainless", and my formula answer would be "36x90" because that would fit my 80" part. I know how to set up vlookup, but I don't know how to incorporate a formula to assess my part length versus my sheet lengths available. Anyone know? Thanks for the help--laura |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with vlookup and return based on a formula
Thanks HUGE to LukeM & TM
Luke, my example wasn't as clear of a picture because my example table made it look like I have similar material lengths per column (like lengths of 90 or lengths of 120), but actually, all my materials have so many iterations of sizes (that's my struggle in all this). So I tried Teethless Mama's solution as well and it worked!!! Thanks to both of you--I tried so hard to figure this out on my own to no avail--so thanks so much for your input! I'm back on the road now! "Teethless mama" wrote: Assuming your data in A2:C6 Criteria in D2: holds metal, E2 holds size In F2: =INDEX(A2:C6,MATCH(D2,A2:A6,0),MIN(IF(RIGHT(B2:C6, LEN(B2:C6)-FIND("x",B2:C6))+0=E2,COLUMN(B2:C6)))) ctrl+shift+enter, not just enter "laurakberg" wrote: Hello! I am working with sheet metal. For any given metal, I have various sizes available. I want to use vlookup my metal and then return the appropriate size based on the length of the part I need to take out of it. Example, my table looks like: METAL SIZE1 SIZE2 Stainless 36x90 48x120 PlainSteel 40x90 60x120 And the part I am building is using Stainless with a length of 80". So using the table, I want to lookup "stainless", and my formula answer would be "36x90" because that would fit my 80" part. I know how to set up vlookup, but I don't know how to incorporate a formula to assess my part length versus my sheet lengths available. Anyone know? Thanks for the help--laura |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a working formula from a Vlookup | Excel Worksheet Functions | |||
VLOOKUP and return formula | Excel Worksheet Functions | |||
formula to return the value of a cell based on a looked up true reference | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |