VLOOKUP 与 HLOOKUP：老牌查找

一、VLOOKUP 的四个参数

=VLOOKUP(查找值,表格区域,列序号,[匹配方式]) 是 Excel 里最著名的函数。四个参数的含义分别是：要找什么、在哪张表里找、找到后返回这张表的第几列、以及是精确匹配还是近似匹配。写成 =VLOOKUP(A2,参数表!A:C,3,0) 就是最标准的用法。

第一个参数查找值，可以是单元格引用，也可以是常量或文本。写 =VLOOKUP("市场部",参数表!A:C,3,0) 与引用单元格效果相同，但把查找值放在单元格里更灵活，向下填充就能批量查询。

第二个参数表格区域，查找值必须位于这个区域的第一列，这是 VLOOKUP 最硬的约束，也是它最大的局限。区域可以是同一张工作表里的一块，也可以是别的工作表，甚至别的工作簿。跨工作表时写成 参数表!$A$2:$C$100，加上美元符号锁定区域，向下填充时才不会跑偏。

第三个参数列序号，是相对于表格区域而言的序号，不是工作表的列号。区域写成 B:D 时，第 1 列是 B 列，第 2 列是 C 列。写成整列引用 =VLOOKUP(A2,参数表!A:C,3,0) 时，序号与工作表列号一致，这也是很多人习惯用整列引用的原因之一。

第四个参数匹配方式，写 0 或 FALSE 表示精确匹配，写 1 或 TRUE 表示近似匹配。日常使用一律写 0，因为近似匹配要求查找列已按升序排序，否则结果不可预期。省略这个参数时默认是近似匹配，这是 VLOOKUP 最坑的设计，无数错误都源于此。

还有一个新手常问的问题：查找区域能不能包含合并单元格。答案是强烈不建议。合并单元格只有左上角存值，其他格为空，VLOOKUP 在遍历时会遇到大量空白，虽然通常仍能返回正确结果，但一旦排序或插入行，结构就会错乱。参数表请一律保持一维清单结构，也就是第一行是标题、下面每行一条记录，不要用合并单元格做视觉分组。

区域大小也影响性能。写成 =VLOOKUP(A2,参数表!$A$2:$C$1000,3,0) 比整列引用更快，但维护成本高。折中方案是给区域留足余量，例如实际只有 200 行却写到 2000 行，既不影响性能太多，又能覆盖未来一两年的增长。

二、精确匹配与近似匹配

精确匹配就是找到完全相同的查找值，找不到就返回 #N/A。写成 =VLOOKUP(A2,参数表!A:C,3,0)，如果 A2 的值在参数表 A 列里不存在，结果就是错误值。这个行为其实很好，它明确告诉你没找到，而不是给你一个看似合理实则错误的数。

近似匹配则会在找不到完全相同的值时，返回小于等于查找值的最大那个。它的典型用途是区间查找。比如成绩分级表第一列是 0、60、80、90，第二列是不及格、及格、良好、优秀，写 =VLOOKUP(75,分级表,2,1) 会返回及格，因为 75 落在 60 到 80 之间。

使用近似匹配有一个硬性前提：查找列必须按升序排列。不排序的话结果完全不可靠，而且不报错。这也是为什么建议日常一律用精确匹配，只在做区间分级时才用近似匹配，并且用过之后给查找列排一次序确认。

还有一点容易忽略：近似匹配在查找值小于最小值时返回 #N/A，因为不存在小于等于它的项。分级表的第一列必须从 0 或负无穷开始，覆盖所有可能的输入，否则低分段的记录会全部报错。

三、它做不到的事

第一件做不到的事是向左查找。VLOOKUP 只能返回查找列右侧的列，如果你的表格里查找列在右、结果列在左，它无能为力。解决办法有三：调整列顺序；用 INDEX 加 MATCH 组合；或者用 CHOOSE 构造一个虚拟区域。前两种是常规做法，第三种只适合临时应急。

第二件做不到的事是多条件查找。VLOOKUP 只能按一个值查找，要按部门和日期两个字段查，必须构造辅助列。做法是在数据源最左侧插入一列，写 =B2&"|"&TEXT(C2,"yyyy-mm-dd") 把两个字段拼成一个键，查找时同样拼接后查询。辅助列加拼接键是经典套路，兼容性极好。

第三件做不到的事是处理重复值。查找列有重复时，VLOOKUP 只返回第一个匹配到的结果，后面的全部忽略，而且不给出任何提示。做主数据匹配前，先用 =COUNTIF(参数表!A:A,A2) 检查是否有重复，重复值大于 1 就要先处理数据源。

