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

Question

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

ryan_mayu · Accepted Answer

mjoe01&nbsp;Please try thisColumn = 
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)

Forum Discussion

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

2 Replies

Related content

Sort by another column

Transform Multiple columns by multiplying by another column

Sequential Order column based on multiple criteria

Find Most Recent Date Relative to another Date Column in Power Query

Yet another Sort by another Column error.