需求：编写动态公式提取Yes/No表格中Yes单元格的行列表头至另一工作表

Hey there! Let's solve this problem where you need to automatically pull row and column headers every time a cell in your Sheet1 table is marked "Yes" (or "Y") into Sheet2. Here's a robust, dynamic solution that updates instantly when your source data changes:

Step-by-Step Solution #

First, let's define your source data structure for clarity (adjust ranges if your table is in different cells):

• Row headers (Apples, Orange, Pear): Sheet1!$A$2:$A$4
• Column headers (Molly, Joe, Sarah): Sheet1!$B$1:$D$1
• Yes/No values: Sheet1!$B$2:$D$4

The Dynamic Formula #

In Sheet2, select cell A1 and paste this formula (it will automatically spill into columns A and B with your matching pairs):

=LET(
    source_range, Sheet1!$B$2:$D$4,
    row_headers, Sheet1!$A$2:$A$4,
    col_headers, Sheet1!$B$1:$D$1,
    row_numbers, SEQUENCE(ROWS(source_range)),
    col_numbers, SEQUENCE(1, COLUMNS(source_range)),
    matching_rows, TOCOL(row_numbers*(source_range="Y")+(source_range="Yes")>0, 2),
    matching_cols, TOCOL(col_numbers*(source_range="Y")+(source_range="Yes")>0, 2),
    IFERROR(HSTACK(INDEX(row_headers, matching_rows), INDEX(col_headers, matching_cols)), "No Yes entries found")
)

How It Works #

Let's break down the formula so you understand each part:

• LET: Lets us name variables to make the formula readable and efficient.
• source_range: The range containing your Y/Yes values.
• row_headers/col_headers: The ranges with your row and column labels.
• row_numbers/col_numbers: Creates a sequence of numbers corresponding to the rows/columns in your source range.
• matching_rows/matching_cols: Uses TOCOL to filter only the row/column numbers where the cell value is "Y" or "Yes" (adjust this condition if you use other yes indicators like "X").
• HSTACK: Combines the matched row and column headers into two adjacent columns.
• IFERROR: Shows a friendly message if there are no "Yes" entries in the source table.

Compatibility Note #

This formula uses Excel 365/2021 dynamic array functions (LET, TOCOL, HSTACK, SEQUENCE). If you're using an older Excel version, we can adjust to a legacy array formula, but the dynamic version is far cleaner and auto-updates without manual refreshes.

Example Outcome #

If Sheet1 has:

Sheet2 will automatically show:

And if you change any Y/Yes to N in Sheet1, Sheet2 will update immediately!

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