您的位置: 首页 > EXCEL技巧 > Excel函数 >

类似VLOOKUP查询第N个相同值的公式

时间:2013-12-13 整理:docExcel.net

大家知道,Excel中的VLOOKUP函数可以在区域的第一列中查找指定的值,然后返回与该值同行的其他列的数据。但如果第一列中有多个相同值时,VLOOKUP函数只能返回第一个。要返回第N个相同值所对应的其他列数据,可以用下面的公式,例如数据在A2:A25区域,共有24条记录,其中与销售人员“王冬”对应的有多条记录,要查找第二个“王冬”所对应的销售额。

在某个单元格中输入数据公式:

=INDEX(D2:D25,SMALL(IF(B2:B25="王冬",ROW(1:24)),2))

公式输入完毕按Ctrl+Shift+Enter结束,下同。公式返回“555.28”。

如果要查找第一个“王冬”所对应的销售额,可以直接用VLOOKUP函数或类似上面的公式,如:

=VLOOKUP("王冬",B2:D25,3,0)

该公式输入完毕直接按回车键,或数组公式:

=INDEX(D2:D25,MIN(IF(B2:B25="王冬",ROW(1:24))))

公式都返回“1981.55”。但后面的公式能够向左边查询,这点VLOOKUP无法实现。如查找第一个“王冬”对应的“销售日期”:

=INDEX(A2:A25,MIN(IF(B2:B25="王冬",ROW(1:24))))

公式返回“2010-7-16”。

类似地,查找倒数第二个“王冬”的销售额:

=INDEX(D2:D25,LARGE(IF(B2:B25="王冬",ROW(1:24)),2))

查找最后一个“王冬”的销售额:

=INDEX(D2:D25,LARGE(IF(B2:B25="王冬",ROW(1:24)),1))

或用LOOKUP函数,该公式输入完毕直接回车即可,无需按输入数组公式的组合键:

=LOOKUP(1,0/(B1:B25="王冬"),D1:D25)

两个公式都返回“639.50”。最后一个公式中,由于LOOKUP函数要求第二个参数“lookup_vector”必须以升序排列,公式的第二个参数为“0/(B1:B25="王冬")”产生一个仅包含“0”和错误值的数组,因而能够返回正确的结果。另外,由于其搜索的值为“1”,在“lookup_vector”中找不到,会返回最后一个“0”对应在“D1:D25”中的值。

类似VLOOKUP函数能区分大小写查询的公式 问题:类似VLOOKUP函数能区分大小写查询的公式
回答:在Excel中用VLOOKUP、HLOOKUP、LOOKUP等函数进行查询时,是不区分大小写的。例如在下图中要B列中查找“108-d”所对应的数值。 如果用公式: =VLOOKUP( 108-d ,A2:B5,2,0) 将返回数值“10”,而不是“108-d”所对应的“210”。 如果要区...
类似VLOOKUP查找带星号文本的公式 问题:类似VLOOKUP查找带星号文本的公式
回答:大家知道,在Excel中用VLOOKUP函数进行查找时,如果第四个参数为FALSE且查找的内容为文本时,就可以使用通配符——问号 (?) 和星号 (*)。但如果所查找的文本中本身包含星号,直接使用VLOOKUP函数可能不会得到正确的结果。例如在...
类似VLOOKUP能返回多个查询值的数组公式 问题:类似VLOOKUP能返回多个查询值的数组公式
回答:在Excel中使用VLOOKUP函数可以在表格数组的首列查找指定的值,然后返回当前行中其他列的值。但表格数组第一列中有两个或多个值与查找值相匹配时,VLOOKUP函数只能返回第一个找到的值。如果要用公式返回与查找值相匹配的多...
突出显示Excel2013中不符合要求的行 问题:突出显示Excel2013中不符合要求的行
回答:...确定要设置格式的单元格,输入公式: =COUNTIF($A:$A,$A1)>VLOOKUP($A1,$E:$F,2,0) 点击格式按钮。 ③弹出设置单元格格式界面,点击填充选项卡。 ④选择一款颜色,确定。 ⑤这时会返回编辑格式规则界面,确定。 ⑥不符合规...
与VLOOKUP函数相反的左向查找公式 问题:与VLOOKUP函数相反的左向查找公式
回答:大家知道,在Excel中可以利用VLOOKUP函数根据区域中第一列的某个数据查找出其他列中对应行的数据。例如,在下图中要查找对应“值班日期”为“初五”的“姓名”,可以用公式: =VLOOKUP(A6,A1:D7,2,0) 但VLOOKUP函数只能向右查找...
避免VLOOKUP函数返回#NA错误的方法 问题:避免VLOOKUP函数返回#NA错误的方法
回答:在Excel中经常使用VLOOKUP函数来查找某个数据区域的第一列,并返回所查找数据在指定列中对应行的单元格值。在用VLOOKUP函数进行精确查找时,如果要查找的值不在数据区域的第一列中,VLOOKUP函数会返回#N/A错误。例如下图数据在...
相关推荐: