求助：根据序列匹配填充Google Sheets指定区域，不匹配则留空

Formula to Use #

In cell AD4 of the 'Today's Matchups!' sheet, enter this formula:

=IFERROR(LET(
  match_key, TEXTJOIN("|", TRUE, 'Today's Matchups!'!$AL$4:$AO$4),
  scenario_keys, BYROW('Battle Strategy Scenarios'!$A$2:$D, LAMBDA(r, TEXTJOIN("|", TRUE, r))),
  match_row, XMATCH(match_key, scenario_keys, 0),
  IF(match_row > 0, INDEX('Battle Strategy Scenarios'!$E$2:$L, match_row, 0):INDEX('Battle Strategy Scenarios'!$E$2:$L, match_row+4, 0), "")
), "")

How It Works #

• match_key: Creates a unique string by joining the 4 values in AL4:AO4 with a delimiter (|) to use as a lookup identifier.
• scenario_keys: Generates a list of unique keys for every scenario in the 'Battle Strategy Scenarios' sheet, by joining the first 4 columns of each scenario row.
• match_row: Uses XMATCH to find the exact row in the scenarios sheet that matches your target key.
• Return the Scenario Table: If a match is found, it pulls the corresponding 5-row × 8-column table (from columns E to L in the scenarios sheet) into AD4:AK8. If no match exists, the range stays blank.

Key Notes #

• Ensure the 4-value sequences in the 'Battle Strategy Scenarios' sheet are unique—duplicates will cause incorrect matches.
• Adjust the cell references ($A$2:$D, $E$2:$L) if your scenario data is located in different columns or starting rows.
• This formula auto-fills the entire AD4:AK8 range without needing to drag it down/across, as it returns a full array of values.

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