写在前面：
Excel中对公式的部分内容进行试算的快捷键是F9，有助于理解嵌套公式里各部分的含义。数组公式结束编辑时要用【Ctrl+Shift+回车】三键，切记！
Excel里边根据一个值去查找列表内另一个对应的值，一般会想到用Vlookup或者INDEX+MATCH这对黄金搭档。今天把查找匹配的方法来个合集。废话不多说，上菜！
最近在看金庸先生所著《倚天屠龙记》，根据书中描述的各人战力杜撰了以下数据这里是武当七侠的名单和他们各自的功力等级
Excel数据查找匹配大合集
现在以查找张翠山的功力等级来说明查找匹配的手段
Excel数据查找匹配大合集
下面听我一一道来：NO.1 =VLOOKUP(E2,$B$2:$C$8,2,0)Vlookup比较简单，江湖上成名已久的侠士。第一个参数是要查找的值（张翠山），第二个参数是查找区域即在哪里查找（B2:C8),第三个参数是返回的结果（对应的功力等级）在查找区域的第几列（这个参数是相对位置，这里是2）第四个参数是查找类型，0表示精确查找。
NO.2 =LOOKUP(1,0/($B$1:$B$8=E2),$C$1:$C$8)然后是Vlookup的大哥，lookup，这位高手神龙见首不见尾，结合逻辑值和数组可以变换出很多招式，vlookup返回的是第一个满足条件的值，lookup返回的是最后一个满足条件的值.这里的用法是其中很经典的一种。解释下：公式中的第二个参数中【($B$1:$B$8=E2)】计算结果是【FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE】
然后用0除以这个数组，0除以0返回错误值（因为0不能是分母，数学常识），0除以1等于0得到新数组【#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!】即源数据中姓名区域中等于【张翠山】的值返回0，其余返回错误值。然后用1作为查找的值在构建的数组中查找，找不到1，所以返回最接近1的值（相比错误值）0所对应的数据，这样就实现查找匹配啦。
Excel数据查找匹配大合集
NO.3 =INDEX($C$1:$C$8,MATCH(E2,$B$1:$B$8))搞明白了上一个，下边的就好理解了INDEX+MATCH这组黄金搭档很经典啊，MATCH计算【张翠山】在姓名区域中的相对位置，INDEX用MATCH的计算结果作为参数，返回【功力等级】区域中相应位置的值，搞掂！！
NO.4 =OFFSET(C1,MATCH(E2,$B$1:$B$8)-1,,,)然后是轻功高手OFFSET登场，先用MATCH计算【张翠山】在姓名区域中的相对位置，跟上边那个一样啦，然后减掉1,得出的结果作为OFFSET偏移的行数，然后三个逗号省略掉另外几个参数。是不是没太懂？再想想呗~~~
NO.5 =SUMIF($B$1:$B$8,E2,$C$1:$C$8)轮到SUMIF了，条件求和函数，对满足定义条件的数据进行求和运算本例中这样理解：第一个参数是条件判断区域（姓名），第二个参数是判断条件（张翠山），第三个参数是求和的区域（功力等级）。公式的含义就是对姓名中是张翠山的那一项对应的功力等级进行求和。这样就得出结果了！当然这个方法的前提是在姓名中【张翠山】只有一个，且要求的【功力等级】是数字。下边的两个也是同样的前提！
Excel数据查找匹配大合集
NO.6 =SUM(($B$2:$B$8=E2)*$C$2:$C$8)Excel江湖中最低调的SUM要露一手啦【($B$2:$B$8=E2)】部分计算之后返回数组{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}（选中公式按下F9即可看到）会发现只有【张翠山】对应的那一行返回TRUE,然后乘上数组{70;70;60;50;40;40;30},就会返回新数组{0;0;0;0;40;0;0}，然后SUM再对结果进行求和计算就可以得到想要的结果啦（张翠山的功力等级）
NO.7 =SUMPRODUCT(—($B$2:$B$8=E2),$C$2:$C$8)最后一个是SUM的远房亲戚SUMPRODUCT，这位仁兄是数组函数，SUMPRODUCT汉语意思是:乘积之和，在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 就会返回错误值 #VALUE!在这里的用法跟上边的SUM相似，也是判断条件返回逻辑值组成的数组{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}，然后用两个负号连接这个数组将它转化成{0;0;0;0;1;0;0}作为SUMPRODUCT的第一个参数，{70;70;60;50;40;40;30}作为第二个参数；SUMPRODUCT会将这两个数组中的数值对应相乘并求和。这样就得到【张翠山】的功力等级了！
好啦，今天的教程有点长额，而且有点烧脑，多看几遍学习下解决问题的思路，举一反三！
网友评论