Excel中，多层区间查询时非常普遍的应用，遇到此类问题，大多同学都是一一对照手动填充的，其实，我们也可以使用Excel函数公式来自动完成查询填充功能。

一、问题描述。

如下图：

我们要从右侧的消费规则中查询消费人员对应的会员等级，该如何去实现了？

二、解决办法。

1、IF函数嵌套法。

方法：

在目标单元格中输入公式：=IF(C3>=50000,"至尊卡",IF(C3>=30000,"金卡",IF(C3>=15000,"银卡",IF(C3>=8000,"五级",IF(C3>=5000,"四级",IF(C3>=3000,"三级",IF(C3>=2000,"二级",IF(C3>=1000,"一级","无等级"))))))))。

释义：

1、此方法利用IF函数嵌套的方式实现，如果会员等级复杂，嵌套的次数就非常的繁多，并且容易出错。

2、书写公式时必须从“高等级”依次向“低等级”书写，不能跳跃，否则就会出现错误。

2、VLOOKUP函数模糊查询法。

方法：

在目标单元格中输入公式：=IFERROR(VLOOKUP(C3,$H$3:$I$10,2),"无等级")。

释义：

1、IFERROR函数为辅助函数，如果数值在表格中查询不到对应的数据，则返回“无等级”。暨消费金额小于1000元时为“无等级”。

2、如果消费金额大于1000原始，执行VLOOKUP(C3,$H$3:$I$10,2)查询，我们不难发现，VLOOKUP函数得第四个参数被省略，此时为模糊查询，暨在第二个参数中找不到第一个参数时，返回比查询值小但最接近查询值的对应值。例如：查询2100时，返回的是2000所对应的值。

3、LOOKUP函数查询法。

方法：

在目标单元格中输入公式：=IFERROR(LOOKUP(C3,$H$3:$I$10),"无等级")。

释义：

1、IFERROR函数为辅助函数，当查询不到对应的值时返回“无等级”。暨消费金额小于1000元时，返回“无等级”。

2、当消费金额大于等于1000元时，执行LOOKUP(C3,$H$3:$I$10)，查询，如果查询不到对应的值，则返回小于且最接近于查询值的对应值。例如：查询2100时，返回的是2000所对应的值。

提起查找函数，大家第一时间想到的肯定是Vlookup，其实大多数人不知道，Lookup才是查找函数之王，它几乎能高效地实现Vlookup函数的所有功能，部分功能是Vlookup函数无法比拟的。

一、语法结构和基本使用方法。

应用场景：当需要查询一行或一列并查找另一行或列中的相同位置的值时。

语法结构：

LOOKUP(lookup_value, lookup_vector, [r]result_vecto)

1、Lookup_value：必需。在向量中搜索的值。

2、Lookup_Vector:必需。只包含一行或一列的区域。此区域中的值必需按照升序排列，否则无法返回正确的结果。文本不区分大小写。

3、result_vector ：可选。只包含一行或一列的区域。result_vector 参数必须与 lookup_vector 参数大小相同。其大小必须相同。

