LOOKUP、VLOOKUP查找与引用函数专题--详细

1、探讨学习EXCEL中的常用的引用函数问题2013查找与引用专题查找函数与引用函数延伸性总结学习pcLOOKUP HLOOKUP HLOOKUP 查找与引用函数一览：函数分类一， 数据的查找以查找值为基准，从工作表中查找与该值匹配的值使用。其中VLOOKUP是常用函数。VLOOKUP在首列查找数值，并返回当前行中指定列处的数值HLOOKUP在首行查找数值，并返回当前列中指定行处的数值LOOKUP(向量形式)从向量中查找一个值LOOKUP (数组形式)从数组中查找一个值INDEX(引用形式)返回指定行列交叉处的单元格引用INDEX(数组引用)返回指定行列交叉处的单元格值VLOOKUP函数含义：VL

2、OOKUP是按列查找，最终返回该列所需查询列序所对应的值；与之对应的HLOOKUP是按行查找的。提示（VLOOKUP函数使用注意事项）一VLOOKUP的语法VLOOKUP函数的完整语法是这样的：VLOOKUP(lookup _value, table _array, col _index _num , range _lookup)1括号里有四个参数，是必需的。最后一个参数range_lookup是个逻辑值，我们常常输入一个0字，或者False;其实也可以输入一个1字，或者true。两者有什么区别呢？前者表示的是完整寻找，找不到就传回错误值#N/A；后者先是找一模一样的，找不到再去找很接近的值，

3、还找不到也只好传回错误值#N/A。2Lookup _value是一个很重要的参数，它可以是数值、文字字符串、或参照地址。我们常常用的是参照地址。用这个参数时，有三点要特别提醒：A）参照地址的单元格格式类别与去搜寻的单元格格式的类别要一致，否则的话有时明明看到有资料，就是抓不过来。特别是参照地址的值是数字时，最为明显，若搜寻的单元格格式类别为文字，虽然看起来都是123，但是就是抓不出东西来的。而且格式类别在未输入数据时就要先确定好，如果数据都输入进去了，发现格式不符，已为时已晚，若还想去抓，则需重新输入。B）在使用参照地址时，有时需要将lookup _value的值固定在一个格子内，而又要使用下

4、拉方式（或复制）将函数添加到新的单元格中去，这里就要用到“$”这个符号了，这是一个起固定作用的符号。比如说我始终想以D5格式来抓数据，则可以把D5弄成这样：$D$5，则不论你如何拉、复制，函数始终都会以D5的值来抓数据。C) 用“& 连接若干个单元格的内容作为查找的参数。在查找的数据有类似的情况下可以做到事半功倍。3Table _array是搜寻的范围，col _index _num是范围内的栏数。Col _index _num不能小于1，其实等于1也没有什么实际用的。如果出现一个这样的错误的值#REF!，则可能是col _index _num的值超过范围的总字段数。4.在使用该函数时，loo

5、kup _value的值必须在table _array中处于第一列。语法该函数的语法规则如下：VLOOKUP(lookup _value , table _array , col _ index _num , range _lookup)参数简单说明输入数据类型Lookup _value要查找的值数值、引用或文本字符串Table _array要查找的区域数据表区域Col _index _num返回数据在区域的第几列数正整数Range _lookup模糊匹配TRUE（或不填） /FALSE示例 HLOOKUP函数要结合MATCH函数研究，略。LOOKUP函数含义返回向量或数组中的数值。函数LOO

