如何让Google表格主状态列表同步最新信息且兼容现有公式？

背景与当前实现 #

我搭建了一套图书借还流程：通过Google表单扫描图书条码，表单响应自动填充到表格中；同时设置了「主状态列表」，用于展示每件物品的借还状态（IN/OUT）。

目前使用公式获取最新状态：

=INDEX($D$2:$D$123,MATCH(2,1/(C2:C123=F4)))

该公式通过匹配C列（物品）的最新记录，返回对应D列的状态。

遇到的问题 #

主状态列表仅能显示物品和状态，无法同步展示对应最新记录的**A列（时间戳）和B列（姓名）**信息——A、B列会随每次借还操作更新，现有公式仅能关联C、D列，无法联动A、B的最新数据。

示例场景 #

• 行23的「BOB Books set 2」，主状态列表F23+G23仅显示物品和状态，无法查看借还人及时间；
• 若后续J. Doe在12/22归还该物品，表单数据会填充到行34，主状态的物品和状态能自动更新，但姓名和时间无法同步，因为表格无法关联最新记录对应的A、B列内容。

解决思路 #

方法1：扩展现有INDEX/MATCH公式 #

直接修改公式适配A、B列，复用原匹配逻辑：

• 获取最新时间戳（A列）：

  =INDEX($A$2:$A$123,MATCH(2,1/(C2:C123=F4)))
  

• 获取最新借还人（B列）：

  =INDEX($B$2:$B$123,MATCH(2,1/(C2:C123=F4)))
  

提示：若表格行数会动态增加，建议将固定范围（如$A$2:$A$123）改为动态范围（如$A:$A，需排除表头），避免新增记录后公式失效。

方法2：使用QUERY函数实现动态关联 #

用QUERY筛选每个物品的最新记录，一次性返回时间、姓名、状态：

=QUERY($A$2:$D$123,"SELECT A,B,D WHERE C = '"&F4&"' ORDER BY A DESC LIMIT 1",0)

若需拆分到单独单元格，可配合INDEX提取对应列：

• 时间戳：=INDEX(QUERY($A$2:$D$123,"SELECT A,B,D WHERE C = '"&F4&"' ORDER BY A DESC LIMIT 1",0),1,1)
• 姓名：=INDEX(QUERY($A$2:$D$123,"SELECT A,B,D WHERE C = '"&F4&"' ORDER BY A DESC LIMIT 1",0),1,2)
• 状态：=INDEX(QUERY($A$2:$D$123,"SELECT A,B,D WHERE C = '"&F4&"' ORDER BY A DESC LIMIT 1",0),1,3)

方法3：用ARRAYFORMULA批量生成主状态 #

若主状态列表的物品为唯一值，可通过ARRAYFORMULA一次性生成所有物品的最新信息，无需逐个单元格输入公式：

• 最新时间戳：

  =ARRAYFORMULA(IF(F2:F="",,VLOOKUP(F2:F,SORT($A$2:$D$123,ROW($A$2:$D$123),FALSE),1,FALSE)))
  

• 最新姓名：

  =ARRAYFORMULA(IF(F2:F="",,VLOOKUP(F2:F,SORT($A$2:$D$123,ROW($A$2:$D$123),FALSE),2,FALSE)))
  

• 最新状态：

  =ARRAYFORMULA(IF(F2:F="",,VLOOKUP(F2:F,SORT($A$2:$D$123,ROW($A$2:$D$123),FALSE),4,FALSE)))
  

原理：先将所有记录按行号倒序排列（行号越大越新），再通过VLOOKUP匹配物品，返回对应最新信息。

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