Returning all values based on 2 criteria...

K

Kevin

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
 
A

Alan

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

T. Valko

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
 
K

Kevin

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
 
M

Max

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