📌 阶段三 · 函数核心 · 第11讲 VLOOKUP函数 · 约 50 分钟

给入库单、出库单装上「自动填表机」：只输商品编号，名称、单位、售价自动带出

学习目标

  • 记住 VLOOKUP 四个参数各管什么，坚持使用精确匹配
  • 跨工作表把商品档案的信息带进入库单和出库单
  • 会用通配符 * 做「只记得一部分」的查找
  • 认识文本数字与数值数字的匹配坑，会用 &"" 和 *1 转换格式
  • 了解 VLOOKUP 模糊匹配适用的区间分级场景

知识点讲解

VLOOKUP：按编号查档案的自动填表机

VLOOKUP 是 Excel 出镜率最高的函数：在区域的首列里找某个值，找到后返回同一行指定列的内容。

语法：=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])。参数 1 是要查找的值；参数 2 是查找的区域；参数 3 是返回值在区域里的第几列；参数 4 选精确还是近似——写 FALSE（或 0，或留一个逗号占位）是精确匹配，写 TRUE（或 1，或干脆不写）是近似匹配。

记一条铁律：查编号、查名称这类业务查找，无脑写 FALSE 精确匹配。

=VLOOKUP(G6,B$5:E$10,4,FALSE)

讲义示例：在 B5:E10 的首列找考生姓名 G6，返回区域第 4 列（总分）。

=VLOOKUP(C2,商品档案!A:B,2,FALSE)

进销存标准式：在商品档案 A 列（编号）里找 C2 的编号，返回第 2 列（商品名称）——入库单商品名称列就此自动化。

💡 提示:

  • 参数之间用英文逗号分隔
  • 区域不是整列时，下拉拖拽要配合 $ 锁定，防止查找范围跑偏

两条铁律：首列是查找列，重复只认第一条

第一，查找区域必须以「查找值所在的列」作为第一列，返回列只能在其右边。按编号查名称，区域就从编号列开始写（商品档案!A:B）；从名称列开头写就查不到。

第二，如果查找值在区域里有重复，VLOOKUP 只返回从上往下第一条记录对应的值。两个「汪梅」只会查到第一个——所以档案表的编号必须唯一，上一讲的 COUNTIF 查重正好是 VLOOKUP 的地基。

💡 提示:

  • 区域首列必须是查找列，这是 VLOOKUP 的先天限制
  • 档案编号唯一性是所有查找函数的前提

跨表引用：一条公式串起两张表

