☰

Excel：为什么 VLOOKUP 返回不一致且意外的结果？

tag-icon • tag-icon microsoft-excel-2010 worksheet-function vlookup
Excel：为什么 VLOOKUP 返回不一致且意外的结果？

我正在自动执行 Excel 列，以根据付款的供应商分配帐户值。例如，如果 AT&T 收到付款，则帐户值为Utilities:Telephone，而当邮局收到付款时，帐户值将编码为Postage and Shipping。我使用以下公式

=VLOOKUP(B2,$E$2:$F$8,2,1)

计算Notes列中的值。ExpectedValue是硬编码的，并且Valid基于是否Notes等于我的ExpectedValue

如果我做了我希望做的事，那么Description就会与我的匹配LookupValue，如果匹配度很高，则该字段会填充来自的相应值DecodedValue。

基于此，第 1 行是OK。但是，第 2-8 行是BAD，不知何故这些行与我的查找表中的第一个索引值匹配并解码为第一个索引值。因为以 开头的行PAYPAL都是OK，所以我认为可能是查找表中的空格或标点符号。但是，如果是这样的话，第 5、8 和 15 行应该通过。所以我认为只有当查找值位于单元格的开头时它才与查找匹配。但是那么第 1 行就不会通过。

15 行 CSV 数据，粘贴在下面并引用上面。

Line,Description,Notes,ExpectedValue,Valid,LookupValue,DecodeValue
1,DDA PUR ATT*PAYMEN 800-331-0500 TX 300100860296,Utilities:Telephone,Utilities:Telephone,OK,ATT*PAY,Utilities:Telephone
2,DDA PUR THE HOME D MILWAUKEE    WI,Utilities:Telephone,Repairs,BAD,NETFLIX,Supplies:Research
3,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,PAYPAL,Supplies
4,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,STONE CREE,Craft Service
5,DDA PUR WALGREENS SHOREWOOD    WI,Utilities:Telephone,Medical,BAD,WALGREENS,Medical
6,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,THE HOME D,Repairs
7,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,USPS,Postage and Shipping
8,DDA PUR NETFLIX.CO NETFLIX.COM  CA,Utilities:Telephone,Supplies:Research,BAD,,
9,PAYPAL           INST XFER,Supplies,Supplies,OK,,
10,PAYPAL           INST XFER,Supplies,Supplies,OK,,
11,PAYPAL           INST XFER,Supplies,Supplies,OK,,
12,PAYPAL           INST XFER,Supplies,Supplies,OK,,
13,PAYPAL           INST XFER,Supplies,Supplies,OK,,
14,DDA PUR THE HOME D MILWAUKEE    WI,Utilities:Telephone,Repairs,BAD,,
15,DDA PUR USPS 56064 MILWAUKEE    WI,Utilities:Telephone,Postage and Shipping,BAD,,

答案1

正如评论中已经解释的那样，range_lookup的参数VLOOKUP不是模糊搜索。相反，它假设列表按升序排列并找到最接近的匹配项。

这是您应该使用的数组公式（输入使用CTRL++ ）ALTENTER

{=IF(MIN(LEN(SUBSTITUTE($B2,$F$2:$F$8,"")))=MAX(LEN(SUBSTITUTE($B2,$F$2:$F$8,""))),"",INDEX($G$2:$G$8,MATCH(MIN(LEN(SUBSTITUTE($B2,$F$2:$F$8,""))),LEN(SUBSTITUTE($B2,$F$2:$F$8,"")),0)))}

它的工作原理如下：

公式的一部分重复多次：（LEN(SUBSTITUTE($B2,$F$2:$F$8,""))
我将LEN(...)在以后使用以节省空间）
它为我们提供了一个整数数组，该数组基于用LookupValue空格替换每个Description整数并测量结果的长度。长度越短，匹配的文本越多。从MIN该数组中取出可以告诉我们LookupValue在中存在的最长值Description。当然，这也意味着THE HOME D将始终被或其他东西打败，因此请在您的领域THE HOME DESIGNERS中小心/精确。 解决这一部分后，让我们像这样展示公式，以使其更易于理解：LookupValue

{=IF(MIN(LEN(...)))=MAX(LEN(...))),"",INDEX($G$2:$G$8,MATCH(MIN(LEN(...))),LEN(...)),0)))}

第一部分IF(MIN=MAX)检查以确保LookupValue描述中至少存在一条记录。如果没有，SUBSTITUTE则不执行任何操作，并且每个LEN值都将相同。

INDEX($G$2:$G$8,MATCH(MIN(LEN(...))),LEN(...)),0))

一旦我们通过了检查，真正的重点就在INDEX+MATCH组合中。我们在长度数组中匹配最短的长度（即LookupValue中存在的最长长度Description），以便使用第一的 LookupValue找到后，将其插入数组中，DecodeValue即可弹出Notes所需的值。

关键点是使您的LookupValue字段尽可能长且准确，以避免重复匹配，然后一切都应该正常工作。

以下是我的结果的 CSV 版本：

行、描述、注释、预期值、有效、查找值、解码值
1、DDA PUR ATTPAYMEN 800-331-0500 TX 300100860296，公用设施：电话，公用设施：电话，OK，ATT付费，公用事业：电话
2，DDA PUR THE HOME D MILWAUKEE WI，维修，维修，OK，NETFLIX，供应：研究
3，DDA PUR STONE CREE SHOREWOOD WI，工艺服务，工艺服务，OK，PAYPAL，供应
4，DDA PUR STONE CREE SHOREWOOD WI，工艺服务，工艺服务，OK，STONE CREE，工艺服务
5，DDA PUR WALGREENS SHOREWOOD WI，医疗，医疗，OK，WALGREENS，医疗
6，DDA PUR STONE CREE SHOREWOOD WI，工艺服务，工艺服务，OK，THE HOME D，维修
7，DDA PUR STONE CREE SHOREWOOD WI，工艺服务，工艺服务，OK，USPS，邮资和运费
8，DDA PUR NETFLIX.CO NETFLIX.COM CA,供应：研究,供应：研究,OK,,
9,PAYPAL INST XFER,供应,供应,OK,,
10,PAYPAL INST XFER,供应,供应,OK,,
11,PAYPAL INST XFER,供应,供应,OK,,
12,PAYPAL INST XFER,供应,供应,OK,,
13,PAYPAL INST XFER,供应,供应,OK,,
14,DDA PUR THE HOME D MILWAUKEE WI,维修,维修,OK,,
15,DDA PUR USPS 56064 MILWAUKEE WI,邮资和运费,邮资和运费,OK,,

相关内容