×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

# Contact US

Log In

#### Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
To Your Posts
• Keyword Search
• One-Click Access To Your
Favorite Forums
• Automated Signatures
On Your Posts
• 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.

Students Click Here

# I have complete function disfunction

 Forum Search FAQs Links MVPs

## I have complete function disfunction

(OP)
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!

### RE: I have complete function disfunction

Use INDEX and MATCH.

Cheers

Greg Locock

### RE: I have complete function disfunction

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!

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

### Reply To This Thread

#### Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

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!

Already a member? Close this window and log in.