How to: Return list of values from one column based on two criteria

Hi there,

I'm making a sheet to analyze livestream participation and want to make the document automatically list the students who are absent for a given event (chosen by drop-down menu).

In Sheet A (see image), I have entered "Real name" of two participants. And underneath three livestream events marked them as Absent.

In Sheet B (see image), I want the names of any absent participant for a given livestream event to appear (can be selected using the dropdown in the top left corner).

So, using the examples in the images, if I choose Livestream event "Kick-Off", "Alibaba Story" or "PEST", I want both Andreas and Rasmus to appear as a list. And if I delete "Absent" in either of the events, the name should not appear on the list in Sheet B.

And, if, lets say, the list got 100 names on it, I want the names of those who are absent for any of those events to appear when I choose either event.

I'd then make a formula to say if "x real name" then (DingTalk Name, Group Name, Tutor) to fill in the rest based on the name that appears in the list.

I have tried with Index + Match and Lookup, but I'm not really able to figure out the logic that says: "When X event is chosen -> lookup "Absent" in Y Column -> return name in 3rd column."

Hope someone got some good ideas. I'm new to this forum, so not sure if I can upload the file if that is of any help.

Looking forward to hearing from you - thank you very much in advance!

Sincerely,

Andreas

Big yellow banner - how to upload sample file.
HelpUs to HelpYou