如何使用vlookup函数实现一对多查找

使用过vlookup函数的大虾们都知道，对于vlookup函数来说，一对一的查找是相当简单滴。但对于“一对多”的情况（如下图：B列中原料名称与C列的供应商关系，即一种原料有多个供应商时），此种情况下如何使用vlookup函数实现像右侧表中的格式显示呢？

操作方法

  • 01

    为简化公式，本例选择A列作为辅助列。在A2单元格中，输入公式=B2&COUNTIF(B$1:B2,B2)。

  • 02

    回车，然后向下填充。填充时可根据实验需要的行数进行填充。如果不影响打印等效果，可一次性向下填充数十行甚至更多。

  • 03

    在F2单元格中输入公式=IFERROR(VLOOKUP($E2&COLUMN(A1),$A:$C,3,0),"")。此外使用IFERROR函数，使无数据单元格显示空白。

  • 04

    回车，然后向左向下填充。填充区域大小根据实际需要进行选择。此时，各原料的供应商均显示出来了。

  • 05

    验证一下，在左侧表中输入原料名称和供应商。在B列中输入已存在原料C，在右侧表中，可自动增加显示供应商名称。若在B列中输入新原料名称D，输入结束后，在E列中输入此新原料名称D后，就会自动显示供应商。

  • 06

    但有个问题，就是重复输入时，右侧供应商表中会显示相同名称的供应商，如下图。为避免此情况出现，应对B列和C列进行数据有效性设置，以避免重复数据的输入。

  • 07

    数据有效性设置：在B、C列中，选择适当区域；点击数据－数据有效性－设置自定义，在公式处输入：=SUM(N(($B2&"|"&$C2)=($B$2:$B$200&"|"&$C$2:$C$200)))=1，设置出错警告，确定后，重新输入重复数据，此时会弹出警告提示，如下图。

赞 (0)

相关推荐