Filter Function with Dynamically Changing Data Column Based on Value in Another Field

I have an Excel Worksheet to manage participants in a conference. It contains a data table (Table1) listing all the participants, some biographical data, and what events they plan to attend. I want to filter by nationality and event to show the data for people from a given nationality and attending any one event.

I have managed to use the FILTER formula to spill the data in the sheet "Access Lists" for people from a given nationality. However, I am having trouble adding an additional element - the event. I want to dynamically change a field value and have that field value change the data column called in the FILTER formula, for example Table1(D5)=D5. As I understand, I should use the INDIRECT function to turn the dynamic field into text that be used by the FILTER formula.

I have been playing with an INDIRECT function nested within a FILTER function to dynamically set the column that is returned. My formula below doesn't seem to work. Can someone show me where I went wrong? See sample file attached.

Please Login or Register  to view this content.

Welcome to the forum.

That formula and the worksheet Participants are not in the sample file.

Here you go (using your original attachment):

Please Login or Register  to view this content.

I hope your Mac version has HSTACK.

Make the Data Validation match the headers in the table for events (with or without the space, but mae them match... for consistency).

Then use (same result as in Post 4):

=LET(A,Table1,B,MATCH(H4,Data!1:1,0),IFERROR(CHOOSECOLS(FILTER(A,(INDEX(A,,2)=H3)*(INDEX(A,,B)=1)),1,2,3,B),"None"))

Anticipating the next question, here's a version that allows for all countries and any event. I've adapted the table headers and the validation list references.

Please Login or Register  to view this content.

Do you have any questions?

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.