View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default Returning all values based on 2 criteria...

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Kevin" wrote in message
...
Thank you both for the fast responses. Biff, I used your sample and it's
exactly what I needed. Having that in front of me has helped me learn how
it's actually working. I appreciate your time and effort, -Kevin

"T. Valko" wrote:

One way....

emp = A2:A16 = employee names
task = B2:B16 = task
status = C2:C16 = either Q or NQ
E1 = drop down list of tasks

Enter this formula in F1. This will return a count of how many employees
are
qualified for the selected task:

=SUMPRODUCT(--(task=E1),--(status="Q"))

Enter this array formula** in G1:

=IF(ROWS(G$1:G1)<=F$1,INDEX(emp,SMALL(IF((task=E$1 )*(status="Q"),ROW(emp)-MIN(ROW(emp))+1),ROWS(G$1:G1))),"")

You need to copy down to a number of cells that is equal to the max
number
of employees that are qualified for any single task. For example, suppose
10
employees are qualified for task1, 5 employees are qualified for task 2
and
7 employees are qualified for task3. You need to copy the formula to at
least 10 cells.

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

Here's a small smaple file:

lookup100.xls 16kb

http://cjoint.com/?mFfH38SFCT


--
Biff
Microsoft Excel MVP


"Kevin" wrote in message
...
I have a list of employees and a list of tasks. I have a third column
that
tells me if an employee is qualified to perform a task. I would like to
make
a dropdown where I can select a task to be done and have it return a
list
of
any and all employees who are qualified to perform that task. I
understand
that vlookup will only return the first match it finds. How can return
more
than one answer to choose from? Thanks