易解语法结构：Lookup(查找的值，查找值所在的范围，返回值所在的范围）。

使用形式：

1、向量形式

可使用Lookup的这种形式在一行或一列中搜索值。

方法：

在目标单元格中输入公式：=LOOKUP(H3,A3:A9,C3:C9)。

2、数组形式。

数组是要搜索的行和列中的值的集合。要使用数组，必需对数据排序。其功能一般用Vlookup函数和Hlookup函数来替代，不建议用哪个数组形式。

方法：

在目标单元格中输入公式：=VLOOKUP(H3,B3:C9,2,0)。

二、Lookup函数实现逆向查找功能。

方法：

1、对数据进行升序排序。

2、在目标单元格中输入公式：=LOOKUP(H3,C3:C9,B3:B9)。

3、Ctrl+Enter填充。

备注：

逆向查找之前，首先要对超找的内容进行升序排序，之后进行查找工作。

三、Lookup函数万能查找（单条件、多条件）。

在前面的学习中我们已经知道，Lookup函数想要实现正确的查找，首先要对查找值所在的范围（Lookup函数的第二个参数）进行升序排序。如果不想排序怎么办了？

1、单条件：

方法：

在目标单元格中输入公式：=LOOKUP(1,0/(B3:B9=H3),C3:C9)。

公式解析：

我们先来看，B3:B9=H3，也就是说判断B3:B9中的值是否等于H3,因此判断结果是{0,1,0,0,0,0,0}，因为之后第二个值等于H3中的值。{0,1,0,0,0,0,0}作为分母，被0除，得出的记过就是{错误值,0,错误值,错误值,错误值,错误值,错误值}。在这个数组中进行查找，会查找不到，那么将会匹配比1小的最大值，也就是0，所以就查找到了H3对应值的位置。

2、多条件：

方法：

在目标单元格中输入公式：=LOOKUP(1,0/((B3:B9=H3)*(E3:E9=I3)),C3:C9)。

备注：

1、此公式是Lookup函数最经典、最万能的公式。可以归纳为：

=Lookup(1,0/((条件1）*（条件2）……条件N），返回值的范围）。

2、从上述的万能公式中我们可以看出，Lookup不仅可以但条件查找，也可以多条件查找。

四、Lookup函数多层次区间条件查找。

方法：

在目标单元格中输入公式：=LOOKUP(C3,$I$3:$J$6)。

在Excel的查找里面，如果涉及到更严格的条件，也可以进行设置。点击窗口里的“选项”按钮，就会出来更具体的筛选条件。

格式

当需要查找含有特定格式的内容的时候，就需要在这里进行设置了。“未设定格式”这里显示的其实是格式的预览，如果没有设置的话，就显示这个文案提醒。

点击“格式”按钮，出来的是“查找格式”窗口，在这个窗口可以进行手动设置特定格式，然后点击“确定”，进行查找。

如果遇到具体颜色或其他格式比较接近，难以确定，可以点击“从单元格选择格式”这个按钮，找到其中一个目标单元格，直接选中就行了。

范围

分为“工作表”和“工作簿”。如果需要在整个工作簿里面查找，就要选择“工作簿”这个选项。

搜索

分为“按行”和“按列”。“按行”是指查找的时候，先在一行里面查找，然后换到下一行。“按列”则是在一列里面查找，查完以后到下一列继续。

查找范围

分为“公式”，“值”和“批注”。

“批注”的意思就是要查找的内容在批注里面。需要的时候就在这里选择“批注”，得到的结果是这个批注所在的单元格。

“值”和“公式”这两个选项会比较难理解。打开“查找和替换”的时候，这个选项默认是“公式”。下面来举例说明一下两者的不同之处。

如图，A1为普通数字，B1：E1单元格内为公式计算的结果，公式详情展示在下一行。查找的范围为A1：E1，并不包括展示公式的单元格。

选择“公式”

查找内容输入“90”，点击“查找全部”。得到的结果是单元格A1和C1。

A1单元格没有问题。

C1单元格，因为公式里面包含了“90”，因此也会被查找到。

单元格D1和E1，虽然公式里面包含了A1，并且A1单元格的值是90，但是因为公式里面不是直接显示90，因而没有被查找到。

单元格B1，得到的90因为是公式计算出来的结果，也没有被查找到。

因此，查找范围为“公式”，单元格的值为非公式计算的结果会被查找到；如果是公式的话，会查找公式本身直接显示的内容，而非公式返回的结果。

选择“值”

选择“数值”，查找数字“90”，得到的结果是A1和B1单元格。

A1单元格也没有问题。

B1单元格，公式返回的结果等于“90”，因此也被查找到了。

C1因为公式返回的值不符，因此没有被查找到。

因此，查找范围为“值”，单元格不是公式，值符合还是会被查找到；如果公式返回的结果与查找内容一致，就会被查找到。这时，Excel不会去查找这个单元格是否有公式。

以上就是查找范围选择“值”和“公式”的区别，在实际应用中要牢记这一点，避免结果引起困惑。

区分大小写

这个选项如果不勾选，就不会进行区分。勾选上了，就会严格按照输入的查找内容的大小写来进行查找。

例如，勾选上以后，输入“aPPle”，仅大小写完全符合的单元格会被查找到。

单元格匹配

不勾选的时候，只要单元格包含输入的查找内容，就会返回结果。如果勾选上，就会查找与输入内容完全匹配，没有其他多余内容的单元格。

如图，勾选上以后，输入了“app”，是这些单元格里都有的，但是因为没有完全匹配，因此只会出现弹窗，提醒无法查到内容。这个功能在精确查找的时候非常有用。

区分全/半角

这个就是针对输入的格式来进行查找，可以在有需要进行区分的时候使用。

找对比，你会首先想到Vlookup函数。但在Excel中只会Vlookup函数是远远不够的。今天我就对查找公式进行一次全面的整理。

1、普通查找

查找李晓峰的应发工资=VLOOKUP(H2,B:F,5,0)

2、反向查找

查找吴刚的员工编号=INDEX(A:A,MATCH(H2,B:B,0))

3、交叉查找

查找3月办公费的金额=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)

4、多条件查找

查找上海产品B的销量=LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)

5、区间查找

根据销量从右表中查找提成比率。=LOOKUP(A2,$D$2:$E$5)

6、双区间查找

根据销量和比率完成情况，从表中查找返利。=INDEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2))

