Excel 多条件查找引用技巧

我们公司最近也正在招聘新员工，我也顺便去网上溜达了下。

看到很多岗位的招聘要求上都写着：「熟练使用 Excel 等办公软件」，而且在面试的时候还都有上机操作的题目。

然后就去网上搜了搜，看看 Excel 面试题都是什么内容、什么水准的题目，测试下自己能不能搞定。

在这个过程中，我发现了一道面试题，挺有意思的，今天分享给大家。

下表记录的是公司客户的每一次来店日期，要求：用函数提取出客户最后一次来店日期。

乍一看好像不难，但是对于小白而言，可能还是需要下一番功夫。

在简历上写着熟练或者精通 Excel 的人，估计有相当一部分是做不出来的

分析问题

我们先来分析下这道题！

这个题目是一个双条件查找引用，而且是查找最后一次的【来店日期】。

比如：客户【张三】有很多重名的，【客户编号】也有重复的。

要求：提取出姓名为【张三】，并且编号为【MD003】的最后一次【来店日期】。

在图中：【张三】【MD003】的【来店日期】一共有两次。

第一次：2022-1-4

第二次（也就是最后一次）：2022-1-8

思考一下，如果是你，你会用什么办法呢？

对于查找，我最先想到的是用 Vlookup 函数（可能有很多人的第一反应也是这个），因为但凡学习 Excel 的人都会接触到它，它也是最常用的查找函数。

这个思路是对的，Vlookup 还真能解决这个问题，下面我们就一起来看看~

解决问题

▋方法 1

Vlookup 通常用于单条件查找，对于双条件或者更多条件的查找时，它自己无法单独完成……

那该怎么办呢？

我们可以结合 IF 函数来实现双条件查找。

如下图：

公式如下：

=VLOOKUP(1,IF({1,0},0/(E2&F2=$A$2:$A$15&$B$2:$B$15),$C$2:$C$15),2,1)

公式大概的意思是：

利用二分法查找的原理，匹配最后一个符合条件的值。

用 if {1,0} 组成一个查找区域和返回区域，

条件区域是：

$A$2:$A$15&$B$2:$B$15

把两个条件 E2 与 F2 用连接符（&）连在一起，然后与 A2:A15 与 B2:B15 的连接起来条件区域相比较，

E2&F2=$A$2:$A$15&$B$2:$B$15

如果相同就返回 TRUE, 否则就返回 FALSE，

结果如下：

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

然后用 0 除以这个数组，得到一个由 0 和错误值组成的内存数组。

{#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

其中：有两个 0 就是跟查找条件一样，最后用比 0 大的任何一个值 ，这里使用 1 去查找最后一个 0 出现的位置，并返回在 $C$2:$C$15 对应的单元格内容。

需要注意的是：

此公式为数组公式需要按三键【Ctrl+Shift+Enter】结束公式输入。

如果是 Office 365 可以直接按【Enter】。

▋方法 2

对于大部分小伙伴们来说，如此复杂的公式不一定会用……

那该怎么办呢？

别急！

面试时一定要稳住！

如果只看题目要求的话，我们可以变通下，让 V 函数使用起来更简单点。

这里我们可以将两个条件变成一个条件，将返回最后一个值，变成返回第一个值。

需要做的是：增加一个辅助列，并且对数据源做个排序。

具体是什么意思呢？往下看 ↓↓↓

❶ 添加辅助列

这一步的目的是将两个条件变成一个条件。

在【A】列增加一个辅助列：

在 A2 单元格中输入如下公式：

=B2&C2

将 B2 的客户名称和 C2 的客户编号用连接符（&）连接在一起，组成一个条件。

并将公式下拉填充到【A15】。

❷ 排序

这一步的目的是将最大的日期排到最上面。以便 V 函数查找第一个值。

在【数据选项卡】中调出【排序】对话框：

并在【排序】对话框中设置三个排序。

第一关键字：客户名称，升序

第二关键字：客户编号，升序

第三关键字：来店日期，降序

最后单击【确定】，排序结果就出来了。

如下图：

❸ 输入公式

这一步就可以和平常使用 V 函数做一样的操作啦！

如下图：

公式如下：

=VLOOKUP(F2&G2,$A$1:$D$15,4,0)

公式的意思是：

将 F2 和 G2 两个单元格的内容连接在一起，组成一个条件，然后在以辅助列开始的【A1:D15】这个区域查找第一次出现的日期（也就是最后一次来店日期），并返回第 4 列对应的值。

PS：这个方法是采取了变通的方式。有些时候使用辅助列来解决问题也是一个不错的思路。

▋方法 3

另外，这道题是求最后一次来店日期。

那么最后一次的来店日期也就是最大的日期。

因此我们也可以用 MAX+IF 组合来完成。

所以我们可以写成下面这样：

=MAX(IF($A$2:$A$15&$B$2:$B$15=E2&F2,$C$2:$C$15))

公式解析：

先将 A 列与 B 列两个条件区域连接成一个条件区域，再和 E 列与 F 列中的条件相比较，如果条件相同，就返回 C 列的区域。最后用 MAX 返回其中最大一个日期值（也就是最后一次来店日期）。

如果你的版本是 Office 2016 版以上，还可以使用 MAXIFS 函数。

=MAXIFS(C:C,A:A,E2,B:B,F2)

Maxifs 函数的用法跟 Sumifs 的函数用法一样。

第一参数是：返回的区域

第二参数是：条件区域

第三参数是：条件

基本套路为：

=MAXIFS返回的区域,条件区域 1,条件 1,条件区域 2,条件 2……)

条件区域和条件对，最多可输入 126 对。

知识拓展

如果题目没有一定要求用函数解决的话，用透视表可能更加简单。

在【插入选项卡】中点击【数据透视表】，调出【数据透视表】对话框。

选中【A1：C5】，并选择【现有工作表】中的【E7】单元格，最后点【确定】。

然后将【客户名称】和【客户编号】拖到【行区域】，【来店日期】拖到【值区域】

对客户编号进行筛选，

选中【计数项: 来店日期】右键设置为：最大值。

最终效果如下图：

好了，我们的面试题这就做完了！

但是，问题来了，你怎么知道结果是否正确呢？

我们在做表的时候，千万要牢记一点，就是要有核对机制。

比如：我们使用 V 函数得出的结果如下：

有的应聘者可能会在 H 列用其他函数（比如：MAX+IF 函数）再校验一次，

最后用两个不同的函数得出的结果值进行比较，并将比较结果放在 I 列。

如果为 true，就是两次结果相同。

如果为 false，就是不同，应进一步查明问题所在。

当然也可以用其他的方法来校验，比如透视表等。方法可以自己选择。

总结一下

今天介绍了如下方法进行多条件查找引用：

❶ VLOOKUP 函数

此函数在多条件查找时需要结合 IF 函数进行数据重新构造，比较复杂。

❷ VLOOKUP 函数 + 辅助列

这种方法适应性比较强，小白也容易上手。

❸ MAX+IF 函数组合

采用的数组判断的方法，适合有一定数组基础的人使用。

❹ MAXIFS 函数

使用方法简单，但只能在 OFFICE 2016 以上的版本中使用。

❺ 透视表

方法最为简单，适用范围广。

另外，在职场中，千万记得做完一件事之后，一定要有核对机制。

并且要有据可查！

职场中有时拼的不仅仅是技能，更重要的是经验！

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：明镜在心，审核：小爽，编辑：竺兰

广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。