你是不是也遇到过这些崩溃瞬间？
- 数据表多到眼花，每次查数据要切10个表格；
- 合并单元格导致公式疯狂报错，加班到深夜；
- 领导临时要报表，你只能手动复制粘贴，结果漏洞百出……
今天教你用 VLOOKUP IF 与VLOOKUP OFFSET组合公式，动态引用区域、跨表精准查找，从此告别重复劳动！
场景1：多区域切换查找（IF+VLOOKUP）
场景描述：不同业务部的数据分散在不同区域，需根据名称自动切换查找范围。
输入公式：
=VLOOKUP($O$2, IF($O$1="销售一部", $A$2:$C$9, IF($O$1="销售二部", $D$2:$F$9, IF($O$1="销售三部", $G$2:$I$9, $J$2:$L$9))), 2, 0)
1、vlookup第1个参数查找值：$O$2 是要查找的值，它会在选定的查找范围的第一列中寻找。
2、VLOOKUP第2个参数：我们要用IF函数根据条件返回不同的区域。
IF($O$1="销售一部", $A$2:$C$9, IF($O$1="销售二部", $D$2:$F$9, IF($O$1="销售三部", $G$2:$I$9, $J$2:$L$9))
$O$1 是一个条件单元格，用于决定使用哪个查找范围：
（1）如果 $O$1="销售一部"，则查找范围是 $A$2:$C$9。
（2）如果 $O$1="销售二部"，则查找范围是 $D$2:$F$9。
（3）如果 $O$1="销售三部"，则查找范围是 $G$2:$I$9。
（4）如果都不满足，则查找范围是 $J$2:$L$9。
3、返回值：查找成功后，返回选定查找范围的第 2 列的值。
4、精确匹配：0
公式向下拉，把VLOOKUP第3参数2改成3就可以了，返回区域内的第3行。
场景2：动态区域查找（OFFSET+VLOOKUP）
场景描述：表格每月新增数据行，VLOOKUP的查找区域需要自动扩展。
具体公式：
=VLOOKUP($G$2,OFFSET($B$1,MATCH($G$1,A:A,0),0,COUNTIF(A:A,$G$1),3),2,0)
第一个参数：lookup_value（查找值）
$G$2这是你要查找的值。
2. 第二个参数：table_array（查找范围）
OFFSET($B$1, MATCH($G$1, A:A, 0), 0, COUNTIF(A:A, $G$1), 3)
OFFSET可以根据指定的起点、行偏移量、列偏移量、高度和宽度，生成一个动态的单元格区域。
（1）起点：$B$1这是动态区域的起始点。
（2）行偏移量：MATCH($G$1, A:A, 0)MATCH函数在列A中查找$G$1的值，并返回该值所在的行号（相对于A:A的起始位置）。
（3）列偏移量：0列偏移量为0，表示从$B$1开始，不改变列。
（4）高度：COUNTIF(A:A, $G$1)COUNTIF函数统计列A中等于$G$1的值的个数。
（5）宽度：3动态区域的宽度是3列。
OFFSET生成了一个动态区域，从$B$1开始，偏移到匹配$G$1的行，高度为COUNTIF的结果，宽度为3列。
3. 第三个参数：col_index_num（返回列的索引）：2
4. 第四个参数：[range_lookup]（查找方式）：0
总结
VLOOKUP的高阶玩法离不开动态区域和多函数联动！
掌握OFFSET动态引用、IF区域切换、再复杂的表格也能轻松拿捏！
最后提醒：避免合并单元格、绝对引用关键区域、多用名称管理器简化公式！
评论 (0)