大家好,今天我们来讲讲那些和VLOOKUP功能一样的函数们,但是却没有VLOOKUP函数那么有名气,所有总是嫉妒VLOOKUP函数。
VLOOKUP函数大家肯定都很熟悉了。VLOOKUP函数是一个查询类函数。
VLOOKUP函数的含义是在表格的首列中查找特定值,并返回该行中指定列的值。
VLOOKUP函数语法:
=VLOOKUP(要查找的值, 查找区域, 返回值所在的列数, [精确匹配或模糊匹配])
我们用最简约的数据进行举例讲解。如下图所示:
数据源区域为A1:B6,A列为编码,B列为产品名称。查询区域为D1:E3,我们想要依据D列的编码,在数据源中查询出产品名称,显示在E列。
最有优势,名气最大的VLOOKUP函数:
=VLOOKUP(D2,$A$2:$B$6,2,0)
精确查询D2单元格的编码bv231,在数据源查找区域$A$2:$B$6中第2列所对应的产品名称,返回“电风扇”。下拉填充公式即可。
那些嫉妒VLOOKUP的函数们!既有单独的函数,又有函数嵌套组合。
单独类函数
输入XLOOKUP函数公式:
=XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6)
XLOOKUP(查找的值,查找范围,返回范围,找不到返回值,查询模式)
查找在$A$2:$A$6这个查找范围内的查找值D2,返回$B$2:$B$6区域内对应的产品名称,返回“电风扇”。下拉填充公式即可。
输入FILTER函数公式:
=FILTER($B$2:$B$6,D2=$A$2:$A$6)
FILTER(筛选返回区域,筛选条件)
FILTER函数筛选返回$B$2:$B$6区域的产品名称,但是只有符合条件D2单元格的编码等于$A$2:$A$6区域的编码时,才会筛选$B$2:$B$6对应的行的数据。
嵌套组合类函数
INDEX+MATCH函数组合(最常见的,大家运用比较广泛的)。
输入函数公式:
=INDEX($B$2:$B$6,MATCH(D2,$A$2:$A$6,0))
MATCH函数返回D2在$A$2:$A$6中的精准位置为4(位于该区域的第4个单元格),然后用INDEX函数返回$B$2:$B$6区域内第4行,第1列(列号与第一参数重合,可选省略)交叉位置的值为“电风扇”。最后下拉填充公式即可。
TAKE+MATCH函数组合(比较偏门,小编目前能力范围内总结的)
输入函数公式:
=TAKE(TAKE($B$2:$B$6,MATCH(D2,$A$2:$A$6,0)),-1)
TAKE函数用于从数组中保留部分行。
MATCH函数返回D2在$A$2:$A$6中的精准位置为4。然后第一层TAKE函数,提取B2:B6区域的前4行{剃须刀;电饭煲;转笔刀;电风扇},最后最外层第二层TAKE函数,提取{剃须刀;电饭煲;转笔刀;电风扇}的倒数第一行数据“电风扇”。下拉填充公式即可。
OFFSET+MATCH函数组合(比较偏门,小编目前能力范围内总结的)
输入函数公式:
=OFFSET($B$1,MATCH(D2,$A$2:$A$6,0),)
OFFSET函数用于从基准位置,按照指定的行数或列数偏移引用,返回对单元格或单元格区域的引用。
OFFSET(基准位置,偏移行数,偏移列数,[引用高度],[引用宽度])
MATCH函数返回D2在$A$2:$A$6中的精准位置为4。然后用OFFSET函数以$B$1为基点,向下偏移4个单元格,取值“电风扇”。下拉填充公式即可。
CHOOSEROWS+MATCH函数组合(比较偏门,小编目前能力范围内总结的)
输入函数公式:
=CHOOSEROWS($B$2:$B$6,MATCH(D2,$A$2:$A$6,0))
CHOOSEROWS函数用于返回数组中的部分行
MATCH函数返回D2在$A$2:$A$6中的精准位置为4。用CHOOSEROWS函数返回数组$B$2:$B$6区域中的第4行“电风扇”。下拉填充公式即可。