Trying to create a stock list program

2

Trying to create a stock list program

Trying to create a stock list program

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.

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.

RE: Trying to create a stock list program

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.

RE: Trying to create a stock list program

Quote (IDS)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.The Eng-Tips staff will check this out and take appropriate action.