Getting more than one lookup results in array
 

I've tried several functions alone and combined with the next problem, but
nothing work correctly.
Suppose I have two columns A i B - first with the personal names and second
with some score written in text: excellent, very good, good. I want to find
oll "excellent" in B column and write down all personal names in
corresponding A column cells.
For example:
A B
Tom excellent
Beky good
Mike excellent
Jerry very good

I want too lookup for ALL excellent in B column and write down (list) in
another column (for instnance column F) all personal names with score
"excellent" - in this particular example:
F
Tom
Mike

It's very important to me and I'm searching through Excell Help for days and
can't find the solution....

Thanks

Getting more than one lookup results in array
 

The best solution is to use AutoFilter, but it is not dynamic. A formula
approach is:

In C1 enter:
=IF(B1="excellent",1,0)

In C2 enter:
=IF(B2="excellent",1+MAX($C$1:C1),"")
And copy down the column

In D1 enter:
=IF(ISERROR(SMALL(C:C,ROW())),"",SMALL(C:C,ROW()))
And copy down the column

In F1 enter:
=IF(D1="","",INDEX(A:A,MATCH(D1,C:C,0),1))
And copy down the column

To see:

Tom excellent 1 1 Tom
Beky good 2 Mike
Mike excellent 2
Jerry verygood


Column C assigns a unique, non-zero, id to each "excellent". Column D
gathers them. Column F gathers the names.
--
Gary''s Student - gsnu200734


"Alex" wrote:

I've tried several functions alone and combined with the next problem, but
nothing work correctly.
Suppose I have two columns A i B - first with the personal names and second
with some score written in text: excellent, very good, good. I want to find
oll "excellent" in B column and write down all personal names in
corresponding A column cells.
For example:
A B
Tom excellent
Beky good
Mike excellent
Jerry very good

I want too lookup for ALL excellent in B column and write down (list) in
another column (for instnance column F) all personal names with score
"excellent" - in this particular example:
F
Tom
Mike

It's very important to me and I'm searching through Excell Help for days and
can't find the solution....

Thanks

Getting more than one lookup results in array
 

Here's another one.

Assume your data is in the range A2:B5.

Try this array formula** :

=IF(ROWS($1:1)<=COUNTIF(B$2:B$5,"excellent"),INDEX (A$2:A$5,SMALL(IF(B$2:B$5="excellent",ROW(B$2:B$5)-MIN(ROW(B$2:B$5))+1),ROWS($1:1))),"")

Copy down until you get blanks.

You can make this more dynamic (like having a dynamic filter) by doing this:

Create a drop down list in a cell of the different scores. Say this drop
down list in cell D2. In cell E2 enter a formula that counts how many of the
selected scores are present:

=COUNTIF(B2:B5,D2)

Then you can refer to those cells in the formula instead of having those
processes written directly into the formula. This makes the formula a little
bit shorter, more efficient and more dynamic (still an array formula**):

=IF(ROWS($1:1)<=E$2,INDEX(A$2:A$5,SMALL(IF(B$2:B$5 =D$2,ROW(B$2:B$5)-MIN(ROW(B$2:B$5))+1),ROWS($1:1))),"")

Here's a small sample file that demonstrates this:

http://cjoint.com/?hrx56jnYdZ

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Alex" wrote in message
...
I've tried several functions alone and combined with the next problem, but
nothing work correctly.
Suppose I have two columns A i B - first with the personal names and
second
with some score written in text: excellent, very good, good. I want to
find
oll "excellent" in B column and write down all personal names in
corresponding A column cells.
For example:
A B
Tom excellent
Beky good
Mike excellent
Jerry very good

I want too lookup for ALL excellent in B column and write down (list) in
another column (for instnance column F) all personal names with score
"excellent" - in this particular example:
F
Tom
Mike

It's very important to me and I'm searching through Excell Help for days
and
can't find the solution....

Thanks

Getting more than one lookup results in array
 

For Gary''s Student

THANKS!!!!!!!!!!! It's working perfectly and is exactly what I was looking
for!
I'm a math professor and I'm working on school statistics and have found
Excell much more appropriate for other colleagues than sql, in which I'm much
more experienced.


"Gary''s Student" wrote:

The best solution is to use AutoFilter, but it is not dynamic. A formula
approach is:

In C1 enter:
=IF(B1="excellent",1,0)

In C2 enter:
=IF(B2="excellent",1+MAX($C$1:C1),"")
And copy down the column

