I have complete function disfunction
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!
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!
Talk To Other Members
RE: I have complete function disfunction
Cheers
Greg Locock
RE: I have complete function disfunction
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!