I am trying to do a lookup and it is only "looking at" the first valid entry

[llheath]

I have a USER table that has multiple users and multiple roles.
if a user has more than one role, it only finds the first one.
For instance

1. user1 Admin
2. user1 Corphr
3. user2 Corphr

IN(LOOKUP(USEREMAIL(), Users, Email, Role), LIST(“CorpHR”))

In the above senerio…user1 would not pass the formula selection, but user2 would.
even though they both are Corphr.

Should I be using a contains? or something else?

[Jonathon · Accepted solution]

This will give you a list of all roles for the logged in user:

SELECT(Users[Role],[Email]=USEREMAIL())

---

If you are wanting to check if the logged in user has the role “CorpHR”, then your formula could look like this:

IN(
   "CorpHR",
   SELECT(Users[Role],[Email]=USEREMAIL())
)

---

An improvement which would check against multiple roles would use an INTERSECT() function, which returns the list of values contained in both lists:

ISNOTBLANK(
   INTERSECT(
      {"CorpHR", "Admin"},
      SELECT(Users[Role],[Email]=USEREMAIL())
   )
)

This would return TRUE if the logged in user had one or both roles CorpHR / Admin, and FALSE otherwise.