查找匹配字段并返回对应值 (Excel 篇)

一、场景描述——两张数据表：

1. 索引库

假设我们有一个数据比较齐全的基础数据表（这里不妨称为 “索引库”），作为日常检索用。如下图所示：

索引库.png

2. 查询库

现在接收到一张新的数据表（这里称为 “查询库”），如下图所示：

查询库.png

我们希望达到什么目的呢？
将查询库与索引库进行数据比对，以 ”姓名“ 作为主键 (Primary Key) 返回补全以下字段：“是否存在”、“性别”、”年龄”、“教育程度”、“星球”。

咳咳~说人话！
<查询库>：“嗨，大佬！听说你上知天文下知地理，我手头上有一些人的资料，但是很不齐全，只知道他们的婚姻状况。你看能不能帮忙补齐他们的资料，好让我多了解他们一点。”
<索引库>：“当然可以！你把他们的 ‘姓名’ 发给我，凡是在我这里备案了的，我会把他们的 ‘性别’、‘年龄’、‘教育程度’ 和 ‘星球’ 全部打包给你。但是也可能有少数没备案过的人，那我就爱莫能助了，建议你添加 ‘是否存在’ 一项，将这些没备案过的人标记为 ‘否’，备案过的人标记为 ‘是’。“
<查询库>：“哇，这样就一目了然了！”

二、实现方式：

1. 在 “查询库” 中补全字段：

Excel 补全字段.png

2. 尝试使用 VLOOKUP 函数

因为判断 “是否存在” 需要将查找函数作为基础函数，所以我们先尝试查询 “性别” 字段，并返回对应值，公式为：
=VLOOKUP($A2,索引库!$A:$E,2,FALSE)

尝试查询字段并返回对应值.png

结果返回的是 N/A，回头检查公式，没有大的问题！填充序列后，发现有既有正常的返回值，也有 N/A 值：

尝试填充序列.png

肉身侦查 “索引库”，发现报错的两行中，它们的主键值 “肖二” 和 ”赵六“ 恰好不在 ”索引库“。于是我们需要事先判断 ”查询库“ 中的主键值在 ”索引库“ 中是否存在。

在索引库中不存在的项将返回 N/A 值

3. 使用 IF + ISERROR + VLOOKUP 函数查询主键 “是否存在”

既然当主键值不存在时，VLOOKUP 将返回 N/A 值，那么 ISERROR 函数可以将 N/A 值转换为布尔值，这样就变成了我们所需的布尔运算，用 IF 函数来判断 “是” 还是 “否”，公式为：
=IF(ISERROR(VLOOKUP(A2,索引库!$A:$E,1,FALSE)),"否","是")

用逻辑函数和 VLOOKUP 判断存在性

4. 嵌套布尔函数以改造其他字段的 VLOOKUP 公式

我们的改造目标是，预先判断主键值是否存在，若存在，则用 VLOOKUP 返回对应值；若不存在，则为空。在上述公式的基础上稍加改动，就有了这个公式：
=IF(ISERROR(VLOOKUP($A2,索引库!$A:$E,1,FALSE)),"",VLOOKUP($A2,索引库!$A:$E,3,FALSE))

先判断存在性，再返回对应值

5. 使用 COLUMN 函数实现横向的自动填充

当前公式中 VLOOKUP 函数的第三个参数是一个常量，难以实现横向自动填充的效果。如下图所示：

VLOOKUP 返回值的列标为常量

当 VLOOKUP 返回值的列标为常量时无法横向自动填充序列.png

这里，我们用 COLUMN 函数来实现返回值列标的参数化。由于 “查询库” 中新增了 ”婚姻“ 和 ”是否存在“ 字段，导致 “查询库” 中 “性别” 等字段的列标比 ”索引库“ 中的列标大 2。比如：

因此，应将原公式中的常量 “2” 替换成 'COLUMN() - 2'，于是新的公式为
=IF(ISERROR(VLOOKUP($A2,索引库!$A:$E,1,FALSE)),"",VLOOKUP($A2,索引库!$A:$E,COLUMN()-2,FALSE))

当 VLOOKUP 返回值的列标为参数时完美实现横向自动填充序列.png

成品见上图，外送对联一副：

上联：当馈赠者定倾尽所有
下联：未备案者恕爱莫能助
横批：感谢参数化

三、相关参数

本案主要涉及到 4 个函数，这里重点解析 VLOOKUP 函数。

VLOOKUP 参数解读.png

作者：GritTang
链接：https://www.jianshu.com/p/b732c25f9e84