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

'I understand that vlookup will only return the first match it finds'

Yes, it will only do that, try SUMPRODUCT to find two or more criteria,
For example, if in A1:A50 you had 'Cats' and 'Dogs' entered randomly in
those
cells, and in B1:B50, also randomly you had 'Male' and 'Female' then:-

=SUMPRODUCT(--(A1:A5="Cats"),--(B1:B50="Female"))

will return the nunber of Female Cats contained in the two lists.
You can also have 'Cats' in say E1 and 'Female' in E2, then he formula would
be

=SUMPRODUCT(--(A1:A5=E1),--(B1:B50=E2))

Probably best to play around with this until you've got it clear in your
head, then go onto the dropdown you want.
It's very do-able, but one thing at a time,
Regards,
Alan.
"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

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

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

Another play using non-array formulas

Illustrated in this sample:
http://www.freefilehosting.net/download/39kih
Returning values based on 2 criteria.xls

Assume source data in sheet: x,
cols A to C, data from row2 down, viz:

Emp Tasks EmpQualified?
Nam1 Task1 Y
Nam2 Task1 Y
Nam3 Task1 N
Nam1 Task2 Y
etc

In another sheet,
Assume B1 will contain a DV list to select the task
In A2:
=IF(AND(x!B2=$B$1,x!C2="Y"),ROW(),"")
Leave A1 empty. This is the criteria col.

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(x!A:A,SMALL(A:A ,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of source data in
sheet: x. Col B will return the required results for the selected value in B1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kevin" wrote:
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

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

Hi Max, thanks for giving me another option, much appreciated - Kevin

"Max" wrote:

Another play using non-array formulas

Illustrated in this sample:
http://www.freefilehosting.net/download/39kih
Returning values based on 2 criteria.xls

Assume source data in sheet: x,
cols A to C, data from row2 down, viz:

Emp Tasks EmpQualified?
Nam1 Task1 Y
Nam2 Task1 Y
Nam3 Task1 N
Nam1 Task2 Y
etc

In another sheet,
Assume B1 will contain a DV list to select the task
In A2:
=IF(AND(x!B2=$B$1,x!C2="Y"),ROW(),"")
Leave A1 empty. This is the criteria col.

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(x!A:A,SMALL(A:A ,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of source data in
sheet: x. Col B will return the required results for the selected value in B1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kevin" wrote:
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

welcome, Kevin
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kevin" wrote in message
...
Hi Max, thanks for giving me another option,
much appreciated - Kevin