Help with if text and auto identify team leaders

[kaymiah]

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!

[#2]

@kaymiah 

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

[#3]

Please find some example below.
Thank you

[#4]

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

[#5]

@Yea_So 

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,

[#6]

@kaymiah 

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

[#7]

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.

[#8 · Accepted solution]

@kaymiah 

Here you go:

File attached

[#9]

Much appreciated. This looks brilliant.
Thanks.
For my reference is there a Tutorial on the code used or a link.

[#10]

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

[#11]

@kaymiah 

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

=IF(
ISBLANK(C97),

//Do this//

"Blank",
//Otherwise do this//
IF(
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)
)
)

[#12]

copy that

[#13]

@kaymiah 

Here's a tutorial workbook attached

[#14]

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

[#15]

@kaymiah 

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

[#16]

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