VLoopUp 函数
查找李四对应工号
正向查找 根据左边的值 查找右边的值
=VLOOKUP("李四", A2:C4, 2, FALSE)
| A | B | C |
|---|---|---|
| 姓名 | 工号 | 部门 |
| 张三 | 001 | 财务部 |
| 李四 | 002 | 技术部 |
| 王五 | 003 | 市场部 |
参数说明
| 参数值 | 说明 |
|---|---|
"李四" | 要查找的值（目标姓名） |
A2:C4 | 数据区域（查找的表格范围） |
2 | 返回区域中第 2 列的值（即工号） |
FALSE | 精确匹配（必须完全匹配“李 四”） |
VLOOKUP 只能在你指定的数据区域的第一列中查找数据，不能往左查，不能跳过这一列。
根据区间 计算提成比例
| 区间 | 销售额 | 提成比例 |
|---|---|---|
| 1000-1999 | 1000 | 5% |
| 2000-2999 | 2000 | 10% |
| 3000-4999 | 3000 | 20% |
| 5000-9999 | 5000 | 30% |
| 10000 及以上 | 10000 | 35% |
| 姓名 | 销售额 | 提成比例（公式） |
|---|---|---|
| A | 1500 | =VLOOKUP(G2, $B$2:$C$6, 2, TRUE) |
| B | 2800 | =VLOOKUP(G3, $B$2:$C$6, 2, TRUE) |
| C | 2300 | =VLOOKUP(G4, $B$2:$C$6, 2, TRUE) |
| D | 1900 | =VLOOKUP(G5, $B$2:$C$6, 2, TRUE) |
| E | 3900 | =VLOOKUP(G6, $B$2:$C$6, 2, TRUE) |
| F | 5600 | =VLOOKUP(G7, $B$2:$C$6, 2, TRUE) |
| G | 12500 | =VLOOKUP(G8, $B$2:$C$6, 2, TRUE) |
参数说明
| 部分 | 意义 |
|---|---|
G2 | 当前员工的销售额（查找值） |
$B$2:$C$6 | 提成对照表（第一列是区间起点/销售额，第二列是提成比例） |
2 | 表示返回第 2 列，也就是“提成比例” |
TRUE | 近似匹配，查找“区间” |
反向查找 根据右边的值 查找左侧列的值
| 产品 | 地址 |
|---|---|
| 笔记本 | 1 号仓库 |
| 手机 | 2 号仓库 |
| 平板 | 3 号仓库 |
| 充电线 | 4 号仓库 |
| 数据线 | 5 号仓库 |
| 产品 | 地址 （公式） |
|---|---|
| 笔记本 | =VLOOKUP(F17, $A$17:$B$21, 2, FALSE) |
| 手机 | =VLOOKUP(F18, $A$17:$B$21, 2, FALSE) |
| 平板 | =VLOOKUP(F19, $A$17:$B$21, 2, FALSE) |
| 充电线 | =VLOOKUP(F20, $A$17:$B$21, 2, FALSE) |
| 数据线 | =VLOOKUP(F21, $A$17:$B$21, 2, FALSE) |
多条件查找 （辅助列）
| 辅助列 | 水果 | 产地 | 数量 | 市场价 |
|---|---|---|---|---|
=B25&C25 | 苹果 | 山东 | 120 | 7.1 元/斤 |
=B26&C26 | 苹果 | 陕西 | 80 | 6.2 元/斤 |
=B27&C27 | 香蕉 | 云南 | 150 | 8.5 元/斤 |
=B28&C28 | 香蕉 | 海南 | 100 | 5.3 元/斤 |
=B29&C29 | 橙子 | 福建 | 90 | 6.5 元/斤 |
=B30&C30 | 橙子 | 广西 | 70 | 9.5 元/斤 |
| 水果 | 产地 | 市场价 |
|---|---|---|
| 香蕉 | 云南 | =VLOOKUP(H25&I25, $A$25:$E$30, 5, FALSE) |
| 香蕉 | 海南 | =VLOOKUP(H26&I26, $A$25:$E$30, 5, FALSE) |
| 苹果 | 陕西 | =VLOOKUP(H27&I27, $A$25:$E$30, 5, FALSE) |
| 苹果 | 山东 | =VLOOKUP(H28&I28, $A$25:$E$30, 5, FALSE) |
| 橙子 | 福建 | =VLOOKUP(H29&I29, $A$25:$E$30, 5, FALSE) |
| 橙子 | 广西 | =VLOOKUP(H30&I30, $A$25:$E$30, 5, FALSE) |
屏蔽错误值 （没有记录的值 来查找当前区域）
| 产品型号 | 质检得分 | 质检等级 |
|---|---|---|
| A100 | 95 | 优 |
| A101 | 82 | 良 |
| A102 | 67 | 合格 |
| A103 | 45 | 不合格 |
| B200 | 88 | 良 |
| B201 | 73 | 合格 |
| B202 | 50 | 不合格 |
| 产品型号 | 质检等级（公式） |
|---|---|
| A100 | =IFERROR(VLOOKUP(H37,$A$36:$C$43,3,FALSE),"无效型号") |
| A101 | =IFERROR(VLOOKUP(H38,$A$36:$C$43,3,FALSE),"无效型号") |
| A106 | =IFERROR(VLOOKUP(H39,$A$36:$C$43,3,FALSE),"无效型号") |
| A103 | =IFERROR(VLOOKUP(H40,$A$36:$C$43,3,FALSE),"无效型号") |
| B20011 | =IFERROR(VLOOKUP(H41,$A$36:$C$43,3,FALSE),"无效型号") |
| B202 | =IFERROR(VLOOKUP(H42,$A$36:$C$43,3,FALSE),"无效型号") |
| B201 | =IFERROR(VLOOKUP(H43,$A$36:$C$43,3,FALSE),"无效型号") |
关键字查找 （通配符）
| 合作客户 | 签订日期 | 合同金额(万元) |
|---|---|---|
| 北京科技有限公司 | 2024-01-15 | 120 |
| 上海创新集团 | 2024-02-20 | 300 |
| 广州未来实业 | 2024-03-10 | 450 |
| 深圳智联科技 | 2024-04-05 | 280 |
| 成都绿色能源 | 2024-04-28 | 150 |
| 合作客户 | 合同金额(万元) |
|---|---|
| 北京科技 | =VLOOKUP("*"&H50&"*",$A$50:$C$54,3,FALSE) |
| 广州未来 | =VLOOKUP("*"&H51&"*",$A$50:$C$54,3,FALSE) |
| 智联 | =VLOOKUP("*"&H52&"*",$A$50:$C$54,3,FALSE) |
| 上海创新 | =VLOOKUP("*"&H53&"*",$A$50:$C$54,3,FALSE) |
| 成都绿色 | =VLOOKUP("*"&H54&"*",$A$50:$C$54,3,FALSE) |
去空格查询
| 姓名 | 部门 | 工资 |
|---|---|---|
| 张三 | 财务部 | 8000 |
| 赵六 | 销售部 | 6800 |
| 陈七 | 研发部 | 10000 |
| 刘德华 | 运营部 | 12000 |
| 司马懿 | 战略部 | 11000 |
| 欧阳娜娜 | 公关部 | 9000 |
| 许飞 | 客服部 | 7500 |
| 林志玲 | 设计部 | 9800 |
| 姓名 | 工资（公式） |
|---|---|
| 刘德华 | =VLOOKUP(SUBSTITUTE(H62," ",""),$A$62:$C$69,3,FALSE) |
| 欧阳娜娜 | =VLOOKUP(SUBSTITUTE(H63," ",""),$A$62:$C$69,3,FALSE) |
| 陈 七 | =VLOOKUP(SUBSTITUTE(H64," ",""),$A$62:$C$69,3,FALSE) |
| 张 三 | =VLOOKUP(SUBSTITUTE(H65," ",""),$A$62:$C$69,3,FALSE) |
| 司马懿 | =VLOOKUP(SUBSTITUTE(H66," ",""),$A$62:$C$69,3,FALSE) |
| 赵 六 | =VLOOKUP(SUBSTITUTE(H67," ",""),$A$62:$C$69,3,FALSE) |
| 林 志玲 | =VLOOKUP(SUBSTITUTE(H68," ",""),$A$62:$C$69,3,FALSE) |
| 许 飞 | =VLOOKUP(SUBSTITUTE(H69," ",""),$A$62:$C$69,3,FALSE) |
多列批量查找
| 姓名 | 部门 | 工号 | 工龄（年） |
|---|---|---|---|
| 张三 | 财务部 | 1001 | 5 |
| 李四 | 技术部 | 1002 | 3 |
| 王五 | 市场部 | 1003 | 7 |
| 赵六 | 销售部 | 1004 | 2 |
| 陈七 | 研发部 | 1005 | 4 |
先将部门公式横向拉动 在竖向拉动填充
| 姓名 | 部门(公式) | 工号(公式) | 工龄（年）(公式) |
|---|---|---|---|
| 张三 | =VLOOKUP($H76,$A$76:$D$80,COLUMN(B1),FALSE) | ||
| 李四 | 技术部 | ||
| 王五 | 市场部 | ||
| 赵六 | 销售部 | ||
| 陈七 | 研发部 |
多列动态查找 列不对应的情况
| 姓名 | 部门 | 工号 | 工龄（年） |
|---|---|---|---|
| 张三 | 财务部 | 1001 | 5 |
| 李四 | 技术部 | 1002 | 3 |
| 王五 | 市场部 | 1003 | 7 |
| 赵六 | 销售部 | 1004 | 2 |
| 陈七 | 研发部 | 1005 | 4 |
先将工号公式横向拉动 在竖向拉动填充
| 姓名 | 工号 | 工龄（年） | 部门 |
|---|---|---|---|
| 张三 | =VLOOKUP($H87,$A$86:$D$91,MATCH(I$86,$A$86:$D$86),FALSE) | ||
| 李四 | |||
| 王五 | |||
| 赵六 | |||
| 陈七 |
引用快捷键 F4
| 操作 | 单元格引用变化 | 说明 |
|---|---|---|
第一次按 F4 | $A$1 | 绝对引用：列和行都锁定 |
第二次按 F4 | A$1 | 锁定行，列为相对引用 |
第三次按 F4 | $A1 | 锁定列，行为相对引用 |
第四次按 F4 | A1 | 完全相对引用（默认） |