Trying to create a stock list program 2

[Kenja824]

I like a challenge, so I dont mind figuring this out on my own. I am explaining what I am trying to accomplish, in case someone already has this or there is an easy way. Then I will explain the one formula I cant figure out.

Main Objective:
Using the simple chart I attached, I want to make a program where someone has to enter three numbers for the length, height, and width of a piece of metal and the program will look those numbers up in this chart and return what the correct stock size is. The reason I want this is because you cannot purchase steel in any stock size.

The rules are as follows....
If possible, we order in Inch x inch x metric (1" x 2" x 50mm) = 1020
If we only have one dimension that is inches, then we order in Inch x Metric X Metric, (1" x 50mm x 50mm) = STL PLT
It is always inches first then metric listed and within one system it is always the smallest number listed first.

So if T5 = 10, U5 = 19, & V5 = 35, the stock returned would be (3/8" x 3/4" x 35MM)


As I said, I am willing to work on figuring all this out, but if you want to save me the headaches because you like the challenge, feel free to do more.

WHAT I REALLY NEED.... T5 = 10, U5 = 19, & V5 = 35

In U7 I need a formula that will look for the number in T5 "10" in the range B1:R1, then inside that column (in this case it would be column D) it will look for the number in U5 "19" . If it finds the exact number, it will return that number. If it doesnt find the exact number it will return a blank cell or an X or something. If the number in T5 does not exist in the first range, it will also return as a blank or an X or something standard.

As you can see by the chart, some cells are blank. This is because these two stocks cannot be bought together. For instance, they do not offer 3/4" x 1 3/8" stock.

This is the only formula I really cant figure out so far. I cant figure out if it is a combo Hlookup & Vlookup or uses index or what.

 

[SkipVought]

Hi,

I like using Named Ranges for both understandability and maintainability. Here are the ranges I named in your sheet:
[pre]
Name Range Description
rInH =Sheet1!$B$2:$R$2 Inches Horizontal
rInV =Sheet1!$A$1:$A$39 Inches Vertical
rLookUpALL =Sheet1!$B$1:$R$39 MM Lookup Range
rLookUpH =Sheet1!$B$1:$R$1 Horizontal MM Lookup
rLookUpV =Sheet1!$B$1:$B$39 Vertical MM Lookup
[/pre]

The formula in...
[tt]
T7: =INDEX(rInH,1,MATCH(T5,rLookUpH,0))
U7: =INDEX(rInV,MATCH(INDEX(rLookUpALL,MATCH(U5,rLookUpV,0),MATCH(T5,rLookUpH,0)),rLookUpV,0),1)
[/tt]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's your file back.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Hey Skip

Thanks for the help. You threw something at me I dont remember ever seeing. lol You called them Named Ranges. I have been messing with the excel file you fixed trying to figure out how that works. I finally googled it. lol I cannot believe I have been using excel for so many years, always messing with formulas and never learned that you can name ranges. lol Should come in handy in the future.

 

[IDS]

lol I cannot believe I have been using excel for so many years, always messing with formulas and never learned that you can name ranges

Wow. Just goes to show that modern help systems are not that helpful I suppose.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

If you have column headings or row headings, you can SELECT all headings and data and use ...
[tt]
Formulas > Defined Names > Create from selection...Create names from values in the:
[/tt]
...and Check the location containing the names for the Named Range(s). I use this method a lot. I also use Structured Tables (introduced in 2007 version) as well.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Wow. Just goes to show that modern help systems are not that helpful I suppose.

The problem with Help systems is that one needs to have some idea something even exists or is possible before they can look it up in Help. lol Personally, those are my favorite things to learn. Things that are commonly known and some how have escaped me. They serve as great reminders that no matter how much we think we know, we can always look stupid. lol