7、线型插值

如下图所示，A列是数量，B列是数量对应的系数值。现要求出数字8所对应的系数值。=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3)

8、查找最后一个符合条件记录

如下图所示，要求查找A产品的最后一次进价。=LOOKUP(1,0/(B2:B9=A13),C2:C9)

9、模糊查找

如下图所示，要求根据提供的城市从上表中查找该市名的第2列的值。=VLOOKUP("*"&A7&"*",A1:B4,2,0)

10、匹配查找

如下图所示，要求根据地址从上表中查找所在城市的提成。=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)

11、最后一个非空值查找

如下图所示，要求查找最后一次还款日期=LOOKUP(1,0/(B2:B13<>""),$A2:$A13)

12、多工作表查找

【例10】从各部门中查找员工的基本工资，在哪一个表中不一定。

方法1 ：

=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息")))))

方法2：

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)

13、一对多查找

【例】根据产品查找相对应的所有供应商

A2 =B2&COUNTIF(B$1:B2,B2)

B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")

14、查找销量最大的城市

查找销量最大的城市（数组公式按ctrl+shift+enter三键输入）

{=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))}

15、最接近值查找

根据D4的价格，在B列查找最接近的价格，并返回相对应的日期

（数组公式按ctrl+shift+enter三键输入）

{=LOOKUP(1,0/(MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)}

16、跨多文件查找

跨多个文件查找，估计你搜遍网络也找不到，这也是兰色首次编写跨多文件查找公式。其实原理和跨多表查找一样，也是借助lookup等函数实现。

文件夹中有N个仓库产品表格，需要在“查询”文件完成查询

仓库表样式

在查询表中设置公式，根据产品名称从指定的文件中sheet1工作表查询入库单价

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1!a:a"),A2),"["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1")&"!a:b"),2,0)。

vlookup函数的多文件查找，同样可以用iferror+vlookup的模式，公式虽然长，但容易理解且公式不容易出错。另外，如果excel文件有几十个或更多，就需要使用宏表函数用Files获取所有excel文件名称，然后应用到公式中，具体的公式我就不再写了。

能想到的查找公式差不多都列出来了，其中有不少公式需要有一定基础才能看懂。如果看不明白先学会套用以后再慢慢理解。

批量输入公式批量修改公式快速查找公式显示公式部分计算结果保护公式隐藏公式显示所有公式把公式转换为数值显示另一个单元格的公式把表达式转换为计算结果快速查找公式错误原因

1批量输入公式

选取要输入的区域，在编辑栏中输入公式，按CTRL+ENTER即可批量填充公式。

2批量修改公式

如果是修改公式中的相同部分，可以直接用替换功能即可。

3快速查找公式

选取表格区域 - 按Ctrl+g打开定位窗口 - 定位条件 - 公式，即可选取表中所有的公式

4显示公式部分计算结果

选取公式中的要显示的部分表达式，按F9键

按F9键后的结果

想恢复公式，按esc退出编辑状态即可。

5保护公式

选取非公式的填写区域，取消单元格锁定。公式区域不必操作。

