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

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

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

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