Lookup multiple results with gaps
 

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?

Lookup multiple results with gaps
 

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" wrote in message
oups.com...
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?

Lookup multiple results with gaps
 

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?


Biff wrote:
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" wrote in message
oups.com...
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?

Lookup multiple results with gaps
 

Biff wrote:
Not sure what you're asking.

The only value that meets your condition is 8.34.

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)))





=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" wrote in message
oups.com...
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?