Multi-variable lookup for sizing gas piping

[Meaglin]

I am a mechanical engineer and I am trying to streamline our process as it stand now when we size gas piping it requires manually checking tables in code books. Our firm only deals with four variations on a regular basis so keying the schedules from the code book into excel should not be a major undertaking. I am trying to automate this process but it requires looking up multiple values and I cannot figure out the formula.

This is as concisely as I can word what needs to happen in the attached excel workbook. Formula located in cell D4 looks at cell C2 and finds the matching value in the table on the gas data sheet in column G. Using the row that value is located on it then looks at B4 and finds the exact or next largest value from the gas data sheet. Then it traces that column up to row 7 returning the value located there.

[JBeaucaire]

First some cosmetic corrections...
1) on the Gas Piper Sizer cell E5, change the formula to this, then copy down: =IF(B5="", "", B5+E4)

2) Cell C23 change to: =MAX(E4:E22)

===========
Now, on the Gas Data sheet

3) I moved the data table to column 1.

4) The title of the data table is now directly above the data, also in column 1.

5) I moved the drop down validation table reference off to the right out of the way, it's now in R1:R4 and I changed the DV formula in A1 of the Sizer sheet to use this.

6) CRITICAL: the data itself (highlighted in yellow) has all been adjusted to show the START of each tier instead of the end. So the 1/2" pipe starts at 0 length, the 3/4 kicks in at 173, etc.

7) I've added the other three tables at rows 46, 91 & 136, they are just duplicates of your one table. You'll need to edit them and correct the values.

NOTE: The other tables can have different numbers of ROWS than the original table, but the columns are expected to be identical as the final formulas below will always derive the answers from row 2, regardless of which data table is used.

============

8) Finally, back on the Sizer sheet, the two formulas doing the heavy lifting are:

D4: =IF(B4="","",INDEX('Gas Data'!$B$2:$P$2, MATCH(B4, OFFSET('Gas Data'!$B$3:$P$3, MATCH($C$2, 'Gas Data'!$A$4:$A$43, 1)+MATCH($A$1, 'Gas Data'!A:A, 0)-1,,,), 1)))

F4: =IF(B4="","",INDEX('Gas Data'!$B$2:$P$2,MATCH(E4,OFFSET('Gas Data'!$B$3:$P$3,MATCH($C$2,'Gas Data'!$A$4:$A$43,1),,,),1)))

[Meaglin]

Thanks this looks great. I knew i was out of my depth on this. I am trying to make a tool suite for my office to expedite our regular calculations.

[JBeaucaire]

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.