如何基于下拉值用VLOOKUP公式跨工作表提取潜在收入

核心公式方案（分两种场景） #

场景1：双条件+区间匹配（投标金额落在某区间内）

假设首个工作表（简称「表1」）中：

• 下拉值单元格为 C2（如客户类型/项目类别）
• 投标金额（Bid Amount）单元格为 D2
  另一对照表工作表（简称「对照表」）结构如下：

在表1的目标单元格输入以下数组公式：

=VLOOKUP(1,IF((对照表!$A$2:$A$10=C2)*(对照表!$B$2:$B$10<=D2)*(对照表!$C$2:$C$10>=D2),对照表!$D$2:$D$10,""),0,FALSE)

注意：Excel 2019及更早版本需按 Ctrl+Shift+Enter 触发数组计算；365/2021版本直接回车即可。

场景2：下拉类别分组+投标金额近似匹配

若「对照表」按下拉类别分组，且投标金额列已升序排列：

使用以下公式实现精准分组匹配：

=VLOOKUP(D2,OFFSET(对照表!$B$1,MATCH(C2,对照表!$A$1:$A$10,0)-1,0,COUNTIF(对照表!$A$1:$A$10,C2),2),2,TRUE)

常见错误排查 #

• 查找值位置错误：VLOOKUP要求查找值必须是目标区域的首列，若你的对照表首列不是下拉类别/投标金额，需调整区域顺序或改用INDEX+MATCH组合
• 未加绝对引用：公式中对照表的区域需加 $ 锁定（如 $A$2:$A$10），否则下拉公式时区域会偏移导致错误
• 区间匹配未排序：使用近似匹配（最后一个参数为TRUE）时，投标金额列必须升序排列，否则匹配结果混乱
• 双条件未合并：直接用单条件VLOOKUP会忽略下拉值，导致匹配到其他类别的数据

调试技巧 #

• 选中公式中的某段（如 (对照表!$A$2:$A$10=C2)），按 F9 查看计算结果，确认是否正确定位到目标类别行
• 检查对照表中是否存在重复的「下拉类别+投标金额」组合，重复值会导致VLOOKUP返回首个匹配结果

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