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

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





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

In C1 enter:

In C2 enter:
And copy down the column

In D1 enter:
And copy down the column

In F1 enter:
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. Valko

Here's another one.

Assume your data is in the range A2:B5.

Try this array formula** :


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:


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


Here's a small sample file that demonstrates this:

** array formulas need to be entered using the key combination of


For Gary''s Student

THANKS!!!!!!!!!!! It's working perfectly and is exactly what I was looking
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.


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

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

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.




T. Valko

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:


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