Something more than VLOOKUP?

[Gary]

Hi All,

This is what i am trying to do.

Column A - Agents' names
Column B - Team Leaders' names
Column C onwards - Other data.

Now in another what I want is that if I enter the Team leader's name, all
the data for his team is returned. For example I enter John in column A, i
want sales in column B, Average in Column C, Agent's name in column D etc.

The problem is that in the first sheet, the team leader's name may appear
several times.

makes sense?

Thanks in advance for any help.

 

[Guest]

Gary,
You may try Index and Match functions. Better would be to have a VBA
programme written.

Easiest option, in my opinion, would be to use Data>Autofilter and select
John in the team leader's name and copy them to sheet 2 ( where ever you want
the filtered data) and use average and other functions as per your
requirement.

From your question, the average of what you want is not clear. It is also
not clear whether you want the sum of sales data for each team leader or for
each agent.

If "team leader's name may appear several times" means you want the sum of
sales etc., data in respect of each team leader, then try sumif or sumproduct
functions.
Best Wishes