In D1 enter:
=IF(ISERROR(SMALL(C:C,ROW())),"",SMALL(C:C,ROW()))
And copy down the column

In F1 enter:
=IF(D1="","",INDEX(A:A,MATCH(D1,C:C,0),1))
And copy down the column

To see:

Tom excellent 1 1 Tom
Beky good 2 Mike
Mike excellent 2
Jerry verygood


Column C assigns a unique, non-zero, id to each "excellent". Column D
gathers them. Column F gathers the names.
--
Gary''s Student - gsnu200734


"Alex" wrote:

I've tried several functions alone and combined with the next problem, but
nothing work correctly.
Suppose I have two columns A i B - first with the personal names and second
with some score written in text: excellent, very good, good. I want to find
oll "excellent" in B column and write down all personal names in
corresponding A column cells.
For example:
A B
Tom excellent
Beky good
Mike excellent
Jerry very good

I want too lookup for ALL excellent in B column and write down (list) in
another column (for instnance column F) all personal names with score
"excellent" - in this particular example:
F
Tom
Mike

It's very important to me and I'm searching through Excell Help for days and
can't find the solution....

Thanks

Getting more than one lookup results in array
 

For T.Valko

I've just seen your solution of the problem and I'm going to try it as soon
as possible. The biggest problem is that I have experience with sql and php
but I'm
rather new in Excel and, to be honest, don't know how to create a drop down
list in Excel. I know that something like drop down list is automatically
created when filtering data...

"T. Valko" wrote:

Here's another one.

Assume your data is in the range A2:B5.

Try this array formula** :

=IF(ROWS($1:1)<=COUNTIF(B$2:B$5,"excellent"),INDEX (A$2:A$5,SMALL(IF(B$2:B$5="excellent",ROW(B$2:B$5)-MIN(ROW(B$2:B$5))+1),ROWS($1:1))),"")

Copy down until you get blanks.

You can make this more dynamic (like having a dynamic filter) by doing this:

Create a drop down list in a cell of the different scores. Say this drop
down list in cell D2. In cell E2 enter a formula that counts how many of the
selected scores are present:

=COUNTIF(B2:B5,D2)

Then you can refer to those cells in the formula instead of having those
processes written directly into the formula. This makes the formula a little
bit shorter, more efficient and more dynamic (still an array formula**):

=IF(ROWS($1:1)<=E$2,INDEX(A$2:A$5,SMALL(IF(B$2:B$5 =D$2,ROW(B$2:B$5)-MIN(ROW(B$2:B$5))+1),ROWS($1:1))),"")

Here's a small sample file that demonstrates this:

http://cjoint.com/?hrx56jnYdZ

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Alex" wrote in message
...
I've tried several functions alone and combined with the next problem, but
nothing work correctly.
Suppose I have two columns A i B - first with the personal names and
second
with some score written in text: excellent, very good, good. I want to
find
oll "excellent" in B column and write down all personal names in
corresponding A column cells.
For example:
A B
Tom excellent
Beky good
Mike excellent
Jerry very good

I want too lookup for ALL excellent in B column and write down (list) in
another column (for instnance column F) all personal names with score
"excellent" - in this particular example:
F
Tom
Mike

It's very important to me and I'm searching through Excell Help for days
and
can't find the solution....

Thanks

Getting more than one lookup results in array
 

how to create a drop down list in Excel.

It's really easy.

Select the cell where you want the drop down.
Goto the menu DataValidation
Allow: List
Source:

Depending on how many items you will need in the list, there are 2 ways to
populate the list. Since you had only 3 items you can type them directly in
Source box separated by a comma: Excellent,Very Good,Good. If you have many
items then you would just list them in a range of cells and refer to that
range as the source of the list: A1:A3

Make sure In cell drop down is selected.
OK

Done!


--
Biff
Microsoft Excel MVP


"Alex" wrote in message
...
For T.Valko

I've just seen your solution of the problem and I'm going to try it as
soon
as possible. The biggest problem is that I have experience with sql and
php
but I'm
rather new in Excel and, to be honest, don't know how to create a drop
down
list in Excel. I know that something like drop down list is automatically
created when filtering data...

"T. Valko" wrote:

Here's another one.

Assume your data is in the range A2:B5.

Try this array formula** :

=IF(ROWS($1:1)<=COUNTIF(B$2:B$5,"excellent"),INDEX (A$2:A$5,SMALL(IF(B$2:B$5="excellent",ROW(B$2:B$5)-MIN(ROW(B$2:B$5))+1),ROWS($1:1))),"")

