Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations IDS on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

I have complete function disfunction

Status
Not open for further replies.

aresar

Civil/Environmental
Mar 3, 2004
2
I am trying to get certain data out of a list that cannot be put in order. The numbers in Column A represent a Structure Number, and Colum B is the depth of a pipe coming into and leaving that structure. This is my list on sheet 1 and below on sheet 2 is where it applies.

Sheet 1
A B

1 6.89
2 6.25
3 5.89
4 4.26
5 3.65
6 2.02

3 5.25
8 4.90
9 2.26
10 1.87

3 4.68
5 3.74
7 0.25

Now my structures are listed in a different way on sheet 2 below. If the function in Column C required a return for the Structure listed in Column A & the function in Column D required a return for the Structure listed in Column B. The answer for each structure is dependant on the structures listed around it.

How would this work?

Sheet 2
A B C D

2 1 6.25 6.89
3 2 5.89 6.25
4 3 4.26 5.89
5 4 3.65 4.26
6 5 2.02 3.65

8 3 4.90 5.25
9 8 2.26 4.90
10 9 1.87 2.26

5 3 3.74 4.68
7 5 0.25 3.74

I know this is confusing, but I am completely stumped....Please help!
 
Replies continue below

Recommended for you

If it was me (and I'm a drainage engineer, too!), I would find a way to make each pipe invert unique. Having Excel compute if the value to return for '3' is 5.89 or 5.25 or 4.68 (from your data above) is going to be real tough.

How would you fill in your table by hand? Chances are, you are already thinking of these data in groups.

Typically, drainage pipe calculations or data are grouped together by pipe run or by plan sheet. Why not have a column with unique identifiers, such as B-3 for structure 3 on pipe run B? Or use 2-3 for structure 3 on sheet 2. Or use E/W/N/S for your location it that works. In any case, if you can create unique & meaningful identifies your lookup can return the unique value you want, instead of trying to compute which "grouping" the value is in. It will be easier this way than trying to code a function.

That said, once you have unique identifiers, you can use INDEX & MATCH as Greg said to retrieve your value, even if the list is not sorted. My experience is that you will likely want the third argument of the MATCH command to be zero to exactly match your looked up value.

Good luck!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor