Excel中IF匹配公式填充序列异常问题及优化方案咨询

需要用L列的商品重量，在O列匹配对应值后提取P列的价格，填入J列对应行。
使用以下公式时，快速填充会导致所有行号下移，匹配范围错误：

=IF(MATCH(L2,O2:O17,0),INDEX(P2:P17,MATCH(L2,O2:O17,0)))

询问如何避免该问题，或提供更合适的替代公式。

问题根源 #

公式里的O2:O17和P2:P17是相对引用，向下填充时会自动变成O3:O18、O4:O19，导致匹配范围不断下移，无法对应到固定的价格表区域。

替代方案 #

方法1：用绝对引用固定范围 #

给价格表范围添加$符号改成绝对引用，填充时范围保持不变：

=IFERROR(INDEX($P$2:$P$17,MATCH(L2,$O$2:$O$17,0)),"无匹配")

• 替换原公式的IF为IFERROR，可在无匹配值时返回自定义提示（比如"无匹配"），避免出现错误值
• $O$2:$O$17和$P$2:$P$17是绝对引用，填充时不会变动范围

方法2：使用VLOOKUP函数 #

VLOOKUP更适配这类"查找对应值"的场景，同样固定查找范围即可：

=IFERROR(VLOOKUP(L2,$O$2:$P$17,2,FALSE),"无匹配")

• L2为要查找的重量值
• $O$2:$P$17是包含查找列（O列）和结果列（P列）的区域，需保证查找列在区域第一列
• 2表示返回区域中第2列（即P列）的对应值
• FALSE表示启用精确匹配模式

方法3：使用XLOOKUP（Excel 365/2021及以上版本可用） #

XLOOKUP是更灵活的新一代查找函数，语法更直观：

=IFERROR(XLOOKUP(L2,$O$2:$O$17,$P$2:$P$17,"无匹配"),"无匹配")

• 无需考虑查找列的位置，直接指定查找值、查找范围、返回范围即可

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