EXCEL函数功能多种多样，个人认为用于查找引用类函数是其中最核心的一部分。很多时候需要的数据取出来了，问题也就解决了。
查找引用涉及的知识点很杂，从最简单的LOOKUP、VLOOKUP，到复杂点的INDEX、OFFSET、INDIRECT，还有让很多人头疼不已的数组。虽说每个知识点单拎出来网上都能找到详细教程，但最基本的，你要先知道有这些点。
这也就是我写这份笔记的目的，希望能帮大家了解EXCEL查找与引用中都有哪些花里胡哨。
注：本文较长，在网页端可能获得更好的阅读体验
-
用VLOOKUP实现多条件查找
-
用VLOOKUP返回多个匹配值
四、题外话：新版OFFICE增加的强力函数
EXCEL提供了两种引用样式：A1样式和R1C1样式。
A1样式即我们平时最常见的样式，此时列上显示字母，该样式下用$表示绝对引用，绝对引用的部分在使用填充柄填充公式时引用区域不会发生变化。
R1C1使用引用单元格与当前单元格的相对位置来描述引用，此时列上显示数字。这种样式下：
R[2]C[2] 对在下面两行、右面两列的单元格的相对引用；
R2C2 对在工作表的第二行、第二列的单元格的绝对引用；
R[-2]C 对在同一列、上面两行的单元格的相对引用；
R[-1] 对活动单元格整个上面一行单元格区域的相对引用；
R 对当前行的绝对引用
这两种样式在EXCEL中可以自由切换，以office2016为例，按照 “文件→选项→公式→R1C1引用样式复选框” 步骤即可完成两种引用样式间的切换。
Excel允许用户对区域进行命名，然后通过名称引用区域。如下图，我们将B1:D3的区域命名为“求和”后，可以看到sum(B1:D3)与sum(求和)的结果是相同的。
选定区域后，直接在左上角输入名称即可
按下CTRL+`即可在公式与结果间切换
对于命名好的区域，可以按照 “公式→名称管理器” 的方式进行查看，当然也可以通过“名称管理器”或“定义名称”直接添加新区域。
注意：引用位置除了填写静态区域外，还可以通过OFFSET等函数实现动态引用，配合INDIRECT可以制作多级下拉菜单，具体方法将在下文介绍OFFSET的时候详细介绍。
Excel可以通过{}录入数据，其中用“，”分隔行内数据，用“；”分隔不同行的数据。
下面举几个例子简单说明（请注意“，”和“；”的区别）：
注意：使用数组时函数需要用 “ctrl+shift+enter” 快捷键来完成输入，否则会产生错误结果。
LOOKUP是关于查找的函数中最基础的一个，语法为：
后一种情况下返回查找区域内最后一列的值
注意：LOOKUP采用二分法进行查找，这带来了许多不便，具体有
1）查找列必须升序排列，否则可能返回预期之外的结果
使用LOOKUP时，索引列必须升序排列
2）当查询数值不存在时不会报错，会返回小于查询值的最接近值（前提是索引列必须按升序排列），在需要精确匹配的场合这意味着返回值无法被信任。
因为LOOKUP函数的这些缺陷，因此个人建议绝大多数情况下用VLOOKUP替代LOOKUP，少有的适合LOOKUP的应用场景如下：
1）利用其返回值的特性，按区间返回评级
LOOKUP可以用来构建分段函数
2）查询值多次出现时，返回最后一次的值
LOOKUP返回最后一个匹配值
若要实现图中效果，其固定函数结构为 LOOKUP（1,0/(查找范围=查找值),结果范围），下面解释具体原理：
0/(A2:A7=D2) 构建了一个如下图所示的查找区域，当单元格值=查找值时为0，其余为除零错误，LOOKUP查找时会忽略错误值，而LOOKUP的查找逻辑会让其返回不大于查找值（即1）的最后一个值，也就是最后一个0。
VLOOKUP和HLOOKUP用法基本相同，区别在于VLOOKUP查找区域中第一列的数据，返回第n列中的值，而HLOOKUP查找区域中第一行的数据，返回第n行的值。
VLOOKUP(查找值,查找区域,返回第几列,是否模糊查找）
HLOOKUP(查找值,查找区域,返回第几行,是否模糊查找）
VLOOKUP函数基本用法演示
HLOOKUP函数基本用法演示
因VLOOKUP与HLOOKUP语法上并无太大差别，且日常工作中大家数据多为列式存储，VLOOKUP出场次数远多于HLOOKUP，故接下来将仅演示VLOOKUP的操作。
1）当需要填充公式时，因为查找区域一般不会改变，可以用$改为绝对引用。
2）可用MATCH函数替换VLOOKUP的第三个参数，这样更改查询内容时将不再需要更改函数。
MATCH(查找值,查找区域,匹配方式）
3）可以使用通配符进行模糊查找，其中“*”通配任意个字符，“?"通配单一字符
但需要注意的是，当查找值为纯数字时，通配符不生效。如必须使用通配符，可采用下面两种方法：
[1] 将查找区域第一列的数字作为文本存储（数字前加上'）；
需要注意的有以下三点：
"A1"与A1的结果截然不同
与区域命名配合，INDIRECT可以轻松制作多级菜单，简述方法如下：
如图，我们有这么一个参数表，我们希望以城市名称为一级菜单，以下面的产品代码为二级菜单。
一级菜单：选中F2单元格，“数据→数据验证”，打开数据验证对话框，选择序列，来源为$B$1:$D$1，注意这里用的是绝对引用
二级菜单：选中B:D，“公式→根据所选内容创建”，仅勾选首行复选框，点击确定
创建后可在名称管理器中查看
最后选中G2单元格，打开数据验证，选择序列，来选处输入=INDIRECT(F2)即可。
至此便完成了多级菜单的设置。具体效果如下：
OFFSET函数通过初始区域和偏移坐标来定位一个新的引用区域，其语法如下：
OFFSET(初始位置,行偏移值,列偏移值,[新区域行数],[新区域列数])
其中初始位置可以是一个单元格或者一个区域，当为区域时，取区域左上角单元格坐标为起始值。偏移值为正时向下向右偏移，为负时向左向上偏移。
一、用VLOOKUP实现逆向查询
前面提到，使用VLOOKUP进行查找时，索引列必须位于区域的最左端。一般情况下，我们只需要调整列的顺序，使索引列位于查找区域的最左侧即可。下面给出不方便调整列的顺序时的解决方案。
解决这个问题的核心是在函数内构建一个新数组，对于这个新构建的数组而言，查找列仍位于区域的最左端。CHOOSE函数和IF{1,0}都可实现这一目的，下面先介绍较为简单的CHOOSE函数。
用CHOOSE调换列的位置非常简单，其语法是
CHOOSE({1,2,3},列C,列B,列A），这样就能构建一个按照列C-列B-列A排列的新数组了，调整前面数组内数字的大小顺序，即可改变生成的新数组中各列的出现顺序。注意这里用到了数组，需要以 ctrl+shift+enter 完成输入。
与CHOOSE相比，IF{1,0}就不那么直观了，其核心是构造一个形如 IF({1,0},列A,列B) 的公式，该公式以列A,列B的顺序输出一个数组，效果如图所示：
我们来拆解一下这个公式，首先根据数组运算法则，{1,0}是1*2的数组，A1:A2和B1:B2均是2*1的数组，它们一起运算时将会扩充成2*2的数组进行计算，具体情况如下图所示：
而IF在进行运算的时候，会将0代表FALSE，1代表TRUE，即IF(1)为真，IF(0)为假，而根据IF({1,0},B1:B2,A1:A2)的写法，为真时输出B列的值，为假时输出A列的值。因此产生了调换两列位置的效果，图示如下：
由此我们还能理解，如果调换了{1,0}中0和1的位置，两列的输出顺序也将对调，即IF({1,0},列2,列1)等价于IF({0,1),列1,列2)。
题外话：EXCEL进行逻辑运算时，1以上同样视为TRUE，利用这一性质，可以用连乘表示AND关系，用连加表示OR关系。
只要理解了这个公式，剩下的操作就与CHOOSE无异了：
该公式中同样使用了数组，所以也要用ctrl+shift+enter完成输入。
需要注意的是，IF{1,0}只能调换两个单列的位置，因此当我们要输出多列时就不太适用了。如果遇到这个问题，除了改用CHOOSE函数来构造数组，还可以利用OFFSET构建一个动态引用。
用OFFSET构建动态引用
二、用VLOOKUP实现多条件查询
有时候我们需要利用原表中的多列数据才能精确确定要查找的值，这种情况下最简便的做法是在区域最左侧添加辅助列，将需要的多个条件合并，运算符&和函数CONCATENATE均能起到需要的效果。当不方便添加辅助列时，我们就要用到上例中一样的技巧，利用IF{1,0}或CHOOSE在公式中构建需要的数组。具体演示如下：
三、VLOOKUP返回多个符合条件的结果
VLOOKUP查找时是从上往下寻找，返回第一个匹配的值。当列表中有多个匹配值时单靠VLOOKUP并不能将其全部找出。下面给出几种解决方案：
构建辅助列的方案最为直观易懂，我们只需将索引列的内容和其出现次数拼接在一次，即可构建查找所需的新标识。函数本身并不复杂，唯一需要注意的就是COUNTIF中参数的写法，只有图中所示的写法才能保证在向下自动填充时保证区域不出错。
在构建完辅助列后，剩余的工作就十分简单了：
需要注意的是，ROW(A1)并没有什么深意，只是为了构建出一个向下拉伸时能自动递增参数，所以当你需要横向拉伸时，只需要用COLUMN代替ROW即可。同时因为左侧只有3个张三，所以第四行出现了错误，如果在实际使用中这种错误令人难以接受，只需要用IFERROR函数进行预处理即可。
下面提供更符合现实情况的写法：
2）在VLOOKUP中构建临时数组解决
与多条件查询类似，当我们不方便在外构建辅助列时，可以利用IF{1,0}或CHOOSE在VLOOKUP内部构建数组。
公式很长，但大多数是前面已经介绍过的函数和用法，这里只介绍公式的核心部分
然后，我们利用INDIRECT对其进行引用，并利用COUNTIF对区域中含有的张三数量进行计数；
最后，我们组合计数与A列的值，形成一个用于查询的新索引列。
值得注意的是：除被查询值外（例子中为张三），其他值的计数并不正确，但这并不会影响到查询结果。
从上面的案例中我们可以发现，当要对查找区域进行处理的时候，VLOOKUP就显得十分笨重。在这类场合，我们可以尝试使用INDEX+MATCH的组合来替代VLOOKUP。
先介绍下新出现的函数INDEX，INDEX有两种参数格式，
第一种是 INDEX(区域,行数,列数) ，函数返回区域内指定行、列交叉处的内容；
第二种是 INDEX((区域1,区域2...),行数,列数,区域数) 与第一种类似但可以输入非连续区域，只是要在函数的最后需要增加一个参数以说明引用的是第几区域内的内容。
要与MATCH配合替代VLOOKUP，我们一般采用是第一种格式。下面我们用具体例子来看一下：
该公式的思路非常简单，即先利用MATCH找出所需数据的行和列，再利用INDEX返回该行列交叉处的值。
那么与VLOOKUP相比，这种方式有什么优点呢？
需要返回最后一个符合要求的值
该函数的逻辑非常简单：TRUE=1,FALSE=0，再与行号相乘，不符合条件的行结果为0，符合条件的行返回行号，然后再求其中的最大值，即为最后一个符合要求的值所在行。唯一需要注意的是，查询区域需要从该列第一行开始，因为我们求得的值是行号，而行号是从第一行开始计数的。
需要返回多个符合要求的值
公式逻辑如下：先判断索引列是否符合条件，是返回该单元格行号，否返回65535，其中65535并没有特殊意义，实际上这里只需要填入一个比你查找区域大的数字即可（本例中8即符合要求）。然后利用SMALL函数返回第k个最小值完成索引，k值由ROW(A1)提供递增序列。
同时如果我们将SMALL函数改为LARGE，然后将65535改为-1，即可完成倒序输出，如下图所示：
同样的，-1也并没有深意，你只需要填入一个小于0的数即可。错误值可以用IFERROR预处理。
前面介绍了很多查找引用的方法，虽然演示的时候是以office2016版为基础的，但在office2010甚至更老的版本也能正常运行。认真的读者应该也都发现了，之所以查找与引用存在这么多的技巧，本质上还是因为office提供的函数有缺陷和不足所致。
但实际上，对于购买了office2019及365的用户而言（可能需要加入office预览体验计划），office已经提供了更加强大的函数来解决这类问题，下面简单介绍一下（没有演示，因为我也没买T^T）。
-
查找区域和返回区域分开，不再要求查找值位于区域内的第一列；
-
当返回区域不止一列时，函数会自动填充相邻的数据列，相当于VLOOKUP+MATCH；
-
查找区域和返回区域分开，处理的时候互不影响、灵活许多，在这一方面类似于用INDEX+MATCH替代VLOOKUP；
-
可自由选择倒序/顺序查找，远比LOOKUP的解决方案简单易懂。
FILTER作用是返回满足筛选条件的所有值，其语法非常简单：
FILTER(要返回的数据列,筛选条件)，当数据列不止一列时函数同样会自动填充相邻的数据列。
需要注意的是，FILTER函数的筛选条件必须是一组true/false的数组而不是具体数值，即：假设有一张A1:D5的表格，如果你要筛选A列姓名为张三的所有信息，函数应写为FILTER(B1:D5,A1:A5="张三"）。
非常感谢各位看官耐心读到这里，以上就是我关于EXCEL查找与引用知道的所有信息，希望能帮上各位。
本笔记纯属一时兴起，不过如果能找到什么有趣的新角度，我会继续更新的。各位看官对本文如有什么补充或有趣的想法，也欢迎留言，就酱。
点击联系发帖人
