Getting more than one lookup results in array


G

Guest

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
 
Ad

Advertisements

G

Guest

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.
 
T

T. Valko

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

Guest

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.
 
G

Guest

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

T. Valko

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 Data>Validation
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!
 
Ad

Advertisements

T

T. Valko

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top