设置单元格格式后，还需要保护工作表：审阅 - 保护工作表。

6隐藏公式

隐藏公式和保护公式多了一步：选取公式所在单元格区域，设置单元格格式 - 保护 - 勾选“隐藏” - 保护工作表。

隐藏公式效果：

7显示所有公式

需要查看表中都设置了哪些公式时，你只需按alt+~组合键（或 公式选项卡 - 显示公式）

把公式转换为数值

8把公式转换为数值

公式转换数值一般方法，复制 - 右键菜单上点123（值）

9显示另一个单元格的公式

如果想在B列显示A列的公式，可以通过定义名称来实现。

公式 - 名称管理器 - 新建名称：G =get.cell(6,sheet3!a4)

在B列输入公式=G，即可显示A列的公式。

excel2013中新增了FORMULATEXT函数，可以直接获取公式文本。

10把表达式转换为计算结果

方法同上，只需要定义一个转换的名称即可。

zhi =Evaluate(b2)

在B列输入公式 =zhi即可把B列表达式转换为值

11快速查找公式错误原因

当一个很长的公式返回错误值，很多新手会手足无措，不知道哪里出错了。兰色介绍排查公式错误的技巧，3秒就可以找到错误。

下面兰色做了一个简单的小例子

【例】：如下图所示，单元格的公式返回值错误。要求排查出公式的哪部分出现了错误。

操作方法：

1、 打开单元格左上角绿三角，点“显示计算步骤”

2、在打开的“公式求值”窗口中，求值会自动停在即将出错的位置。这时通过和编辑栏中的公式比对，就可以找出产生错误的单元格。（D7）

如果公式中有多处错误，可以先修正前一次，然后再点显示计算步骤，查找下一处错误。

当我们辛辛苦苦做了一个很长的公式需要计算出成果时却返回错误值给我们了，很多新手会手足无措，不知道哪里出错了。今天就为大家带来一个轻轻松松搞定公式错误的解决方法，3秒钟就可以找到错误。

下面给大家做了一个简单的小例子：

【例】：如下图所示，这就说明我们单元格的公式返回值出现了错误。要求排查出公式到底是哪部分出现了错误，我们来具体看一下

操作方法吧！

1、 我们现在需要打开单元格左上角的绿三角，然后点“显示计算步骤”

2、在打开的“公式求值”窗口中，求值会自动停在即将出错的位置。这时只要通过和编辑栏中的公式对比一下，就可以找出产生错误的单元格。（D7）

怎么样今天的知识分享大家学会了吗？如果在我们的公式中还有多处的错误，可以先修正前一次，然后再选择显示计算步骤，查找下一处错误，直到正确为止。

好了，今天的分享就到这里了，如果您还没有关注我们就请点击关注吧，如果您觉得的此文非常有用就为我们点赞哦！您有更好的方法也希望您在评论区与大家一起分享出你的技术，关注后每天学会更多办公软件技巧，我们一起来提高吧！

查找引用在工作中的需求太常见了，如果你还在Excel中手动查找，就太OUT啦，那样不但效率低下，还极易出错......

这篇教程中给大家科普一下工作中适用场景最多的查找引用方法，便于大家直接套用，提高工作效率，远离加班！

1 单条件查找

2 多条件查找

3 从下向上逆向查找

4 双条件十字交叉查找

5 一对多查找所有符合条件的数据

阅读过程中，即使你没理解公式原理，也不影响工作中直接套用这些公式解决问题，建议同学们可以点右上角三个点先收藏起来。

遇到从一个表格里按条件查找数据，把这些提取到另一个表里，你还在手动一个一个弄吗？

场景演示见下图（黄色区域为公式生成）

=VLOOKUP(D2,$A$2:$B$12,2,0)

除了单条件查找，多条件查找也不在话下，一个公式照样搞定！

=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$12&$B$2:$B$12,$C$2:$C$12),2,0)

注意这里要使用数组公式，按组合键输入。

有时候遇上要查找符合条件的最后一个数据时，这个公式会帮到你很多！

=LOOKUP(1,0/($B$2:$B$13=F2),$D$2:$D$13)

双条件在报表行列中交叉查询的需求，80%的同学都会用得上！

场景演示见下图（点击gif观看动态演示）

