Lookup multiple results with gaps

[Fin Fang Foom]

Hi,
My Data is Set-Up in this format.
A B D E
Hours Dept.Names Criteria Expected Results
8.34 A B 8.34
8.34 7.21
7.64 3.21
7.21
7.21
3.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 are the hours.
Column B are the Dept. names
Cell D2 has the criteria
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$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15)-ROW(A$2)+1,""),ROWS(A$2­:A2)))

But if you notice that in column B has gaps between them anyway we
could go around that without any helper columns or helper cells? A all
in one formula.

 

[Biff]

OK, I see what you want.

Try this: (based on your sample data in the range A3:B16):

=IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B$3:B$16,0)+ROWS($1:1)-1),"")

Biff

Hi,
My Data is Set-Up in this format.
A B D E
Hours Dept.Names Criteria Expected Results
8.34 A B 8.34
8.34 7.21
7.64 3.21
7.21
7.21
3.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 are the hours.
Column B are the Dept. names
Cell D2 has the criteria
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$2:A$80,SMALL(IF(B$2:B$80=D$2,ROW(A$2:A$15)-ROW(A$2)+1,""),ROWS(A$2­:A2)))

But if you notice that in column B has gaps between them anyway we
could go around that without any helper columns or helper cells? A all
in one formula.

 

[Fin Fang Foom]

OK, I see what you want.

Try this: (based on your sample data in the range A3:B16):

=IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B$3:B$16,0)+ROWS($1:1)-1),"")

I'm getting a #N/A error. I'm putting the exact formula you gave
me.

 

[Biff]

I forgot something!

You need a flag to define the end of the range in column B. It'd be easier
to show you in a sample file rather than try to explain it. So, here's a
sample file:

Sample_extract2.xls 14.0kb

http://cjoint.com/?lmbGTt680S

Biff

 

[Fin Fang Foom]

Ok I got it to work. Thank you very much!

The first try Iit did not work because I use it on another example
worksheet I have that instead of texts in column B they were numbers
but your formula you provided works well if column B are texts. Can it
work both ways for vaules and texts?

 

[Fin Fang Foom]

Sorry Biff your formula works great.

Thank You very much!!!

 

[Biff]

You're welcome!

Biff

Sorry Biff your formula works great.

Thank You very much!!!

 

[Biff]

Can it work both ways for vaules and texts?

Your posted example was in TEXT so I wrote the formula specifically for
TEXT.

To make it work for either text or numbers:

=IF(ROWS($1:1)<=MATCH("*",B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16,0)+1),0),INDEX(A$3:A$16,MATCH(D$2,B$3:B$16,0)+ROWS($1:1)-1),"")

Change this portion:

MATCH("*",B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16,0)+1),0)

To:

MATCH(TRUE,B$16:INDEX(B$3:B$16,MATCH(D$2,B$3:B$16,0)+1)<>"",0)

This will make the formula an array. So, enter using the key combination of
CTRL,SHIFT,ENTER.

You still need an end of range flag in column B. Using this modified
formula, that flag can be either text or numeric.

Biff

Ok I got it to work. Thank you very much!

The first try Iit did not work because I use it on another example
worksheet I have that instead of texts in column B they were numbers
but your formula you provided works well if column B are texts. Can it
work both ways for vaules and texts?

 

[Fin Fang Foom]

Hi Biff,

Thank You for the reply. Is there a way to get around the range flag.
Because my data fluctuates daily. My data could end at row 50 or row
300 even at row 4000. Can we get around this without using VBA? If not
possible thats ok.