第四件做不到的事是插入列后不失效。因为列序号是写死的数字，在查找区域中间插入一列后，原本的第 3 列可能变成第 4 列，公式却还写着 3，于是返回错误的字段。这是 VLOOKUP 最典型的维护事故，也是很多人转向 INDEX 加 MATCH 的直接原因。

第五，它对数据类型敏感。查找值是数字而查找列是文本时，即使看起来一样也匹配不上。判断方法是 =ISNUMBER(A2) 与 =ISNUMBER(参数表!A2) 对比，类型不一致就先统一。数字转文本用 =A2&""，文本转数字用 =A2*1 或 VALUE。

四、HLOOKUP：横向表的查找

=HLOOKUP(查找值,表格区域,行序号,[匹配方式]) 是 VLOOKUP 的横向版本，参数结构完全对称，只是把列序号换成行序号。它在查找值位于区域第一行、结果在下方某一行时使用。

横向表在实际工作中不算少见，尤其是别人做的报表。典型结构是第一行为月份或指标名，第一列为项目名。这时要按项目名查某个指标，用 VLOOKUP 反而更顺；要按月份查，用 HLOOKUP 更顺。选哪个取决于查找值在哪一行或哪一列。

HLOOKUP 的用法与限制和 VLOOKUP 完全一致：查找值必须在区域第一行、行序号相对于区域、第四参数同样建议写 0。它在插入行时同样会失效，维护风险也一样。

横向表还有一个更现代的替代写法：用 INDEX 配合 MATCH 两次，一次匹配行、一次匹配列，写成 =INDEX(数据区,MATCH(项目名,A2:A50,0),MATCH(月份,B1:M1,0))。这种二维查找在交叉表里极为常用，比 HLOOKUP 灵活得多。具体写法见 INDEX 加 MATCH：更稳的查找组合。

五、真实场景与排错清单

场景一：按工号补姓名与部门。花名册放在参数表，A 列工号、B 列姓名、C 列部门。在明细表里写 =VLOOKUP($A2,参数表!$A:$C,2,0) 取姓名，=VLOOKUP($A2,参数表!$A:$C,3,0) 取部门。两个公式只有第三参数不同，向下填充即可批量补全。

场景二：按销售额查提成比例。分级表第一列是档位下限 0、100000、300000、500000，第二列是比例 3%、5%、8%、10%。写成 =VLOOKUP(C2,费率表!$A$2:$B$5,2,1)，用近似匹配自动落到对应档位。记得确认第一列已按升序排好。

场景三：跨表核对两列数据是否一致。写成 =IF(ISNA(VLOOKUP(A2,对照表!A:A,1,0)),"缺失","存在")，用 ISNA 判断是否为查找不到，配合筛选就能找出缺失项。也可以直接写 =IFERROR(VLOOKUP(A2,对照表!B:B,1,0),"缺失")，更短。

场景四：多条件查找用辅助键。数据源最左插入列写 =B2&"|"&C2，把部门与日期拼成键；查询时写 =VLOOKUP(F2&"|"&G2,数据源!$A:$D,4,0)，同样拼接后查找。分隔符选一个数据里不会出现的字符，竖线或下划线都可以，避免出现歧义。

场景四的补充：拼接键时最好统一格式，尤其是日期和数字。日期要写成 =TEXT(C2,"yyyy-mm-dd") 而不是直接引用 C2，否则拼接出来的可能是序列号，两边对不上。数字同理，位数或小数不一致也要先统一。拼接键的格式化规则一旦定下来，源数据和查询公式两边必须严格遵守，否则匹配必然失败。

场景五：查不到时的兜底。写成 =IFERROR(VLOOKUP(A2,参数表!$A:$C,3,0),"未找到")，把错误值换成友好提示。更进一步写成 =IFERROR(VLOOKUP(...),0)，让结果可以直接参与求和。兜底写法的完整讨论见 XLOOKUP 与 IFERROR：新写法和兜底。

排错清单值得单独列出来，因为查找类错误的原因高度集中。第一，检查第四参数是不是写了 0；第二，检查查找列是不是区域的第一列；第三，检查数据类型是否一致；第四，检查有没有多余空格，用 =TRIM() 处理；第五，检查区域引用有没有加美元符号；第六，检查列序号是否仍然正确。按顺序过一遍这六条，绝大多数问题都能解决。

六、让查找更稳的几个做法

第一个做法是区域用整列或智能表。写成 =VLOOKUP(A2,参数表!$A:$C,3,0) 后，参数表新增行会自动纳入查找范围，不必回头改公式。更规范的做法是把参数表转成智能表，用结构化引用写成 =VLOOKUP(A2,表1,3,0)，列数变化时公式依然成立，可读性也更好。

