Using a table to search multiple columns in another table and return a value from the first table

[mjoe01]

I need to be able to understand which tasks managers need to be aware belong to their team. I would like to use table B which has a list of managers and their team members to identify which tasks in table A the manager has a team member with an action for that task and then return their name in a new column in table A. Teammembers may change over time, which is why I wanted to have table B since it will be easy to update the names in the table. Tasks will usually have just one manager working on it, if there is overlap, as long as one manager is aware of the task it's fine. 

Table A							Table B			New Column in Table A
Task ID	action 1	action 2	action 3			manager	team member		Task ID	Manager Owner
1	adam	bob	bob			David	adam		1	David
2	bob	bob	adam			David	bianca		2	David
3	bob	bianca	bob			David	charlie		3	David
4	charlie	bob	bob			Lauren	dan		4	David
5	bob	bob	dan			Tim	elena		5	Lauren
6	bob	elena	bob			Tim	sarah		6	Tim
7	sarah	sarah	bob						7	Tim

[amitchandak]

@mjoe01 , Try a new column like

concatenatex(filter(TableB, TableA[action 1] = TableB[team member] || TableA[action 2] = TableB[team member] || TableA[action 3] = TableB[team member]),TableB[manager])

or

concatenatex(summarize(filter(TableB, TableA[action 1] = TableB[team member] || TableA[action 2] = TableB[team member] || TableA[action 3] = TableB[team member]),TableB[manager]),[manager])

[ryan_mayu · Accepted solution]

@mjoe01 

Please try this

Column = 
VAR A1= LOOKUPVALUE(TableB[manager],TableB[team member],TableA[action 1])
VAR A2=LOOKUPVALUE(TableB[manager],TableB[team member],TableA[action 2])
var A3=LOOKUPVALUE(TableB[manager],TableB[team member],TableA[action 3])
RETURN if(ISBLANK(A1),if(ISBLANK(A2),A3,A2),A1)