VLOOKUP:多列中查找一列
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
- lookup_value:待查找的值
- table_array:查找范围。范围的第一列必须为lookup_value所在的列。范围多次使用时,可将其命名,类似于设置变量
- col_index_num:取匹配到的第几列,从1开始
- range_lookup:是否精确匹配。 TRUE或为空,返回近似匹配值(或小于lookup_value的最大值)。FALSE,则返回精确匹配值,如果匹配不到返回非法值#N/A。FALSE支持通配符*和?
例如:
VLOOKUP(F3, $B$2:$D$8, 3, FALSE) #根据F3中的商品ID查找价格
VLOOKUP(F3, $B$2:$D$8, 3) #第三个参数默认
VLOOKUP(F:F, $B$2:$D$8, 3, FALSE) #根据F列中的商品ID找价格
VLOOKUP(F:F, Sheet3!$B$2:$D$8, 3, FALSE) #在其他Sheet页中查找
VLOOKUP(F3, IF(F3<2000, PriceTbl1, PriceTbl2), 3,FALSE) #从两个表中查找
IFERROR(VLOOKUP(F3, PriceTbl1, 3), VLOOKUP(F3, PriceTbl2, 3)) #从PriceTbl1中查找不到时,从PriceTbl2中查找
IFERROR(VLOOKUP(F3, PriceTbl1, 3), "No Product!") #从PriceTbl1中查找不到时,显示指定文字
这里B2和D8使用$前缀,可以防止往下自动填充时这个范围自动增加。
HLOOKUP:多行中查找一行
HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
与VLOOKUP类似,不过是按行查找,使用不多
LOOKUP:在一行或一列中查找,返回另一行或另一列中的值
VLOOKUP指定区域时,待查找的值必须在第一列,所以VLOOKUP无法往查找列之前查询。使用LOOKUP则可以指定任意列。
例如:
同样,在不同Sheet页查找时,可以指定Sheet名称
LOOKUP(D11, Sheet3!$C$2:$C$8, Sheet3!$B$2:$B$8)
命名区域
多次重复使用的区域,可以使用名称管理器将其命名为一个名称。这样在需要使用区域的地方,直接使用名称即可。
名称创建方式:快捷键Ctrl+F3或鼠标点击公式->名称管理器,点击新建,输入名称,选择引用区域,确定。
使用:
VLOOKUP(F3, PriceTbl1, 2, FALSE)
MATCH:查询匹配值在区域中的位置
MATCH(lookup_value, lookup_array, match_type)
- lookup_value:待查找值
- lookup_array:查找区域。一般为一行或一列。
- match_type:可以取-1、0、1三个值。 -1:大于等于lookup_value的最小值。0:精确匹配。 1:小于等于lookup_value的最大值。 默认值为1
使用举例:
INDEX:按位置取单元格
INDEX(array, row_num, col_num)
- 取array中,第row_num行,col_num列的元素
INDEX(PriceTbl1, 2, 3) #取PriceTbl1中第2行,第三列的单元格
INDEX(B2:B8, 3) #取B2到B8中,第三个单元格,即B4
INDEX(B2:D2, 2) #取B2到D2中,第二个单元格,即C2
MATCH与INDEX双列查找
INDEX(D2:D8, MATCH(G1&G2, B2:B8&C2:C8)) #根据G1和G2的值,从B列和C列中匹配出D列的值