6、KUP 有两种语法形式：向量和数组。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。函数 LOOKUP 有两种语法形式：向量和数组。提示LOOKUP _vector 的数值必须按升序排序：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。语法（1）向量形式：公式为 = LOOKUP(lookup _value ,lookup _v

7、ector , result _vector)式中 lookup _value函数LOOKUP在第一个向量中所要查找的数值，它可以为数字、文本、逻辑值或包含数值的名称或引用；Lookup _vector只包含一行或一列的区域lookup _vector 的数值可以为文本、数字或逻辑值；Result _vector只包含一行或一列的区域其大小必须与 lookup _vector 相同。（2）数组形式：公式为= LOOKUP (lookup _value , array)式中 array包含文本、数字或逻辑值的单元格区域或数组它的值用于与 lookup _value 进行比较。例如：LOOKUP(

8、5.2,4.2,5,7,9,10)=5。注意：lookup _vector的数值必须按升序排列，否则函数LOOKUP不能返回正确的结果。文本不区分大小写。如果函数LOOKUP找不到lookup _value，则查找lookup _vector中小于或等于lookup _value的最大数值。如果lookup _value小于lookup _vector中的最小值，函数LOOKUP返回错误值#N/A。示例该函数对引用区LOOKUP-vector有顺序要求，所以相对严谨。=LOOKUP(E2,$B$2:$B$7,$C$2:$C$7)所以LOOKUP只适合于升序的数字查找区，而引用区的数字或文本顺序

