工作中,大家使用VLOOKUP函数查询数据是很常见的事,但有时候,一个VLOOKUP函数可能无法解决更多的问题,这时候学习下其他函数组合也很必要! 下图中,如果通过VLOOKUP函数查找E3:E5单元格姓名对应的销售额,公式应该怎么写? F3公式:=VLOOKUP(E3,$B$3:$C$10,2,0) 公式解析:
E3:表示要查找的内容。 $B$3:$C$10:表示以查找内容为首列的查找区域。 2:表示我们要返回的结果在查找区域中属于第2列。 0:表示精确查找。 可以代替VLOOKUP函数解决这道题的函数有哪些? 一、LOOKUP函数。
F3公式:=LOOKUP(1,0/($B$3:$B$10=E3),$C$3:$C$10) 公式解析: $B$3:$B$10=E3:判断B3:B10单元格区域中的内容是否跟E3单元格内容相等,若相等,返回TRUE,否则,返回FALSE,此时返回一个TRUE和FALSE的数组:{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE} 0/($B$3:$B$10=E3):用0/TRUE,0/FALSE会返回一个0和错误值的数组:{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!} 整个公式的意思是:用LOOKUP函数查找数字1在{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!}这个数组中的位置,但始终查不到,于是返回最后一个0值的位置,这时返回相对应的C3:C10单元格区域中对应的值。 二、INDEX+MATCH函数。F3公式:=INDEX($C$3:$C$10,MATCH(E3,$B$3:$B$10,0)) 公式解析:
MATCH(E3,$B$3:$B$10,0):精确查找E3单元格内容在查找范围B3:B10中的位置,返回的结果为:5。 $C$3:$C$10:是INDEX函数的查找区域。 整个公式的意思就是:在C3:C10查找区域内,返回第5行对应单元格的内容。 三、OFFSET+MATCH函数。F3公式:=OFFSET($B$2,MATCH(E3,$B$3:$B$10,0),1) 公式解析:精确查找E3单元格内容在查找范围B3:B10中的位置,返回的结果为:5。
MATCH(E3,$B$3:$B$10,0):精确查找E3单元格内容在查找范围B3:B10中的位置,返回的结果为:5。 $B$2:是OFFSET函数的基准位置。 整个公式的意思是:以B2单元格为基准,向下偏移5行,向右偏移一行,最后两个参数省略,默认一个单元格的高度。即C7单元格的位置。 四、INDIRECT+MATCH函数。F3公式:=INDIRECT('C'&MATCH(E3,$B$2:$B$10,0)+1) 公式解析;
MATCH(E3,$B$2:$B$10,0):精确查找E3单元格内容在查找范围B3:B10中的位置,返回的结果为:6。 MATCH(E3,$B$2:$B$10,0)+1:加1是因为单元格的内容是从第2行开始写的,而行号从1开始,加1才能得到正确的结果,这里返回的结果为:7。 =INDIRECT('C'&MATCH(E3,$B$2:$B$10,0)+1):使用&文本连接符将C与MATCH函数返回的位置连接起来,相当于公式=INDIRECT('C7'),也就是引用C7单元格的内容,所以结果为:195。 五、HLOOKUP+TRANSPOSE函数。
F3公式:{=HLOOKUP(E3,TRANSPOSE($B$3:$C$10),2,0)} 公式解析: TRANSPOSE($B$3:$C$10):将纵向的单元格区域B3:C10转成横向的单元格数据。使用该函数的原因是因为HLOOKUP函数只能横向查找。 {=HLOOKUP(E3,TRANSPOSE($B$3:$C$10),2,0)}: E3:查找值。 TRANSPOSE($B$3:$C$10):查找区域。 2:返回值在查找区域的第2行。 0:精确查找。 注意:该公式中的双大花括号并不是手动输入的,而是公式写完之后按组合键“Ctrl+Shift+Enter”自动生成的。 |
|
来自: xxcc140 > 《excel函数教程》