Lookup multiple results with gaps
 

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.

Lookup multiple results with gaps
 

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,MATC H(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

"Fin Fang Foom" wrote in message
ups.com...
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.

Lookup multiple results with gaps
 

Biff wrote:
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,MATC H(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

"Fin Fang Foom" wrote in message
ups.com...
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.

Lookup multiple results with gaps
 

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

"Biff" wrote in message
...
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,MATC H(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

"Fin Fang Foom" wrote in message
ups.com...
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.

Lookup multiple results with gaps
 

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?


Biff wrote:
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,MATC H(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

"Fin Fang Foom" wrote in message
ups.com...
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.

Lookup multiple results with gaps
 

Sorry Biff your formula works great.

Thank You very much!!!




Fin Fang Foom wrote:
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?


Biff wrote:
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,MATC H(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

"Fin Fang Foom" wrote in message
ups.com...
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.

Lookup multiple results with gaps
 

You're welcome!

Biff

"Fin Fang Foom" wrote in message
oups.com...
Sorry Biff your formula works great.

Thank You very much!!!




Fin Fang Foom wrote:
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?


Biff wrote:
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,MATC H(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

"Fin Fang Foom" wrote in message
ups.com...
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.

Lookup multiple results with gaps
 

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,MAT CH(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

"Fin Fang Foom" wrote in message
ups.com...
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?


Biff wrote:
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,MATC H(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

"Fin Fang Foom" wrote in message
ups.com...
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.

Lookup multiple results with gaps
 

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.




Biff wrote:
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,MAT CH(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

"Fin Fang Foom" wrote in message
ups.com...
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?


Biff wrote:
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,MATC H(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

"Fin Fang Foom" wrote in message
ups.com...
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.