=INDEX(B2:E9,MATCH(H3,A2:A9,0),MATCH(H4,B1:E1,0))

除了单条件查找、多条件查找、逆向查找和十字交叉查找，再来讲个一对多查找！

如果报表中存在多个符合条件的数据，你想把他们统统找出来，用这招！

=INDEX(B:B,SMALL(IF(A$2:A$11=D$2,ROW($2:$11),4^8),ROW(A1)))&""

注意这也是数组公式，按

如果你觉得有用，就分享给朋友们看看吧~

End.

在需要查找时，很多童鞋会首先想到VLOOKUP函数。但有时候仅仅只会VLOOKUP函数是远远不够的。今天小编为大家进行了一次全面的整理，一起来看吧！

一、普通查找

查找李晓峰的应发工资。在对应单元格中输入“=VLOOKUP(H2,B:F,5,0)”

二、反向查找

查找吴刚的员工编号。在对应单元格中输入“=INDEX(A:A,MATCH(H2,B:B,0))”

三、交叉查找

查找3月办公费的金额。在对应单元格中输入“=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)”

四、多条件查找

查找上海产品B的销量。在对应单元格中输入“=LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)”

五、区间查找

根据销量从右表中查找提成比率。在对应单元格中输入“=LOOKUP(A2,$D$2:$E$5)”

六、双区间查找

根据销量和比率完成情况，从表中查找返利。

在对应单元格中输入“=IDNEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2))”

七、线性插值

求数字8所对应的系数值。在对应单元格中输入“=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3）”

八、查找最后一个符合条件记录

查找A产品最后一次进价。在对应单元格中输入“=LOOKUP(1,0/(B2:B9=A13),C2:C9)”

九、模糊查找

根据提供的城市从表中查找该市名的第2列的值。在对应单元格中输入“VLOOKUP("*"&A7&"*",A1:B4,2,0)”

十、匹配查找

根据地址从表中查找所在城市的提成。在对应单元格中输入“=LOOKUP(9^9.FIND(A$3:A$6,A10),B$3:B$6)”

十一、最后一个非空值查找

查找最后一次还款日期。在对应单元格中输入“=LOOKUP(1,0/(B2:B13<>""),$A2:$A13)”

十二、多工作表查找

从各部门中查找员工的基本工资，在哪个表中不一定。

方法1：

在对应单元格中输入“=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息”)))))”

方法2：

在对应单元格中输入“=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)

十三、一对多查找

根据产品查找对应的所有供应商。在A2单元格中输入“=B2&COUNTIF(B$1:B2,B2)”;

在B11单元格中输入“=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),""”

十四、查找销量最大的城市

查找销量最大的城市（数组公式按ctrl+shift+enter三键输入）

在对应单元格输入“{=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))}”

十五、最接近值查找

根据D4的价格，在B列查找最接近的价格，并返回相对应的日期。（数组公式按ctrl+shift+enter三键输入）

在对应单元格输入“=LOOKUP(1,0/MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)}”

十六、跨多文件查找

文件夹中有N个仓库产品表格，需要在“查询”文件完成查询。

仓库表样式

在查询表中设置公式，根据产品名称从指定文件中sheet1工作表查询入库单价。在对应单元格输入“=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1!a:a")A2),"["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1")&"!a:b"),2,0)”

平时会用到的查找公式基本就这么多了。其中有不少公式需要有一定基础才能看懂，如果不太明白可以先套用，日后慢慢理解。

今天的Excel查找公式就讲到这里了，这些内容对于你的工作或学习有没有帮助呢？如果你有任何问题，记得在评论区留言，小编看到后会第一时间回复。

Excel中反向查找也有很多种公式写法，比如一些高手们经常玩的lookup函数的经典查找模式：Lookup(1,0/((条件1)*(条件2)*(条件n)),返回值)。本文和大家聊的是INDEX+MATCH组合。

咱们通过E2单元格的姓名，来查找对应的A列的部门。

看到上面的函数嵌套，对EXCEL了解不多的，是不是容易晕呢。别晕哈，小雅给大家分步梳理一下。

第一，查找E2单元格的姓名，在C2:C13区域的行数。

