×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Looking up values in a table2

## Looking up values in a table

(OP)
I am trying to write a spreadsheet which will select a pipe based on a certain Section Modulus.  I have a cell which contains a calculated section modulus based on stress.  I would like to then select a pipe which has a larger section modulus than the calculated one by looking up the values in a table.  Can anyone share some insight on how to do this?

Thanks,

### RE: Looking up values in a table

2
Before I cn answer this, are you looking for the least weight, any diameter pipe with Section Mudulus equal to or greater than required?
There is A LOOKUP and MATCH combination which will do this easily.

### RE: Looking up values in a table

(OP)
I am looking up only Standard pipe selections so only a section modulus exceeding the minimum required is all I need.

### RE: Looking up values in a table

Req'd modulus in cell A1.
List, in descending order, the moduli in cells B3 thru B20 (for example). The corresponding sizes of pipe, such as 4"dia x .25" wall, or whatever way you want to call them, are listed in A3 thru A20.
In cell C1, which will show your lookup value, =OFFSET(A2,MATCH(A1,B3:B20,-1),0)

Note the LOOKUP function is not required in this case.

### RE: Looking up values in a table

(OP)
Thanks SacreBleu!  That worked...just not exactly like I wanted it too, but it works and gives the correct answer.  I need to get a book on excel to help me with this stuff so I don't have to ask all over the world about how to do something.

### RE: Looking up values in a table

Aggman,
I don't know what you exactly need. I thought you needed to lookup the lightest pipe, with at least the required modulus, but that is a lot more involved. I haven't had any luck with books...I usually experiment with Excell, and look at other people's work for examples. The Excell "help" is not very good, and was never improved by MS all these years.

### RE: Looking up values in a table

The best Excel help I ever got was throughly reading (and working through) Microsoft's worksheet function and VBA manuals from cover to cover (still have them from Excel 5). I have a number of other books but the best bang came from that task.

Nowadays though we don't get printed manuals from Microsoft...

You can get great Excel help at Microsoft's public newsgroups (log into their newsserver at msnews.microsoft.com using Outlook Express).

### RE: Looking up values in a table

Tom,
The folks at MS have little interest in engineering applications of their Excell program. Probably not worth their time, and eats into their gargantuan profits.

### RE: Looking up values in a table

As a postscript, when I first started using the LOOKUP functions as a way to extract data from arrays, I ran into some confusion and incorrect results. When doing a "Search" for keywords here on Eng-Tips, I found ways of understanding how the whole process functions, using MATCH, OFFSET, and INDEX. I rarely use LOOKUP anymore, and am able to all the stuff I need with correct results.

#### 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.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!