0

I'm currently trying to filter some data. I have two sheets, one with the users name, e-mail and their function within the company and another with the same e-mail and then which mailboxes they have access to in another column. I want to take a function, see which users are associated with it and then use those e-mails to again check which mailboxes are connected. This way I can see which function has which users and which rights to mailboxes. With =FILTER I already have a sheet where I can choose a function in a drop-down menu and then get the relevant users, but now as I already said I want to take those newly filtered users e-mails and again put them against the mailboxes.

=FILTER(ADExportUsersNL!A2:J445;ADExportUsersNL!J2:J445=A3;"NoData") <-- this is the first function that already works.
=FILTER(MailboxRechten!B2:L3452;MailboxRechten!G2:G3452=B6;"NoData") <-- This is the one I want to work.

The problem is that with the INCLUDE part of the formula (so =B6), I can only verify against one thing at a time, one cell. If I specify one cell which happens to have the previously filtered data in it, then it works. I want to be able to search in the same way, but then in a range. So for example: =FILTER(MailboxRechten!B2:L3452;MailboxRechten!G2:G3452=B5:B100;"NoData") but this doesn't work and gives an error. I've tried using XLOOKUP, but I don't think I understand it correctly. I can't upload some sample data for now since it would take a lot of time to anonymize it, so I hope my explanation is clear enough. Maybe a tip for which function to use?

Many thanks for the help.

Improve this question
1
  • FILTER and XLOOKUP are similar in approach, but are used for a different objective. You usually use XLOOKUP to find one (the first) matching instance, whereas you use FILTER when you want to return all the matching instances. So which you use is determined solely by what you are trying to accomplish.
    – Max R
    Commented Oct 21, 2022 at 2:03

1 Answer 1

Reset to default
0

Someone else figured it out for me, I'll mark this as the answer and if something comes up I'll change it later.

=FILTER(MailboxRechten!B2:L3452;isnumber(match(MailboxRechten!G2:G3452;B5:B100;0));"NoData")
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .