Help with if text and auto identify team leaders

Occasional Contributor
Hi I have a long list of names with many repeats due to the tasks they do. How can I auto populate the Column to show their team leaders?

The two Coloums. Are
Name and team leader.

I have a list of all the team leaders and who is in their team on a different sheet.

Thank you!
15 Replies


Can you please share some file with raw data? It will be easier to assist you.

Please find some example below.
Thank you
you provided us with nine names and two team leader names.
How are we to help you auto populate using a formula if the formula has nothing to point to? you need to share that data set so we can suggest what formulas to use to make your life a bit easier but without that data set your guess is as good as ours



Please find a data set. To confirm I am after a formula in D2 to auto populate based on the entry in WC column (C) based on the team leader for that person in WC column. This team info is in sheet two.

Hope this is clear. 

Many thanks,



The two most important columns in sheet1 are non existent.  Are the ID and Team Leaders column unavailable because those columns are the ones that will make your life easier.


which list are the team leader and how can I tell which one is the team leader for which group?


Please elaborate

To elaborate the column C and D are my focus. A and B are purely just information for me.
If you look in sheet 2 you will see the groups and the team leader in bold.
To clarify I’m looking to populate Column D based on the name in column C.

Thank you.
best response confirmed by allyreckerman (Microsoft)



Here you go:


File attached

Much appreciated. This looks brilliant.
For my reference is there a Tutorial on the code used or a link.
Additionally is the file attached above possible to delete as it contains some real data and would appreciate if the code was pasted for any reference.
Many thanks


How to use INDEX and MATCH | Exceljet


Exceljet has lots of cool and intuitive tutorials you can learn many things from that site.


btw this is the main formula:

INDEX(teams[#All],MATCH(C97,teams[[#All],[team member]],0),2)


The rest is just to support the main formula


//Do this//

//Otherwise do this//
ISNA(INDEX(teams[#All],MATCH(C97,teams[[#All],[team member]],0),2)),

//Then display//
"Not Found",

//Otherwise do the main formula//
INDEX(teams[#All],MATCH(C97,teams[[#All],[team member]],0),2)



Here's a tutorial workbook attached

Sorry If I was not clear.
Can you delete all the attachments please in the thread.
Thank you.



there is nothing to see in this thread except dummy information if you bother to open the attachments

Thank you for clarification and changing the original template.
Thank you again!