MATCH单个函数容易理解，MATCH(G2,C2:C13,0)，就是查找E2单元格的姓名“周学”在C2：C13中位于第几行。答案是6行。

第二，我们在看A2:A13单元格区域中第6行值是多少？答案是：售后部。这正是我们所需要的结果。

我们用INDEX函数来返回值：INDEX(A2:A13,6,0)，得到“售后部”。

所以，咱们综合上面两个步骤，把第一个步骤的公式，做INDEX函数的第二参数，公式就合并为：上述公式可以合并为=INDEX(A2:A13,MATCH(E2,C2:C13,0))。

然后，如果您想E2单元格的姓名作为动态显示的，就用数据有效性做一个下拉列表，选择不同的姓名，部门就变为姓名相对应的其他部门。

你知道的EXCEL逆向（从右往左）查找还有哪些组合写法哦，评论告诉我们！

找对比，你会首先想到

Vlookup

函数。但在Excel中只会Vlookup函数是远远不够的。今天兰色对查找公式进行一次全面的整理。

对于一个地方需要用到对比的时候我们肯定想到的是Vlookup函数，但是你在用Excel的时候只会用它可不行，今天我们就一起来梳理下常用的！

地球人都知道：一题多解的只选取最优公式！

1、普通查找VLOOKUP函数

我们需要查找李晓峰的应发工资

公式：=VLOOKUP(H2,B:F,5,0)

2、反向查找INDEX

函数

查找吴刚的员工编号

公式：=INDEX(A:A,MATCH(H2,B:B,0))

反向查找

3、交叉查找VLOOKUP函数：

查找3月办公费的金额

公式：=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)

4、多条件查找

VLOOKUP函数

查找上海产品B的销量

公式：=LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)

5、区间查找

根据销量从右表中查找提成比率。

公式：=LOOKUP(A2,$D$2:$E$5)

6、双区间查找

根据销量和比率完成情况，从表中查找返利。

公式：=INDEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2))

7、线型插值

A列是数量，B列是数量对应的系数值。现要求出数字8所对应的系数值。

公式：=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3)

8、查找最后一个符合条件记录

要求查找A产品的最后一次进价。

公式：=LOOKUP(1,0/(B2:B9=A13),C2:C9)

9、模糊查找

要求根据提供的城市从上表中查找该市名的第2列的值。

公式：=VLOOKUP("*"&A7&"*",A1:B4,2,0)

10、匹配查找

要求根据地址从上表中查找所在城市的提成。

公式：=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)

11、最后一个非空值查找

要求查找最后一次还款日期

公式：=LOOKUP(1,0/(B2:B13<>""),$A2:$A13)

12、多工作表查找

【例10】从各部门中查找员工的基本工资，在哪一个表中不一定。

方法1

公式：=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息")))))

方法2：

公式：=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)

13、一对多查找

【例】根据产品查找相对应的所有供应商

公式：

A2 =B2&COUNTIF(B$1:B2,B2)

B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")

14、查找销量最大的城市

查找销量最大的城市。注意：注意：注意：数组公式按ctrl+shift+enter三键输入

公式：{=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))}

15、最接近值查找

根据D4的价格，在B列查找最接近的价格，并返回相对应的日期

注意：注意：注意：数组公式按ctrl+shift+enter三键输入

公式：{=LOOKUP(1,0/(MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)}

15、跨多文件查找

跨多个文件查找，网上大概很难找到这样的教程，仔细想想其实原理和跨多表查找一样，也是借助lookup等函数实现。

文件夹中有N个仓库产品表格，需要在“查询”文件完成查询

仓库表样式

在查询表中设置公式，根据产品名称从指定的文件中sheet1工作表查询

入库单价

公式：=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1!a:a"),A2),"["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1")&"!a:b"),2,0)。

如果你在用vlookup函数做多文件查找的时候也可以用iferror+vlookup的模式的这种模式，看起来公式很长，但是这样不会出错。此外，如果一个表用到的函数太多就需要用宏表函数用Files获取所有excel文件名称了。

老男孩PS

：我能想到的基本上都已经列出来了，但是我相信有很多大家可能看不懂，但是看不懂没关系，赶紧收藏起来，然后直接套用就可以了。