三维引用场景下，如何调整Google Sheets公式匹配偏移行TIMING LOCK

需求背景 #

基于Google Sheets数据集制作简化表格，需在简化表格D列返回与匹配集数对应的“Monday”日期，且关联数据集中偏移行的“TIMING LOCK”单元格对应的集数。现有公式仅能在“TIMING LOCK”与数据集D列集数同行时返回正确日期数组：

=INDEX(IFNA(VLOOKUP($B2:$B, SPLIT(TOCOL(IF(REGEXMATCH(InnerTable, "(?i)\b"&"Timing"&"\b"), EpNumCol&"×"&MondayRow, ), 1), "×"), 2, 0)))

（注：InnerTable、EpNumCol、MondayRow为数据集的命名区域）

调整后的公式及说明 #

针对“TIMING LOCK”与集数行存在固定偏移的场景，使用OFFSET函数关联对应集数行的数据，示例公式如下：

=INDEX(IFNA(VLOOKUP($B2:$B, SPLIT(TOCOL(IF(REGEXMATCH(InnerTable, "(?i)\bTiming\b"), OFFSET(EpNumCol, -k, 0)&"×"&OFFSET(MondayRow, -k, 0), ), 1), "×"), 2, 0)))

• 核心修改：将原公式中的EpNumCol和MondayRow替换为OFFSET(EpNumCol, -k, 0)、OFFSET(MondayRow, -k, 0)
• 参数说明：k是**“TIMING LOCK”行相对于对应集数行的偏移量**：
  • 若“TIMING LOCK”在集数行下方1行，k=1，用-1表示向上偏移1行取对应集数和日期
  • 若“TIMING LOCK”在集数行上方2行，k=-2，用2表示向下偏移2行取对应集数和日期

示例场景 #

假设“TIMING LOCK”总是出现在对应集数行的下方第2行，那么k=2，代入后的公式为：

=INDEX(IFNA(VLOOKUP($B2:$B, SPLIT(TOCOL(IF(REGEXMATCH(InnerTable, "(?i)\bTiming\b"), OFFSET(EpNumCol, -2, 0)&"×"&OFFSET(MondayRow, -2, 0), ), 1), "×"), 2, 0)))

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