条件值位置不固定，可能在A列也可能在B列，该如何查找？
作者：老菜鸟来源：部落窝教育发布时间：2023-02-13 13:44:21点击：793
编按：
有一种比较特别的数据查找，条件值（如人名）的位置不固定在某列，而是可能存在A、B、C等列中的任何一列中，也就是A、B、C列的数据是并列的，都是人名；那如何在并列的多列数据中查到条件值并返回对应的需要值呢？很显然不适合直接用VLOOKUP函数——因为它要求条件值必须位于查找区域的首列。今天就来给大家分享几个函数公式。
在群里看到一个数据匹配的问题觉得挺有趣，将数据模拟了一下，如图所示，要根据人物名称匹配出对应的书名。
类似的还有根据员工姓名匹配部门、根据配件名称匹配对应的成品等等，都是在并列的多列数据中查找某个值再返回对应的值。
下面就来具体分析一下解决这类问题的常用思路。
常规数据匹配思路：
首先要明确的是目标结果（书名）所在位置是A2:A5这个单元格区域，只要能够确定是这个区域的第几行，就能得到所需结果。
而要确定目标在第几行，就需要根据提供的人物与B2:J5这个区域中的单元格去比较，这也是解决问题的最核心所在。
这一步有两个公式可以实现：
公式1 =SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4))
公式2 =MAX(IF($B$2:$J$5=A8,ROW($A$1:$A$4))) 数组公式，需要三键（Ctrl、shift、enter）输入。
两个公式中都用到了$B$2:$J$5=A8，这个等式会得到一组逻辑值，人物所对应的具体单元格会返回TRUE。
两个公式的另外一个共同点就是ROW($A$1:$A$4)，这部分相当于常量数组{1;2;3;4}。
公式1是利用SUMPRODUCT计算出一组逻辑值和数组的乘积之和，最终得到符合条件的姓名所在的行号。
关于SUMPRODUCT函数的详细示例参考教程：Excel的求和函数之王！
公式2则是利用IF函数返回数据，其中TRUE所对应的是一个数字。
再用MAX得到这一组数据中的最大值，从而得到姓名所在的行号。
要理解这个思路需要对数组有一定的认识才行，关键在于公式中IF函数的第一参数是一组逻辑值而不是一个逻辑值，所以IF函数返回的结果也是一组数据，必须借助MAX得到需要的结果。
理解了以上两个公式，其实要解决问题就非常容易了，使用INDEX、OFFSET和INDIRECT函数都可以得到正确结果。
以下采用公式1的进行介绍，有兴趣的同学可以自己替换公式2去研究一下。
INDEX函数解法：
公式为：=INDEX($A$2:$A$5,SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4)))
INDEX根据SUMPRODUCT得到的行号，在目标区域中得到对应的书名。
OFFSET函数解法：
公式为：=OFFSET($A$1,SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4)),)
OFFSET以A1单元格为基点，按照SUMPRODUCT得到的行数向下偏移，就得到了对应的书名。
INDIRECT函数解法：
公式为：=INDIRECT("A"&SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$2:$A$5)))
注意这个公式中的ROW($A$2:$A$5)和前几个公式稍有区别，因为INDIRECT需要的是表格中的行号，而不是目标区域中的行号，所以这里用的$A$2:$A$5而不是$A$1:$A$4。
以上分享的这些公式都是常规数据匹配思路，最后再来分享两个不一样的思路，分别用到了合并函数CONCAT函数和TEXTJOIN函数，并且都是数组公式，需要三键（Ctrl、shift、enter）输入。
如果你的Excel或WPS也有这两个函数的话，就可以研究一下了。
CONCAT函数解法：
公式为：=CONCAT(IF($B$2:$J$5=A8,$A$2:$A$5,""))
TEXTJOIN函数解法：
公式为：=TEXTJOIN("",,IF($B$2:$J$5=A8,$A$2:$A$5,""))
这两个公式的本质都是一样的，利用IF函数返回一组文本，只有一个是书名，其他都是空值。
将这一组文本合并后就得到所需要的结果。
以上就是今天要分享的全部内容，不知道你学会了多少，留一个自测题吧。
如果大家知道答案的话，可以在评论区留言告诉我们。
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
再因为核对数据而加班，买块豆腐吧！难道12种方法不够你用？！
版权申明：
本文作者老菜鸟；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。