Excel高级查找

一、正向查找

从前往后查找，常见的是LOOKUP、VLOOKUP、HLOOKUP等

1、LOOKUP

从单行货单列或从数组中查找一个值，条件是先后兼容性。

=LOOKUP(lookup_value,lookup_vector,[result_vector])

=LOOKUP(lookup_value,array) 

2、VLOOKUP

搜索表区域首列满足条件的原始，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值，默认情况下，表是以升序排序的。

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

=VLOOKUP(要匹配的值,匹配区域,区域中相对行,匹配类型)，其中匹配类型为，0或False为精确匹配；1或True为模糊匹配  

=VLOOKUP(H2,B2:E7,4,0)，返回H2的值“C”在B2:E7中的第4列，精确匹配。

 

3、HLOOKUP

定义：搜索数组区域首行满足条件的原始，确定待检索单元格在区域中的序列号，再进一步返回选定单元格的值

公式：=HLOOKUP(lookup_value,table_array,row_index,[range_lookup])，

解读：=HLOOKUP(要查找的值，要查找的区域，返回的行，匹配类型)，其中匹配类型为，0或False为精确匹配；1或True为模糊匹配  

实例：=HLOOKUP(H2,B1:F7,4,0)，返回B1:F7区域中，“3季度”所在的列，第4行，精确匹配。

4、Index+Match组合

=INDEX（要查找的区域，相对行数，相对列数）

=INDEX(C2:F7,3,4)返回相对区域第3行第4列的值

 

=MATCH(要查找的值，目标区域，匹配类型)，其中匹配类型，1:小于，0:精确匹配，-1:大于

=MATCH("C",B2:B7,0)，返回“C”在B2:B7这个一维数组的位置，得到行。

=MATCH("3季度",C1:F1,0)，返回“3季度”在C1:F1这个一维数组的位置，得到列。

 

二、逆向查找

题目：查找F2列中的值“E”在B列“编号”中对应的值。

1、方法一：使用index+match实现逆向查找

使用公式：=INDEX(B2:B7,MATCH(F2,C2:C7,0),1)

 

1）使用match函数匹配出F2在C2:C7区域中对应的行号（相对），得到5

　　函数定义：返回符合特定值特定顺序的项在数组中的相对位置

　　函数说明：

　　match(lookup_value,lookup_array,[match_type]),对应解读：match(要查找的值，要匹配的区域/数组，匹配类型)，其中匹配类型，1:小于，0:精确匹配，-1:大于

　　使用公式：=MATCH(F2,C2:C7,0) 

2）使用index函数匹配出C2:C7区域中，第5行，第1列的值

　　函数定义：在给定的单元格区域中，返回特定行列交叉处单元格的值或引用

　　函数说明：

　　index(array,row_num,[column_num])，对应解读：index(要查找的区域，相对行号，相对列号)，其中行列都从“1”开始。都是必选参数。

　　index(reference,row_num,[column],[area_num])，对应解读：？？

　　使用公式：=INDEX(C2:C7,5,1) 

3）使用index+match组合实现逆向查找。

使用公式：=INDEX(B2:B7,MATCH(F2,C2:C7,0),1)

index返回B2:B7区域中 行=（match）F2列的值在C2:C7中的相对行，列=1 

2、方法二：使用VLOOKUP

函数定义：搜索表区域首列满足条件的原始，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。默认情况下，表是以升序排序的。

函数说明：=VOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

对应解读：VLOOKUP(要查找的值，要查找的区域，返回区域的列相对列号，匹配类型)，其中匹配类型为，0或False为精确匹配；1或True为模糊匹配 

使用函数：=VLOOKUP(F2,IF({1,0},C2:C7,B2:B7),2,0)  ##注意：这里掉换的两个数据必须有边界，不能全选一列，否则会提示“内存不足”

