Match data through User ID and return 'TRUE' OR 'FALSE'-CodePudding

I have 2 spreadsheets, 1 is the 'master data' sheet and the second is the 'verification' sheet (to check.

I want to match the data from the 'verification' sheet with 'the master data' sheet through User ID to match DOB, Name, organization name, and user ID to return 'TRUE' or 'FALSE'.

The screenshot is attached for reference.

MasterSheet

Verification Sheet 2

Thank you in advance

CodePudding user response：

Use INDEX/MATCH functions combination.

=INDEX($A$2:$E$4,MATCH($B9,$A$2:$A$4,0),MATCH(G$8,$A$1:$E$1,0))=INDEX($B$9:$E$11,MATCH($B9,$B$9:$B$11,0),MATCH(G$8,$B$8:$E$8,0))

CodePudding user response：

You can try with this in F3. Change MasterSheet with the actual name of your sheet:

=MAKEARRAY(COUNTA(A3:A),3,LAMBDA(r,c,INDEX(B2:D,r,c)=INDEX(FILTER({MasterSheet!B2:B,MasterSheet!E2:E,MasterSheet!D2:D},MasterSheet!A2:A=INDEX(A3:A,r)),,c)))