Lookup multiple results with gaps

[Fin Fang Foom]

Hi ,


My data is set-up in format.

A B D E
Hours DeptName Criteria Results

B
8.34
7.64 A
7.21
6.64
3.21
6.62
8.34 B
7.21
3.21
9.21 C
4.15
8.34
10.11


Column A is the hours.
Column B are the Dept Name

Cell D2 Has the Dept.name
I would like a formula that returns multiple results for Dept B in cell
E2 and filler down.

I could use this formula:

=INDEX(A$3:A$80,SMALL(IF(B$3:B$80=D$2,ROW(A$3:A$15)-ROW(A$3)+1,""),ROWS(A$3:A3)))

But if you notice that in column B has gaps between them anyway we
could go around that?

 

[Biff]

Not sure what you're asking.

The only value that meets your condition is 8.34.

=INDEX(A$3:A$80,SMALL(IF(B$3:B$80=D$2,ROW(A$3:A$15)-ROW(A$3)+1,""),ROWS(A$3:A3)))

That formula will fail if you have any cells that meet the condition below
the 13th row of the indexed range.

The row array should match the size of the indexed array:

INDEX(A$3:A$80 = 1:78
ROW(A$3:A$15)-ROW(A$3)+1 = 1:13

Biff

 

[Fin Fang Foom]

Hi Biff,

That was my fault I missed type It should look like this:


=INDEX(A$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$80)-ROW(A$2)+1,""),ROWS(A$2:A2)))


Is there a way to get the expected results that I'm trying to achieve?

 

[Fin Fang Foom]

Yes because B coresponds that value but it should also include
7.21 and 3.21 because it resides in that Dept.Name. I tried use this
formula also but I cant get it to work properly.

=INDEX($A$2:$A$80,SMALL(IF(SUBTOTAL(9,OFFSET(B$2:B$80,,,ROW(B$2:B$80)-ROW(A$2)+1))=$D$2,ROW($A$2:$A$80)-ROW(A$2)+1),ROWS(A$2:A2)))