XLOOKUP与INDEX-MATCH无法匹配超规包裹对应运单编号求助
2026-7-1
问题：匹配超规包裹对应运单编号并计算总费用
我在一家相框公司工作，需要找出哪些寄给客户的包裹因超规（超重、超大等）产生附加费。目前收到快递公司的运单数据表，要创建一个仅包含运单编号及总费用的可刷新表格，但无法将超规包裹行与所属主运单行匹配。
原始运单数据表
| 最后一个包裹编号 | 描述 | 费用 | 运单编号 | 运单内所有包裹编号 |
|---|---|---|---|---|
| 0001 | 标准件 | £ 5.00 | CN001 | 0001 |
| 0003 | 标准件 | £ 10.00 | CN002 | 0002;0003 |
| 0004 | 标准件 | £ 5.00 | CN003 | 0004 |
| 0008 | 标准件 | £ 20.00 | CN004 | 0005;0006;0007;0008 |
| 0010 | 标准件 | £10.00 | CN005 | 0009;0010 |
| 0002 | 超规件 | £ 15.00 | ||
| 0009 | 超规件 | £ 15.00 | ||
| 0010 | 超规件 | £ 15.00 |
已尝试的操作及问题
要求新表格可刷新，因此用IF语句嵌套函数：
=IF( E2= "", <function>, E2)
该公式在第4列（运单编号）有值时能正常显示数据，但第4列无值时，尝试用XLOOKUP结合TEXTSPLIT拆分第5列内容作为查找数组：
=XLOOKUP( A2, TEXTSPLIT( E2:E5, ";"), D2:D5)
所有超规行均返回#N/A错误；单独使用TEXTSPLIT时返回#VALUE错误。尝试过INDEX-MATCH组合，但无法实现匹配，寻求可行解决办法。
可行解决方案
方法1：兼容多数Excel版本（用ISNUMBER+SEARCH）
在超规行的运单编号单元格（比如D7，对应包裹0002）输入以下公式，向下填充：
=IF(D2<>"",D2,XLOOKUP(TRUE,ISNUMBER(SEARCH(A2,E$2:E$6)),D$2:D$6,"未找到"))
说明：
SEARCH(A2,E$2:E$6)检查当前超规包裹编号是否存在于主运单的包裹编号列表中，返回匹配位置的数组ISNUMBER将搜索结果转为布尔值，XLOOKUP匹配第一个TRUE对应的运单编号
方法2：适用于Excel 365/2021（动态数组方案）
利用动态数组函数拆分所有包裹编号并对应运单编号：
=IF(D2<>"",D2,XLOOKUP(A2,TOCOL(TEXTSPLIT(E$2:E$6,";")),REPT(D$2:D$6,COUNTA(TEXTSPLIT(E$2:E$6,";"))),"未找到"))
说明：
TEXTSPLIT(E$2:E$6,";")拆分所有主运单的包裹编号为二维数组TOCOL将二维数组转为一维数组，REPT重复对应运单编号，让包裹编号数组和运单编号数组长度完全匹配，最终用XLOOKUP精准查找
计算运单总费用
得到完整的运单编号列后，用SUMIF计算每个运单的总费用（假设运单编号在D列，费用在C列）：
=SUMIF(D:D,F2,C:C)
（其中F2为需要计算总费用的运单编号单元格）
内容的提问来源于stack exchange，提问作者TalynW
相关产品推荐
相关产品推荐
