从表中查找值

Question

我想编写一个公式来获取表 1 中特定订单 ID（第 1 列）的出发城市和到达城市（第 2 列和第 3 列）之间的距离（第 4 列），同时从距离工作表中查找数据，如下所示。

距离工作表：

Answer 1

使用单元格 D2 中使用的 XLOOKUP() 函数

公式 --> 适用于 Excel 2021 和 Excel 2021仅限 O365 用户

=XLOOKUP(B2,$F$3:$F$7,XLOOKUP(C2,$G$2:$K$2,$G$3:$K$7),"Not Found")

因此，第二个 XLOOKUP() 函数返回

XLOOKUP(C2,$G$2:$K$2,$G$3:$K$7)

返回从以下任一位置获取的距离 (km) B 列中列出的到达堪培拉的地点，因为我以第一个单元格为例，所以它显示的是堪培拉，这就是对于 C 列中的其他 Destination 也是如此，

{285;676;1199;3735;0}

最后将其包装在另一个 XLOOKUP() 函数中，其中查找值来自于 列B 应返回准确的距离 必需的。

3 个替代公式：

• 对于所有用户

=VLOOKUP(B2,$F$3:$K$7,MATCH(C2,$F$2:$K$2,0),0)

• 对于 Office 365 和 Office 365 Excel 2021

=FILTER(FILTER($G$3:$K$7,(B2=$F$3:$F$7)),C2=$G$2:$K$2)

• 适用于 Office 365 和 Office 365 Excel 2021

=INDEX(FILTER($G$3:$K$7,B2=$F$3:$F$7),XMATCH(C2,$G$2:$K$2,0))

注意：为了方便起见，我在同一工作表中显示了两个表，因此您可能需要根据您的数据库相应地更改范围。

Answer 2

这是一个快速版本：

INDEX(A2:D5,MATCH(B8,A2:A5,0),MATCH(B9,A2:D2,0))

B8为起点，B9为终点。
没有重复您的所有数据，因为您将其作为图片提供，我懒得输入所有这些数据。

Answer 3

您可以使用 AVERAGEIFS 轻松完成此操作：

单元格 H4 中的公式：

=IF(H$3=$G4;"";AVERAGEIFS($D$2:$D$19;$B$2:$B$19;$G4;$C$2:$C$19;H$3))

拖至左侧并拖至底部。

您需要使用 AVERAGEIFS，因为点之间的距离在两个方向上相同。因为在您的数据中，您在 OrderId 2 中获得了 Melbourner - Sidney ，并在 OrderId 4 中获得了 Sidney - Melbourne 。这是相同的重复距离，因此如果您只是求和，你会产生一个错误。