Excel匹配UPS与沃尔玛最近坐标时公式返回#VALUE!求助

问题背景 #

你手里有两组门店数据：

• UPS门店：A列存编码，B列是纬度，C列是经度，一共400多行
• 沃尔玛门店：E列是纬度，F列是经度

要找出每个沃尔玛门店对应的最近UPS门店编码，用了下面这个公式：

=INDEX($A$3:$A$434, MATCH(MIN(SQRT((E11-B$3:B$434)^2+(F11-C$3:C$434)^2)), SQRT((E11-B$3:B$434)^2+(F11-C$3:C$434)^2), 0))

这个公式在10行小样本里能正常输出结果，但放到400+行的真实数据中，全部返回#VALUE!错误。（小样本正常运行时，公式会准确对应到最近的UPS门店编码）

错误原因&解决办法 #

1. 数组公式未按正确方式输入

如果使用的是Excel 2019或更早版本，这种跨多单元格运算的公式需要按Ctrl+Shift+Enter作为数组公式输入，大样本下未执行此操作就会报错。输入完公式不要直接按Enter，按下这三个组合键，公式会自动被大括号{}包裹（不要手动输入大括号）。

2. 数据类型存在异常

B、C、E、F列中可能混入了非数值数据（比如文本格式的经纬度、空单元格、特殊字符），小样本刚好没碰到这类情况，大样本中出现后就会导致运算出错。

• 可以用=VALUE()函数批量将这些列转换为数值格式，或者通过「数据-分列」功能快速统一数据类型
• 筛选出空值或非数值的单元格，补全有效数据或删除无效行

3. 计算量超限触发限制

400多行的平方根运算，旧版Excel可能无法承载。其实比较距离时，SQRT()不影响最小值的判断（平方根是单调递增函数，原数最小则平方根也最小），可以去掉SQRT()简化公式，减少运算量：

=INDEX($A$3:$A$434, MATCH(MIN((E11-B$3:B$434)^2+(F11-C$3:C$434)^2), (E11-B$3:B$434)^2+(F11-C$3:C$434)^2, 0))

旧版Excel仍需按Ctrl+Shift+Enter输入，新版Excel直接按Enter即可。

4. 用XLOOKUP简化（仅限Excel 365/2021）

如果是新版Excel，直接用XLOOKUP更省心，无需手动设置数组输入，公式也更简洁：

=XLOOKUP(MIN((E11-B$3:B$434)^2+(F11-C$3:C$434)^2), (E11-B$3:B$434)^2+(F11-C$3:C$434)^2, $A$3:$A$434)

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