CODEWITHSUNDEEP
excelexcel-formula
Tchotchke
Tchotchke

Reputation: 399

INDEX-MATCH and SUMPRODUCT

I have a list of users and the security groups they're associated with, generated from Active Directory. It looks like this:

Username  Security Group
Chris     Group A
Chris     Group B
Chris     Group C
Dave      Group D
Dave      Group A
Dave      Group E

I have another list of usernames that I need to cross-reference to determine if a user is a part of one or more specific security groups. The list of security groups is dynamic and will change based on some other criteria so I'd like to just reference a named range in my check.

The equation I'm using right now looks like this: 

=IF(INDEX('Users in Security Groups'!C:C, MATCH('Cross-Reference'!A2, 'Users in Security Groups'!B:B, 0))=Rng_SecGrp, "YES", "NO")

Because INDEX-MATCH stops on the first value I am getting "NO" for username that I know actually belong to these security groups I'm concerned with. I imagine the answer lies with array formulas and/or SUMPRODUCT but both are a bit outside my comfort zone and I've failed to nail down the right formula to this point.

Any help is appreciated! Thanks!

Upvotes: 1

Views: 455

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use COUNTIFS() not INDEX(,MATCH()):

=IF(SUM(COUNTIFS('Users in Security Groups'!C:C,Rng_SecGrp,'Users in Security Groups'!B:B,'Cross-Reference'!A2)),"YES","NO")

This may need to be array entered with Ctrl-Shift-Enter.

Upvotes: 1

Related Questions