只能用VLOOKUP匹配数据？还有更多的公式（查找引用公式集锦）

最近推送的几篇文章：

·  正  ·  文  ·  来  ·  啦  ·

需求

什么是“查找引用”，通俗一点讲，就是“抄”，将其他表已有的数据抄到本表格来。抄的方式有多种，按指定条件、按位置、按排行，最常见的就是按指定的条件“抄”。

一些新手在工作中遇到这种“抄”的需求时，不知道该用什么函数，在偷懒的技术读者群经常看到下面这种类似的提问：

象上面这种，需要按指定的条件、指定的字段匹配数据，匹配到后，将对应的数据“抄”过来就用查找引用函数，在查找引用函数里最常见的就是VLOOKUP函数了。这个函数让我们领略到函数的威力。但是，只会这个函数就够了吗？

显然是不够的，本文就对查找引用能用到的函数做个小小总结。

查找引用公式

一、使用VLOOKUP

VLOOKUP函数语法：

=VLOOKUP（找什么，在哪找，第几列，查找模式）

要点：

  • 第一参数“找什么”要在第二参数的首列

  • 第三参数是指在第二参数的第几列，而不是表格中的第几列

  • 第四参数为0时为精确匹配，为1时为近似匹配

=VLOOKUP(A11,$B$2:$C$7,2,0)

二、使用INDEX+MATCH经典组合
INDEX的作用简而化之，记住二点差不多就用了
1、根据第几行第几列来交叉取数
2、取列表中的第几个
而MATCH是数数的，告诉我们某个指定的数值是列表中的第几个。
组合在一起，其公式为：
=INDEX($C$2:$C$7,MATCH(A11,$B$2:$B$7,0))
这个公式翻译一下就是：
先用MATCH数数，计算出A11的商品D是B2:B7单元格区域中的第4个。然后用INDEX取C2:C7单元格区域中的第4个，就取到了商品D对应的单价22。
三、使用OFFSET+MATCH
OFFSET函数是根据位置来取数的，只要给它一个原点，告诉他往上下偏移多少行、再往左右偏移多少列来取数。语法格式：
=OFFSET（定位单元格，偏移多少行，偏移多少列，取几行，取几列）
将它与MATCH结合，其公式为：
=OFFSET($C$1,MATCH(A11,$B$2:$B$7,0),0)
四、使用LOOKUP函数
这个函数之前详细介绍过，也做过答疑，就不详细介绍了，请看下面的文章
【扩展】新手进阶必学的三个函数①：LOOKUP
深入理解LOOKUP：LOOKUP函数的查找原理
公式解释：LOOKUP反向模糊查询公式
用到本案例，其公式为：
=LOOKUP(1,0/(A14=$B$2:$B$7),$C$2:$C$7)
五、使用最新的XLOOKUP函数
XLOOKUP函数是查找引用函数中的王者，它可以纵向查找、横向查找、逆向查找、从下往上找、还可进行数组运算，有了它，VLOOKUP、LOOKUP、INDEX+MATCH离下岗不远了，详见：
新函数XLOOKUP详解，VLOOKUP自愧不如、自惭形秽、自求下岗
公式：
=XLOOKUP(A11,B2:B7,C2:C7)
六、使用FILTER函数
FILTER函数本来是做筛选的，貌似不能用来查找，但是本案例中源数据表的商品名称都是唯一的，因而，也可用它来做查找。详细解释：
OFFICE 365的FILTER函数，颠覆了我对Excel的认知②
公式：
=FILTER(C2:C7,B2:B7=A16)
七、使用INDIRCT函数
INDIRCT是将文本变为引用，在本案例中先拼凑出C11这个文本，然后将其变为引用，也就是引用C1单元格。
公式：
=INDIRECT("C"&MATCH(A11,$B$1:$B$7,0))
八、使用SUMIF函数
SUMIF是做条件求和的，本案例中源数据表的商品名称都是唯一的，因而，也可用它来做查找。
公式：
=SUMIF($B$2:$B$7,A11,$C$2)
大家看了上面的公式，可能会觉得奇怪，为什么第三参数写成C2，而不是C2:C7，实际上效果是一样的，详细情况参见：
深入理解SUMIF：如何多表多列多条件求和？
赞 (0)

相关推荐