Find two values in separate table

D

Dharris144

Board Regular
Joined
Jul 22, 2009
Messages
97
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?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Sheet "ERP"

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]ROL[/TD]
[TD="align: center"]STATUS[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD]John Doe[/TD]
[TD]Display[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD]John Doe[/TD]
[TD]Edit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD]John Doe[/TD]
[TD]Delete[/TD]
[TD]Does not exist[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD]Frank smith[/TD]
[TD]Display[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD]Frank smith[/TD]
[TD]Edit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD]Frank smith[/TD]
[TD]Modify[/TD]
[TD]Does not exist[/TD]
[/TR]
</tbody>[/TABLE]

Formula
[TABLE="class: grid, width: 900"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] "]
[TD]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>
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet "roles"

roles

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD]Name[/TD]
[TD]Rol 1[/TD]
[TD]Rol 2[/TD]
[TD]Rol 3[/TD]
[TD]Rol 4[/TD]
[TD]Rol 5[/TD]
[TD]Rol 6[/TD]
[TD]Rol 7[/TD]
[TD]Rol 8[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD]John Doe[/TD]
[TD]Display[/TD]
[TD]Edit[/TD]
[TD]Modify[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD]Frank smith[/TD]
[TD]Display[/TD]
[TD]Edit[/TD]
[TD]Delete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I don't care what anyone says. This deserves a hug!!!!!
DanteAmor said:
Sheet "ERP"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]ROL[/TD]
[TD="align: center"]STATUS[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]John Doe[/TD]
[TD]Display[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]John Doe[/TD]
[TD]Edit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]John Doe[/TD]
[TD]Delete[/TD]
[TD]Does not exist[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Frank smith[/TD]
[TD]Display[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Frank smith[/TD]
[TD]Edit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Frank smith[/TD]
[TD]Modify[/TD]
[TD]Does not exist[/TD]
[/TR]
</tbody>[/TABLE]

Formula
[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]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>
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet "roles"

roles

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Name[/TD]
[TD]Rol 1[/TD]
[TD]Rol 2[/TD]
[TD]Rol 3[/TD]
[TD]Rol 4[/TD]
[TD]Rol 5[/TD]
[TD]Rol 6[/TD]
[TD]Rol 7[/TD]
[TD]Rol 8[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]John Doe[/TD]
[TD]Display[/TD]
[TD]Edit[/TD]
[TD]Modify[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Frank smith[/TD]
[TD]Display[/TD]
[TD]Edit[/TD]
[TD]Delete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
294
Mouse7681
M
B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
508
bh24524
B
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
366
Alex Blakenburg
A
W
  • Question Question
Autofill/size adjacent column next to a table
Replies
1
Views
176
plettieri
P
R
  • Question Question
Match Values for repeating Employee IDs
Replies
1
Views
599
AhoyNC
A

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top