时间戳

首页 > 解决方案 > 从 A 列 Excel 数组公式中查找 C 列中最接近的值

excel - 从 A 列 Excel 数组公式中查找 C 列中最接近的值

问题描述

我在 A 列中有数字，在 B 中有相应的代码。现在对于 CI 中的数字，想在 A 中搜索每个最接近的值并显示其 B 的值。所以我在 D2 中使用以下数组公式，它几乎适用于所有情况，但为 C 列中的最后一个值打印错误值 (38909)。A 列中与 38909 最接近的值为 38909，其相关代码为 PUK，但正在打印与值 3890947021 相关的 JIM。

请有人帮我修复公式以匹配此表中的所有案例。谢谢

我的公式是：

=IF($C2="","",IFERROR(LOOKUP(9.99999999999999E+307,
SEARCH(IF(LEN($A$2:$A$15)>LEN($C2),"|"
&LEFT($A$2:$A$15,LEN($C2)),"|"
&$A$2:$A$15),"|"&$C2),
$B$2:$B$15),"NOT FOUND"))

桌子

[A]         [B]     [C]         [D]          [E] 
CC          CODE    NUMBERS     RESULT NOW   RESULT EXPECTED
237         CMR     18763044    JAM          JAM
230         MUS     187635      JAM          JAM
61          AUS     23092       MUS          MUS
31          NLD     3162        NLD          NLD
599         ANT     38050       NOT FOUND    NOT FOUND
358         FIN     33          FRA          FRA
33751       FRA     49185       NOT FOUND    NOT FOUND
65          SGP     51078       NOT FOUND    NOT FOUND
1721        SXM     1246        BRB          BRB
1876        JAM     389094702   JIM          JIM
81          JPN     38909       JIM          PUK
124622      BRB
38909       PUK
3890947021  JIM

更新

几个小时后，如果 A 列中的值按升序排序，我就能得到这个公式。

=IF($C2="","",IFERROR(
LOOKUP(9.99999999999999E+307,
SEARCH(
IF(OR($A$2:$A$15=$C2),"|"&$A$2:$A$15,
IF(LEN($A$2:$A$15)>LEN($C2),
"|"&LEFT($A$2:$A$15,LEN($C2)),
"|"&$A$2:$A$15)),
"|"&$C2),$B$2:$B$15),"NOT FOUND"))

感谢您的意见。

标签： excelexcel-formulaarray-formulas

解决方案

如果我正确理解你的公式，它会完全按照它应该做的。问题是 38909 在 3890947021 中。对于 38909，您的公式有两个可能的匹配项，所以它给您最后一个。如果您更改 A 列和 B 列中的顺序，例如：

[A]         [B]
CC          CODE
237         CMR
230         MUS
61          AUS
31          NLD
599         ANT
358         FIN
33751       FRA
65          SGP
1721        SXM
1876        JAM
81          JPN
124622      BRB
3890947021  JIM
38909       PUK

您将获得数字 3890947021 和 38909 的 PUK。我看到的唯一方法是在公式中添加另一个条件，该条件将检查 A 列中是否存在完全匹配，但如果顺序相反，它仍然无法解决您的问题.

推荐阅读