9、没要求。这类适用于查找有编号的数字区。，修改结果返回区后，发现result -vectorI不一定要为数字，其它文本符号等都可以。这里千万要记住引用的查找区lookup vector一定要为顺序排列的数字。反例对于求和的情况，那LOOKUP虽然查找效果不错，但不适合用，反倒SUMIF合适。=IF(SUM(SUMIF($A$1:$A$4,F1,$C$1:$C$4),SUMIF($A$8:$A$12,F1,$C$8:$C$12)0,SUM(SUMIF($A$1:$A$4,F1,$C$1:$C$4),SUMIF($A$8:$A$12,F1,$C$8:$C$12),)因为字母虽然按顺序来，但不是数字，

10、所以LOOKUP做不到所需要的效果。E H均查找不到 按照lookup的查找规则 会返回小于E H的数 D为最后一个小于E和H的数 所以D即为查找值所以可以采用lookup(1,0/这种方式就是精确查找 其公式详解见另外 还有 matchindex高深的研究，有待再探索（下面是别人发的一个LOOKUP比VLOOKUP优秀的例子）=LOOKUP ( ( ROW(1:4)-1)*5+COLUMN(A:E),ROW(1:20),A1:A20)举个上面的例子 lookup的变形 一列 转换为4列 多单元格数组公式再举个例子合并单元格的查找利用lookup返回小于等于查找值这个原理将合并单元格中为空的补

11、齐：=INDEX(C:C,MATCH(F1&G1,LOOKUP(ROW(1:12),IF(A1:A12,ROW(1:12),A1:A12)&B1:B12,)这其中的lookup就是补齐为空的a列数据还有文本数字提取数字的这个很常见就不举例了与LOOKUP相关结合的函数：offset，match sumproduct在Excel中，OFFSET函数的功能为以指定的引用为参照系，通过给定偏移量得到新的引用OFFSET(reference,rows,cols,height,width)MATCH函数含义：返回指定数值在指定数组区域中的位置MATCH(lookup_value, lookup_arra

12、y, match_type)lookup_value：需要在数据表（lookup_array）中查找的值。lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须包含在某一行或某一列。match_type：为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列：为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列：为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列。另外，大家是否

13、有另一个担忧如果区域不是升序排列的呢？lookup还可能完成查找吗？当然，答案仍然是肯定的。下面举例说明：图十lookup处理乱序排列的数据从上图中，我们可以看到，参考数据区的姓名以乱序排列，而lookup是不能查找乱序数据的。怎么办？变通方式是：将查找区域值变成一个只有两种类型的值，例如五个数据#DIV/0!,0, #DIV/0!, #DIV/0!, #DIV/0!，其中四个是错误值，一个是0.那么我们将lookup的第一参数设为0，就可以找到它对应的位置。但是如何将当字变成错误值和0呢？请看思路：“张三丰”=“张三丰”,它的结果是true，表示两者相等，而0/true的结果等于0；“张三丰

14、”=“李四光”结果为false，表示两者不等，而0/false的结果等于错误值，因为除法中0不能做为除数，强制计算那么结果就为#DIV/0!。有了思路就可以动手搭建公式了。1.以图十为例，首先用A2=D2:D9，它得到的结果是FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE2.然后用0/ (A2=D2:D9)得到一串错误值和一个唯一的零值：#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!3.有了这个结果所有问题都解决了，直接在其中查找0就行了。最后公式是：=LOOKUP(0,0/(A2

15、=D$2:D$9),E$2:E$9)大家可以把这个普通公式当做一个定理来用，当目标区不是升序排列时（是升序也可以用）：=lookup(0,0/(查找值=查找区域),目标存放区域)LOOKUP二分法的理解先引一个例子进行理解性的扩展解释：=LOOKUP (1,0/(A1:A65535),A1:A65535)公式解析：这是单条件查找唯一值的查询公式，区域条件，是一个条件表达式，生成的结果是 逻辑值集合。0/逻辑值集合的结果是0和1的集合（逻辑值之间是可以相互运算的）。a1:a100。在执行过程中第一步判断A1是不是不为空，如果是空单元格那么值为FALSE,如果不是，结果为TRUE。判断好了A1就接

16、着判断A2，一直判断到区域的第后一个单元格。每一次判断，都会形成一个逻辑值结果，也因此，区域会形成一个逻辑值的集合。比如true,false,fasle。区域中有多少个单元格，也就会生成多少个 逻辑值的 集合，在你这个应用中，集合中的TRUE值只有1个。0/逻辑值你可以想一下0/true,其实就是0除以1结果为00/false其实就是0除以0，结果为错误值0是不能当除数的，那样无意义在唯一值查找中，条件生成的逻辑值集中，仅有一个TRUE那么0/逻辑值集合的结果，是一堆错误值和一个唯一的0的集合lookup(1,0/逻辑值集合，结果区域） LOOKUP是遵循模糊查找的规则也默认第二参数的查找区域

17、为升序尽管你生成的查找区域为乱序的，LOOKUP在查找过程中也当它是升序因为查找区域中，是一个0和多个错误值的集合尽管集合中没有1。0/后不可能会出现大于1的数，所以必定返回最后一个等于0的值但LOOKUP查找是模糊查找，而不是精确查找所以，返回的是=0的自然数，都可以。计算原理：第一次比较，取数据中的中间值0.2。满足条件的话，继续向后比较，第二次取0.1比较满足条件，继续向后比较，第三次取0.3仍满足条件，得到结果 5多条件查找多个结果中的最后一个结果，可不能这样子来了max(条件1*条件2*row(区域)可以确定满足两条件的多个结果中最后一个结果所在的行号。这也就意味着确定了，两个条件满

18、足后的最后一个结果的行号。再通过indexindirectoffset的常规查找手段就可以得出结果了。一个复杂的应用，可以拆解成多个小应用，你只要把多个小应用逐个逐个的实现，再拼合成一个大应用。这也就要求你对常用函数能有所掌握了，如果你理解的多，解题的思路也就多。我可以肯定你现在是一知半解，甚至还没理解，哈你仅仅是通过了这个应用，了解了一个LOOKUP的解题的一个方法而实质的原理，为什么要这样子做的原因，你肯定不理解的在乱序的状态下，LOOKUP得出来的结果很有可能是莫名其妙的乱序仍旧遵循二分法规则，结果不会乱，看看这个就知道了虽然有满足条件的三个1，但最终还是第一个1满足条件，同样只需要比较两次，第一次取5，第二取1。A1:A9是一个区域，有9个数字，查找值59,为什么不是45呢？而是10！在查找时，首先要确定一个“二分点”，你可以理解为“中点” 。因为是9