请求协助：根据发票日期返回对应币种的产品历史价格及公式修正

问题场景 #

需要根据发票日期，从对应币种工作表（EUR/USD）中匹配产品的生效价格及对应生效日期：

• 例：产品发票日期为2023-11-27，币种EUR；EUR表中该产品2022-01-01起价格49欧元，2023-12-01起调整为52欧元，因发票日期早于调价日，应返回49欧元及2022-01-01。
• 原使用拼接产品与日期的VLOOKUP公式未得到正确结果，原公式：

=IF(I2="EUR";VLOOKUP(E2&VLOOKUP(D2;EUR!G:G;1;TRUE);EUR!A:J;7;0);VLOOKUP(E2&VLOOKUP(D2;USD!G:G;1;TRUE);USD!A:J;7;0))

解决方案 #

假设表结构：

• Main表：D列=发票日期，E列=产品编号，I列=币种（EUR/USD）
• EUR/USD表：A列=产品编号，G列=生效日期，H列=对应价格

1. 获取生效价格（Main表J列）

使用INDEX+MATCH组合实现多条件匹配（旧版Excel需按Ctrl+Shift+Enter作为数组公式执行，新版Excel自动支持）：

=IF(I2="EUR",
  INDEX(EUR!$H:$H, MATCH(1, (EUR!$A:$A=E2)*(EUR!$G:$G<=D2), 0)),
  INDEX(USD!$H:$H, MATCH(1, (USD!$A:$A=E2)*(USD!$G:$G<=D2), 0))
)

若使用新版Excel，可简化为XLOOKUP：

=IF(I2="EUR",
  XLOOKUP(1, (EUR!$A:$A=E2)*(EUR!$G:$G<=D2), EUR!$H:$H, "", 0, 1),
  XLOOKUP(1, (USD!$A:$A=E2)*(USD!$G:$G<=D2), USD!$H:$H, "", 0, 1)
)

2. 获取对应生效日期（Main表K列）

使用MAXIFS直接提取符合条件的最大生效日期（即最接近发票日期且不晚于它的生效日）：

=IF(I2="EUR",
  MAXIFS(EUR!$G:$G, EUR!$A:$A, E2, EUR!$G:$G, "<="&D2),
  MAXIFS(USD!$G:$G, USD!$A:$A, E2, USD!$G:$G, "<="&D2)
)

原公式失败原因 #

原公式通过E2&VLOOKUP(...)拼接产品编号与日期字符串，要求EUR/USD表中必须存在完全一致的拼接值才能匹配，但实际表中是一个产品对应多个生效日期，拼接后的字符串大概率不存在，导致匹配失效。

注意事项 #

• 确保EUR/USD表的生效日期列（G列）为升序排列，否则近似匹配类函数无法正常工作
• 若同一产品+生效日期存在重复数据，需先清理确保唯一对应关系

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