Looking up values in a table 2

[aggman]

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,

 

[SacreBleu]

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.

 

[aggman]

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

 

[SacreBleu]

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.

 

[aggman]

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.

 

[SacreBleu]

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.

 

[TomBarsh]

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

 

[SacreBleu]

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.

 

[SacreBleu]

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.