Double index match in Alteryx

[Saraphas]

Hi everyone,

I am trying to replicate something in Alteryx that I have been able to do fairly straightforwardly in Excel, but can't seem to figure it out 

In my data, Every Project has an overall lead laid out in the column "overall lead", but in certain instances the overall lead also has the role type "data Lead", in those instances I want to create a new column that finds the row with that project's "Lead" role and add that to a new column. 

In Excel, I have created a new column identifying if the Overall Lead is also a Data Lead, and then created a column where I use an index match to find the name of the Lead using a concatenation of Project ID + the "Lead" role - since every project must have a "Lead", there are no errors. 

I can't seem to figure out how to do the same in Alteryx and would be grateful for any support  !

Many thanks, 

Project ID	Name	Role	Overall Lead	Data lead is overall lead?	New Overall Lead
1	Joe	Data Manager	David	No	Jenna
1	Sam	Data Scientist	David	No	Jenna
1	Jenna	Lead	David	No	Jenna
1	David	Data Lead	David	Yes	Jenna
2	Henry	Data Manager	Paula	No	Paula
2	James	Data Scientist	Paula	No	Paula
2	Paula	Lead	Paula	No	Paula
2	Dennis	Data Lead	Paula	No	Paula

[atcodedog05 · Accepted solution]

Hi @Saraphas 

Here is a workflow for the task.

Input:

Output:

Hope this is the expected output

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Saraphas]

Thank you so much! This works perfectly !

[atcodedog05]

Happy to help 🙂 @Saraphas 

Cheers and Happy Analyzing 😀