Find two values in separate table

[Dharris144]

Hello everyone,
I have a spreadsheet "roles" Column A has user name Columns B:Z have their roles
Example
John Doe Display Edit Modify

I export another spreadsheet from our ERP a list of user and roles but it is only two columns so
John Doe Display
John Doe Edit
John Doe Delete

So how do I know if John Doe has a role that is not in the spreadsheet?
Example John Doe Delete is not in the "roles" spreadsheet.

maybe index match?

 

[DanteAmor]

Sheet "ERP"

	A	B	C
1	NAME	ROL	STATUS
2	John Doe	Display
3	John Doe	Edit
4	John Doe	Delete	Does not exist
5	Frank smith	Display
6	Frank smith	Edit
7	Frank smith	Modify	Does not exist

<tbody>
</tbody>

Formula

Cell Formula C2 =IF(IFERROR(MATCH(B2,INDIRECT("roles!"& MATCH(A2,roles!A:A,0) & ":" & MATCH(A2,roles!A:A,0)),0), 0)=0, "Does not exist", "") <tbody> </tbody>

<tbody>
</tbody>

Sheet "roles"

roles

	A	B	C	D	E	F	G	H	I
1	Name	Rol 1	Rol 2	Rol 3	Rol 4	Rol 5	Rol 6	Rol 7	Rol 8
2	John Doe	Display	Edit	Modify
3	Frank smith	Display	Edit	Delete

<tbody>
</tbody>

 

[Dharris144]

I don't care what anyone says. This deserves a hug!!!!!

Sheet "ERP"

	A	B	C
1	NAME	ROL	STATUS
2	John Doe	Display
3	John Doe	Edit
4	John Doe	Delete	Does not exist
5	Frank smith	Display
6	Frank smith	Edit
7	Frank smith	Modify	Does not exist

<tbody>
</tbody>

Formula

Cell Formula C2 =IF(IFERROR(MATCH(B2,INDIRECT("roles!"& MATCH(A2,roles!A:A,0) & ":" & MATCH(A2,roles!A:A,0)),0), 0)=0, "Does not exist", "") <tbody> </tbody>

<tbody>
</tbody>

Sheet "roles"

roles

	A	B	C	D	E	F	G	H	I
1	Name	Rol 1	Rol 2	Rol 3	Rol 4	Rol 5	Rol 6	Rol 7	Rol 8
2	John Doe	Display	Edit	Modify
3	Frank smith	Display	Edit	Delete

<tbody>
</tbody>

 

[DanteAmor]

I don't care either. I return a loving hug.