How to index match based on three criteria

Question

I have 3 columns: A) Department, B) Manager, C) Agent Name

I am trying to pull a list of agent names based on their department and manager. However I am only able to pull agent names based on their manager using the formula below:

=INDEX($C$1:$C$16,MATCH(0,IF($F$1=$B$1:$B$16,COUNTIF($F$2:$F2,$C$1:$C$16),""),0))

F1 has the manager's name

Can anyone tell me what to add in to pull the agent's name based on manager AND department?

What is the third criteria mentioned in the title of your question? — cybernetic.nomad, Dec 24 '18 at 15:57
One way is to concatenate `department` and `manager` into a single column and then do a `vlookup` with `concat(dept, manager)` as the look up value. — Alex P, Dec 24 '18 at 15:57

score 0 · Answer 1 · answered Dec 24 '18 at 15:57

0

F1 has the manager's name

G1 has the department.

=index($C$1:$C$16, aggregate(15, 7, row($1:$16)/(($A$1:$A$16=$G$1)*($B$1:$B$16=$F$1)), row(1:1)))

Fill down for additional agents. If you want to find the additional agents by filling right then change row(1:1) to column(A:A).

answered Dec 24 '18 at 15:57

Perfect! Thank you very much! – bprin87 Dec 25 '18 at 16:32
do you have any idea how I can use the same formula (or a slightly different one) if for example I want to return a specific manager based on the agent name, but the same agent name appears multiple times with various managers? So in theory ignore any duplicate agent names unless it has a specific manager next to it. – bprin87 Jan 10 '19 at 14:23

How to index match based on three criteria

1 Answers1