第二个做法是把第三参数改成 MATCH 动态定位。写成 =VLOOKUP(A2,参数表!$A:$D,MATCH("部门",参数表!$1:$1,0),0)，先用 MATCH 在第一行的标题里找到部门所在的列号，再交给 VLOOKUP。这样在参数表中间插入列也不会取错字段，是低成本提升稳定性的好办法。

第三个做法是给参数表加唯一性校验。在参数表旁边加一列写 =IF(COUNTIF($A:$A,A2)>1,"重复","")，每次更新参数表后扫一眼。主数据重复是查找类公式最隐蔽的错误来源，因为它不报错，只是悄悄给出错误的那条记录。

第四个做法是用命名区域替代裸引用。选中参数表区域，在名称框里给它起个名字比如价格表，公式写成 =VLOOKUP(A2,价格表,3,0)。命名区域自动带绝对引用，不用记美元符号，公式也好读。改区域范围时只需改一次定义，所有引用处同步生效。

常见问题

为什么 VLOOKUP 总是返回错误值

按顺序排查六项。第一，第四参数写成 0 了吗，省略时默认是近似匹配，最容易出错。第二，查找值所在列是不是区域的第一列，不是就永远找不到。第三，查找值与查找列的数据类型是否一致，数字与文本互不匹配。第四，单元格里有没有多余空格或不可见字符，用 =LEN() 对比长度。第五，区域引用有没有被填充柄带偏，补上美元符号。第六，列序号有没有超出区域的总列数，超出会返回 #REF!。

能不能用通配符查找

可以，但只在精确匹配模式下讨论才有意义。写 =VLOOKUP("北京*",客户表!$A:$C,3,0) 会返回第一个以北京开头的客户对应的第三列。通配符只能用在查找值里，不能用在查找区域。VLOOKUP 对通配符的支持不如 SUMIF 稳定，实际中用得不多，需要模糊匹配时更常见的做法是先筛选出候选，再精确查找。

查找值是数字，查找列也是数字，为什么还是找不到

多半有一方是文本型数字。用 =ISNUMBER(A2) 分别检查两边，FALSE 的那一方需要转换。批量转换的快捷做法：选中文本型数字所在列，点击数据选项卡里的分列，一路点下一步直到完成，Excel 会自动把它们转成数值。也可以新增辅助列写 =A2*1 再粘贴回原列。

近似匹配的结果为什么总是差一档

因为查找列没有按升序排序。近似匹配依赖二分查找，数据乱序时它会在错误的位置停下来。解决方法是选中查找列升序排序一次，或者干脆改用精确匹配。另外要注意，查找值小于查找列的最小值时同样会返回错误值，分级表的第一档务必从 0 开始。

查找区域里第一列不是我要查的字段怎么办

三种解法。最省事的是在参数表最左侧插入一列，把查找字段复制过去，VLOOKUP 就能用了。最规范的是改用 INDEX 加 MATCH，彻底摆脱方向限制。最取巧的是用 CHOOSE 构造虚拟区域，写成 =VLOOKUP(A2,CHOOSE({1,2},C2:C100,B2:B100),2,0)，把两列临时拼成一个查找列在前、结果列在后的区域。临时查一次用第三种，长期维护用第二种。

能不能一次返回多列

传统写法不行，一个 VLOOKUP 只返回一个值，要取三列就写三次，把第三参数分别写成 2、3、4。Excel 365 与新版 WPS 支持动态数组，可以写成 =VLOOKUP(A2,参数表!A:D,{2,3,4},0)，第三参数用数组常量，一次返回三个值并自动溢出到右侧单元格。老版本里老老实实写三遍，或者用 INDEX 加 MATCH 配合 COLUMN 函数做横向填充。

WPS 里 VLOOKUP 有差异吗

没有实质差异，四个参数的含义与行为完全一致。需要注意的是 WPS 早期版本对第三参数使用数组常量的支持不稳定，返回多列时可能只得到第一个值，此时改回逐个写或者用 INDEX 加 MATCH。另外跨工作簿引用在 WPS 与 Excel 之间传递时，路径格式可能变化，建议把参数表放在同一工作簿内。

VLOOKUP 和 XLOOKUP 该学哪个

如果只看自己的电脑，XLOOKUP 更好用，语法更直观，也不受方向限制。但考虑到文件要发给别人、要兼容老版本，VLOOKUP 仍然必须会，因为它是存量表格里最常见的写法。建议两者都掌握：读别人的表用 VLOOKUP 的知识，写自己的表可以优先用 XLOOKUP。两者的对比在 XLOOKUP 与 IFERROR：新写法和兜底 里有详细说明。