真正干活时，档案和单据不在同一张工作表。操作很自然：在入库单 D2 输入 =VLOOKUP( 后，用鼠标点工作表标签切到「商品档案」，框选 A:B 两列，Excel 自动写成 商品档案!A:B，再回编辑栏补完逗号和后面的参数即可。

这一步的价值是「数据只维护一份」：商品档案改了单位、改了名称，所有单据的公式重新一算，全部自动跟上，不存在两处不同步的问题。

=VLOOKUP(A2,数据源!A:B,2,FALSE)

讲义示例：按客户 ID 跨表查公司名称——表名加英文叹号就是跨表引用。

=VLOOKUP(C2,商品档案!A:D,4,FALSE)

按编号带出第 4 列单位：跨表选区域时注意首列仍是编号列。

💡 提示:

  • 点选完别的表记得回编辑栏补参数；也可以直接手打「表名!区域」
  • 跨表区域建议锁定或用整列，防止拖拽时跑偏

通配符：只记得半个名字也能查

要查的值和数据源对不上完整匹配时，在查找值后面连接一个 （代表任意数量的任意字符）：=VLOOKUP(A2&"",数据源!B:E,4,FALSE)。

比如单元格里只写了「宏达」，数据源里是「宏达办公用品有限公司」，加个 * 就命中了。讲义例子正是公司名称对不全时按关键字找地址。

=VLOOKUP(A2&"*",数据源!B:E,4,FALSE)

A2&"*" 表示「以 A2 开头、后面随便」的模糊关键字，仍属精确匹配模式，只是条件放宽了尾巴。

💡 提示:

    • 代表任意多个任意字符；能确定唯一关键字时优先用完整精确匹配，通配符是兜底手段

模糊匹配：找小于等于自己的最大值

参数 4 写 TRUE 时是近似匹配，规则是「找小于等于查找值的最大值」，天生适合按区间分级：满 100 件 98 折、满 500 件 95 折这类档位表。

使用前提：区域首列必须从小到大排序，因为模糊匹配用的是二分法。讲义例子是提成比例：档位表升序排好后 =VLOOKUP(G9,C$8:D$13,2,TRUE)，销售额落在哪个档位就取哪档比例。

进销存里批量采购折扣、阶梯运费都是它的舞台。

=VLOOKUP(G9,C$8:D$13,2,TRUE)

在升序的档位首列里找不大于 G9 的最大档，返回同区域第 2 列的比例。

💡 提示:

  • 档位表只写每档的下限，并保持升序
  • 返回错误或档位跳档，先检查有没有排序

文本数字 vs 数值数字：#N/A 的头号元凶

单元格里的 123 可能是数值，也可能是「长得像数字的文本」。格式两边不一致，VLOOKUP 就匹配不上，返回 #N/A。

解法是把两边变成同一种格式：① 查找值是数值、数据源是文本——在查找值后连接空串把它变文本：=VLOOKUP(A1&"",...)（数值只能加减乘除，一旦左右相连，Excel 就把它当文本）；② 查找值是文本、数据源是数值——做一次不改变大小的运算：=VLOOKUP(A11,...) 或 A1+0 或 --A1；③ 一列里两种格式混存——用 IF 加 ISNA 先试一种，报错就换另一种：=IF(ISNA(VLOOKUP(F201,A$18:C$22,3,FALSE)),VLOOKUP(F20&"",A$18:C$22,3,FALSE),VLOOKUP(F20*1,A$18:C$22,3,FALSE))。

讲义也提醒：组合拳只是证明可行性，最好的做法还是把两列格式统一，一劳永逸。

=VLOOKUP(A1&"",...)

数值转文本：连接一个空串，不改内容只改类型。

=VLOOKUP(F12*1,A$10:C$14,3,FALSE)

文本转数值：乘 1（或 +0、--）做一次无损耗运算。

=IF(ISNA(VLOOKUP(F20*1,A$18:C$22,3,FALSE)),VLOOKUP(F20&"",A$18:C$22,3,FALSE),VLOOKUP(F20*1,A$18:C$22,3,FALSE))

格式混合的自适应版：先按数值试查，ISNA 判断出错就改按文本查，否则保持原结果。

💡 提示:

  • 看到 #N/A 先查两件事：两边格式是否一致、编号有没有多敲空格

HLOOKUP：横着查的兄弟

VLOOKUP 面向「一行一条记录」的竖表；如果表格是「一列一条记录」的横表（比如第一行是字段名、第一列是序号的考核表），就轮到 HLOOKUP：语法几乎一样，第 3 参数变成返回第几行，同样注意绝对引用。

讲义示例：=HLOOKUP(B14,$1:$3,3,FALSE) 在第 1 行里找 B14 的值，返回第 3 行的内容。

进销存以竖表为主，HLOOKUP 出场不多，但要认识它。

=HLOOKUP(B14,$1:$3,3,FALSE)

在区域第 1 行（横向）里查找 B14，返回第 3 行对应列的值，精确匹配。

💡 提示:

  • V 竖 H 横：VLOOKUP 竖着找行，HLOOKUP 横着找列
  • 第 4 参数同样写 FALSE 精确匹配

实战演练

这一讲给单据表装「自动填表机」。入库单目前还靠手工抄商品名称和单位，费时又容易抄错；我们让 D 列（商品名称）、E 列（单位）全部公式化——录入时只碰商品编号，其余自动带出。出库单更进一步：单价自动带售价、客户名称自动带公司名。从此单据录入只保留「日期、编号、数量」这些真正需要人决策的字段。可先在右侧练习数据里试手感，再回到进销存工作簿正式建造。

  1. 入库单：自动带出商品名称 — 入库单 D2 输入 =VLOOKUP(C2,商品档案!A:B,2,FALSE)，下拉到全部流水行。注意区域首列必须是编号列；故意从 B 列开头选一次区域，观察报错，再改正——体会在犯错中记住「首列铁律」。
  2. 自动带出单位 — 入库单 E2 输入 =VLOOKUP(C2,商品档案!A:D,4,FALSE)，下拉。同一个编号、同一个区域，只把返回列从 2 改成 4，名称和单位就都齐了。
  3. 给公式穿上 IFERROR 防弹衣 — 把某行编号临时改成档案里不存在的 XX999，D 列出现 #N/A。用第 8 讲的 IFERROR 包住：D2 改为 =IFERROR(VLOOKUP(C2,商品档案!A:B,2,FALSE),"编号有误")，E 列同样处理，再把编号改回正确的。
  4. 出库单：带出售价和客户名称 — 出库单 D、E 列照入库单处理；H2 输入 =VLOOKUP(C2,商品档案!A:F,6,FALSE)——出库走售价，返回第 6 列；J1 写表头「客户名称」，J2 输入 =VLOOKUP(F2,客户档案!A:B,2,FALSE) 下拉，客户一编号公司名就出来。
  5. 通配符练手 — 在空白格试 =VLOOKUP("得力*",商品档案!B:C,1,FALSE)——只记得品牌名也能按半个名称命中档案里的全称；再换成确定完整的名称对比两种写法。
  6. 全面检查：不留一个 #N/A — 把 D、E（出库单还有 H、J）列公式下拉覆盖所有流水行，用 Ctrl+F 搜索「#N/A」逐一排查：编号不存在就去档案补录，编号是文本数字与档案数值不一致就按 &"" 或 *1 处理，确保全表干净。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ 入库单 D、E 列全部公式化，改一个编号后名称单位立刻自动更新
  • ☐ 出库单 H 列带出的是售价而不是进价（第 6 列没选错）
  • ☐ IFERROR 上线后，错误编号显示「编号有误」而不是 #N/A
  • ☐ 能说出 #N/A 的两大成因：编号不存在、文本数字与数值数字格式不一致
  • ☐ 商品档案编号经第 9 讲查重确认无重复（VLOOKUP 重复只认第一条）