Match and offset

  • Thread starter Thread starter aresar
  • Start date Start date
A

aresar

I am trying to get certain data out of a list that is and cannot be pu
in order. The numbers in Column A represent a Structure Number, an
Colum B is the depth of a pipe coming into and leaving that structure.
As you can see "3" is shown in three different places, but wit
different depths. If I want to find the depth of 3 in the se
3-8-9-10, how do I tell Excel to find that particular one. (The answe
it gives should be 5.25.)

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.2
 
How about INDEX?

=INDEX(B1:B20,SMALL(IF(A1:A20=G1,ROW(A1:A20)),H1))

where G1 holds the structure number and H1 specifies which
group you wish to pull from (H1 = 2 in this case).

This is an array formula so press ctrl/shift/enter for the
formula to take.

HTH
Jason
Atlanta, GA
 
What is my search for the value of "3" was on another sheet? Now m
structures are listed below. If the function was in Column C and i
required a return for the Structure listed in Column A. How would thi
apply?

Sheet 2

A B

2 1
3 2
4 3
5 4
6 5

8 3
9 8
10 9

5 3
7
 
Back
Top