Copy down until you get blanks.

You can make this more dynamic (like having a dynamic filter) by doing
this:

Create a drop down list in a cell of the different scores. Say this drop
down list in cell D2. In cell E2 enter a formula that counts how many of
the
selected scores are present:

=COUNTIF(B2:B5,D2)

Then you can refer to those cells in the formula instead of having those
processes written directly into the formula. This makes the formula a
little
bit shorter, more efficient and more dynamic (still an array formula**):

=IF(ROWS($1:1)<=E$2,INDEX(A$2:A$5,SMALL(IF(B$2:B$5 =D$2,ROW(B$2:B$5)-MIN(ROW(B$2:B$5))+1),ROWS($1:1))),"")

Here's a small sample file that demonstrates this:

http://cjoint.com/?hrx56jnYdZ

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Alex" wrote in message
...
I've tried several functions alone and combined with the next problem,
but
nothing work correctly.
Suppose I have two columns A i B - first with the personal names and
second
with some score written in text: excellent, very good, good. I want to
find
oll "excellent" in B column and write down all personal names in
corresponding A column cells.
For example:
A B
Tom excellent
Beky good
Mike excellent
Jerry very good

I want too lookup for ALL excellent in B column and write down (list)
in
another column (for instnance column F) all personal names with score
"excellent" - in this particular example:
F
Tom
Mike

It's very important to me and I'm searching through Excell Help for
days
and
can't find the solution....

Thanks

Getting more than one lookup results in array
 

Clarification:

If you have many items then you would just list them in a range of cells
and refer to that range as the source of the list: A1:A3

If your list source is a range of cells refer to it like this:

=A1:A3


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
how to create a drop down list in Excel.

It's really easy.

Select the cell where you want the drop down.
Goto the menu DataValidation
Allow: List
Source:

Depending on how many items you will need in the list, there are 2 ways to
populate the list. Since you had only 3 items you can type them directly
in Source box separated by a comma: Excellent,Very Good,Good. If you have
many items then you would just list them in a range of cells and refer to
that range as the source of the list: A1:A3

Make sure In cell drop down is selected.
OK

Done!


--
Biff
Microsoft Excel MVP


"Alex" wrote in message
...
For T.Valko

I've just seen your solution of the problem and I'm going to try it as
soon
as possible. The biggest problem is that I have experience with sql and
php
but I'm
rather new in Excel and, to be honest, don't know how to create a drop
down
list in Excel. I know that something like drop down list is automatically
created when filtering data...

"T. Valko" wrote:

Here's another one.

Assume your data is in the range A2:B5.

Try this array formula** :

=IF(ROWS($1:1)<=COUNTIF(B$2:B$5,"excellent"),INDEX (A$2:A$5,SMALL(IF(B$2:B$5="excellent",ROW(B$2:B$5)-MIN(ROW(B$2:B$5))+1),ROWS($1:1))),"")

Copy down until you get blanks.

You can make this more dynamic (like having a dynamic filter) by doing
this:

Create a drop down list in a cell of the different scores. Say this drop
down list in cell D2. In cell E2 enter a formula that counts how many of
the
selected scores are present:

=COUNTIF(B2:B5,D2)

Then you can refer to those cells in the formula instead of having those
processes written directly into the formula. This makes the formula a
little
bit shorter, more efficient and more dynamic (still an array formula**):

=IF(ROWS($1:1)<=E$2,INDEX(A$2:A$5,SMALL(IF(B$2:B$5 =D$2,ROW(B$2:B$5)-MIN(ROW(B$2:B$5))+1),ROWS($1:1))),"")

Here's a small sample file that demonstrates this:

http://cjoint.com/?hrx56jnYdZ

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Alex" wrote in message
...
I've tried several functions alone and combined with the next problem,
but
nothing work correctly.
Suppose I have two columns A i B - first with the personal names and
second
with some score written in text: excellent, very good, good. I want to
find
oll "excellent" in B column and write down all personal names in
corresponding A column cells.
For example:
A B
Tom excellent
Beky good
Mike excellent
Jerry very good

I want too lookup for ALL excellent in B column and write down (list)
in
another column (for instnance column F) all personal names with score
"excellent" - in this particular example:
F
Tom
Mike

It's very important to me and I'm searching through Excell Help for
days
and
can't find the solution....

Thanks