嵌套函数（IF）翻译：=VLOOKUP(F2,{"A","Y1019";"B","Y1020";"C","Y1021";"D","Y1022";"E","Y1023";"F","Y1024"},2,0)，使用IF前为Y1019:A，使用IF后为A:Y1019

嵌套函数解读：函数中IF({1,0},C2:C7,B2:B7)的作用是将两个区域C2:C7和B2:B7顺序调换，形成数组，提供给VLOOKUP作为正向匹配使用。 

3、使用LOOKUP

搜索表区域首列满足条件的原始，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值，默认情况下，表是以升序排序的。

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

=VLOOKUP(要匹配的值,匹配区域,区域中相对行,匹配类型)，其中匹配类型为，0或False为精确匹配；1或True为模糊匹配  

=VLOOKUP(H2,B2:E7,4,0)，返回H2的值“C”在B2:E7中的第4列，精确匹配。

 

三、多条件查找

1、方法一：使用INDEX+MATCH函数组合

=INDEX($C$2:$F$7,MATCH(H2,$B$2:$B$7,0),MATCH(I2,$C$1:$F$1,0))

查找C2:F7区域，H2对应值“C”所在的行，I2对应值“3季度”所在的列。

2、方法二：使用VLOOKUP函数 

=VLOOKUP(H2,$B$2:$F$7,MATCH(I2,$C$1:$F$1,0)+1,0),对应解读：VLOOKUP(查找H2的值，在B2：F7的区域里，“4季度”所在的列,精确匹配)

这里=MATCH(I2,$C$1:$F$1,0)+1的返回值为4，即查找I2，在C1:F1区域的位置，从0开始计算。

 

四、模糊查找 

1、方法一：使用INDEX+MATCH函数组合

=MATCH("*"&E3&"*",$B$2:$B$5,0)，模糊匹配，返回含有E2值关键字内容，在B2:B5区域的行。

=INDEX($C$2:$C$5,MATCH("*"&E2&"*",$B$2:$B$5,0),1)，返回C2:C5区域，第5行1列

 

2、方法二：使用VLOOKUP函数

 =VLOOKUP("*"&E2&"*",$B$2:$C$5,2,0)，查找含有E列值内容的B2:C5区域的第2列。

 

五、补充：XLOOKUP（目前仅OFFICE 365支持）

1、XLOOKUP正向查找

=XLOOKUP(要查找的值，要查找的区域，返回的区域)

=XLOOKUP(F2,B3:B6,C3:C6)，查找F2的值在B3:B6区域查询，返回C3:C6对应的姓名。

2、XLOOKUP反向查找

 =XLOOKUP(要查找的值，要查找的区域，返回的区域)

=XLOOKUP(F2,C3:C6,B3:B6)，查找F2的值在C3:C6区域查询，返回B3:B6对应的姓名。

 

3、XLOOKUP返回多列

=XLOOKUP(要查找的值，要查找的区域，返回的多列区域)

=XLOOKUP(H2,B3:B6,C3:E6)，查找H2的值在B3:B6区域查询，返回C3:E6对应的值。

4、XLOOKUP自动除错

=XLOOKUP(要查找的值，要查找的区域，返回的区域，错误定义) ##如果不定义，返回的是#N/A

=XLOOKUP(H2,B3:B6,C3:E6,"无此数据")，查找H2的值在B3:B6区域查询，返回C3:E6对应的值,没有数据则返回“无此数据”。

  

5、XLOOKUP模糊查找

=XLOOKUP(F2,B2:B8,C2:D8,0,-1)查找B列中查询F2的值，第5个参数-1，表示如果找不到它，就从返回区域中返回下一个较大的值（不用事先对查询区域进行排序）

 

如果查询中使用了通配符，第5个参数一定要选择2

=XLOOKUP(F2&"*",B2:B8,C2:D8,0,2)

 

XLOOKUP函数还有第6个参数，如果数据源中有2个符合条件的结果时，设置为-1时返回最后一个结果。