I've really been struggling with this. Tried using XLOOKUP but I just can't get it as I'm not that advanced with Excel.
I exported a list of sites, permissions and user names from SharePoint online (over 1,000 rows). User name field is formatted as "user1; user2; user3". I have a separate list of users for which I am searching through the list. Basically, I need to know anywhere a user has permissions in SharePoint.
| | A | B | C | D |
| | Site | Users | Permissions | Lookup |
| 1 | SiteA/path1 | user1; user5 | write | =XLOOKUP... |
| 2 | SiteA/path2 | user3 | edit | =XLOOKUP... |
| 3 | SiteC | user2; user7; user8 | full control | =XLOOKUP... |
I am able to use XLOOKUP("*" & UserList & "*",B1,B1,2,1), which does a wildcard search for each user in the list of delimited users and seems to work okay. It's returning an array though, which isn't working for me, and I can't get it to return the user names it's found. I'd like it to return a listing of the users its found, separated by commas.
I've been trying all kinds of different things but I don't understand Excel well enough so I'm really just not getting anywhere.
