如何在LIST MEMBERS工作表中关联对应成员的“事件+日期”？

Absolutely! You can totally pull all matching "Event + Date" entries for each member into their corresponding row in your LIST MEMBERS sheet. Let me walk you through how to do this for both Google Sheets and Excel, since those are the most common tools people use for this kind of thing:

For Google Sheets #

Method 1: TEXTJOIN() + FILTER() (Recommended) #

This is the simplest and most efficient approach. Let's assume your setup looks like this:

• Events Sheet: Column A = Member Name, Column B = Event Name, Column C = Event Date
• LIST MEMBERS: Member names are in column A (starting at row 2, with row 1 as headers), and you want to show their events in column B

Paste this formula into cell B2 of LIST MEMBERS, then drag it down to apply to all members:

=TEXTJOIN(", ", TRUE, FILTER(Events!B:B&" ("&TEXT(Events!C:C, "yyyy-mm-dd")&")", Events!A:A=A2))

Breakdown of what this does:

• FILTER() grabs all rows from the Events Sheet where the member name matches the name in cell A2, then combines the event name and formatted date into a single string like "Team Meeting (2024-05-20)"
• TEXTJOIN() takes all those matching strings and joins them with a comma + space. The TRUE parameter ignores any empty results if a member has no events.
• Adjust the date format ("yyyy-mm-dd") to whatever you prefer—like "mm/dd/yyyy" or "dd-mmm-yy"—to match your needs.

Method 2: QUERY() (Alternative) #

If you prefer using QUERY for more advanced filtering, you can use this formula instead:

=TEXTJOIN(", ", TRUE, QUERY(Events!A:C, "SELECT CONCAT(B, ' (', TEXT(C, 'yyyy-mm-dd'), ')') WHERE A = '"&A2&"'", 0))

This works similarly, but uses QUERY to fetch and format the matching entries before joining them.

For Excel #

Method 1: TEXTJOIN() + IF() (Excel 2019/365) #

Using the same column setup as above, paste this formula into cell B2 of LIST MEMBERS:

=TEXTJOIN(", ", TRUE, IF(Events!$A:$A=$A2, Events!$B:$B&" ("&TEXT(Events!$C:$C, "yyyy-mm-dd")&)", ""))

• Note: For pre-365 Excel versions, this is an array formula—you'll need to press Ctrl+Shift+Enter after typing it instead of just Enter. Excel 365 handles it automatically.

Method 2: FILTER() + TEXTJOIN() (Excel 365 Only) #

If you're on Excel 365, you can use a cleaner version with FILTER:

=TEXTJOIN(", ", TRUE, FILTER(Events!$B:$B&" ("&TEXT(Events!$C:$C, "yyyy-mm-dd")&")", Events!$A:$A=$A2, ""))

This works exactly like the Google Sheets TEXTJOIN + FILTER formula.

Extra Tips #

• Make sure member names match exactly between the two sheets (no extra spaces, consistent capitalization)—otherwise the formula won't find matches.
• If you want entries to appear on separate lines instead of comma-separated, replace the ", " in TEXTJOIN with CHAR(10), then enable Wrap Text for the cells.
• To speed up formula performance, replace full-column ranges (like Events!A:A) with specific ranges (e.g., Events!A2:C1000) if you know your data won't go beyond a certain row.

内容的提问来源于stack exchange，提问作者Carlos