快捷搜索:  汽车  科技

excel中vlookup函数多行多列查找(HLookUp函数的使用方法及实例)

excel中vlookup函数多行多列查找(HLookUp函数的使用方法及实例)E、如果 Range_LookUp 设置为 False 且 LookUp_Value 为文本,则在 LookUp_Value 中可以使用通配符问号(?)和星号(*);问号匹配任意一个字符,星号匹配任意一个或一串字符;如果要查找问号或星号,需要加转义字符~,例如查找问号,应该这样写 ~?,星号应该这样写 ~*。D、Range_LookUp 为可选项,它有两个选项,一个为 True(近似匹配),另一个为 False(精确匹配);如果 Range_LookUp 省略或为True,若找不到 LookUp_Value,则返回小于 LookUp_Value 的最大值。 如果 Range_LookUp 为 False,若找不到 LookUp_Value,则返回错误值 #N/A。A、LookUp_Value 需要在选定区域的第一行查找,如果选定区域不包括表格的第一行,则不是在表格第一行查找;例如选定区域为

在 Excel 中,VLookUp和LookUp函数用于坚直查找,而HLookUp函数用于水平查找,它通常在表格第一行查找,但如果选定区域不包括表格第一行,则不在表格第一行查找。HLookUp函数的参数与VLookUp函数一样也有四个,它们三个参数相同,只有一个参数不同,即一个是返回行号,另一个是返回列号当对数值、字母和逻辑值进行查找且匹配选项设置为模糊匹配时,需要对查找范围的第一行排序,否则HLookUp函数可能返回不正确的结果;当找不到值,HLookUp函数返回错误值。HLookUp函数可与Match函数组合使用,以动态返回不同行的值。

一、Excel HLookUp函数语法

1、表达式:HLOOKUP(LookUp_VALUE Table_Array Row_Index_Num [Range_LookUp])

中文表达式:=HLOOKUP(查找值 查找区域 返回行号 [匹配选项])

2、说明:

A、LookUp_Value 需要在选定区域的第一行查找,如果选定区域不包括表格的第一行,则不是在表格第一行查找;例如选定区域为 B2:C9,则应该在 B2:C2 查找 LookUp_Value,也就是 B2:C2 为第一行,因此千万不要把表格第一行认为是查找行。

B、如果 Range_Lookup 设置为 True,Table_Array 第一行的数值、字母和逻辑值必须从左到右按升序排列,否则 HLookUp 可能返回不正确的数值;如果 Range_LookUp 设置为 FALSE,Table_Array 不需排序;Table_Array 中的文本不区分大小写。

C、当 Row_Index_Num 为 1 时,返回 Table_Array 第一行的值;当 Row_Index_Num 为 2 时,返回 Table_Array 第二行的值;其它的以此类推。如果 Row_Index_Num 小于 1,则 HLookUp 返回值错误 #VALUE!;如果 Row_Index_Num 大于 Table_Array 的行数,则 HLookUp 返回引用错误值 #REF!。

D、Range_LookUp 为可选项,它有两个选项,一个为 True(近似匹配),另一个为 False(精确匹配);如果 Range_LookUp 省略或为True,若找不到 LookUp_Value,则返回小于 LookUp_Value 的最大值。 如果 Range_LookUp 为 False,若找不到 LookUp_Value,则返回错误值 #N/A。

E、如果 Range_LookUp 设置为 False 且 LookUp_Value 为文本,则在 LookUp_Value 中可以使用通配符问号(?)和星号(*);问号匹配任意一个字符,星号匹配任意一个或一串字符;如果要查找问号或星号,需要加转义字符~,例如查找问号,应该这样写 ~?,星号应该这样写 ~*。

二、Excel HLookUp函数的使用方法及实例

(一)省略参数 Range_LookUp 的实例

1、假如要在第一行查找“姓名”。双击 A9 单元格,把公式 =HLOOKUP("姓名" A1:C9 5) 复制到 A9,按回车,返回“财务部”;双击 A9,在 5 后加逗号( ),按回车,返回“李秀丽”;双击 A9,在逗号后输入 true,按回车,返回“财务部”;再次双击 A9,把 true 改为false,按回车,返回“李秀丽”;操作过程步骤,如图1所示:

excel中vlookup函数多行多列查找(HLookUp函数的使用方法及实例)(1)

图1

2、公式说明:

A、公式 =HLOOKUP("姓名" A1:C9 5) 中,“姓名”为查找值,A1:C9 为查找区域,5 为返回行号,公式省略了参数 Range_LookUp;公式的意思是,在第一行(指选区域 A1:C9 的第一行)查找“姓名”,返回第 5 行的值。

B、公式 =HLOOKUP("姓名" A1:C9 5) 与 =HLOOKUP("姓名" A1:C9 5 True) 都返回“财务部”,说明第三个参数 5 后不加逗号,参数Range_LookUp 默认选择 True;公式 =HLOOKUP("姓名" A1:C9 ) 与 =HLOOKUP("姓名" A1:C9 5 False) 都返回“李秀丽”,说明第三个参数 5 后加逗号,Range_LookUp 默认选择 False。

(二)参数 Row_Index_Num 小于 1 与大于查找范围行数的实例

1、双击 A9 单元格,把公式 =HLOOKUP("姓名" A1:C9 0 1) 复制到 A9,按回车,返回值错误 #VALUE!;双击 A9,把 0 改为 9,按回车,返回 0;再次双击 A9,把 9 改为 10,按回车,返回引用错误 #REF!;操作过程步骤,如图2所示:

excel中vlookup函数多行多列查找(HLookUp函数的使用方法及实例)(2)

图2

2、公式说明:

公式 =HLOOKUP("姓名" A1:C9 0 1) 中,返回行号 Row_Index_Num 为 0,返回值错误 #VALUE!,说明 Row_Index_Num 小于 1 返回值错误;当把 0 改为 9 后,返回第 9 行的值,也就是返回公式所在行的值,即返回 0;当把 9 改为 10 后,Row_Index_Num 已经超过选定区域的最大行数,因而返回引用错误。

(三)找不到 LookUp_Value,返回小于 LookUp_Value 的最大值实例(查找范围第一行需要排序)

1、假如要查找销量 589。双击 B8 单元格,把公式 =HLOOKUP(589 B2:E7 4 TRUE) 复制到 B8,按回车,返回 679;双击 B2,把 539 改为 639,B8 中的返回值变为 512;操作过程步骤,如图3所示:

excel中vlookup函数多行多列查找(HLookUp函数的使用方法及实例)(3)

图3

2、公式 =HLOOKUP(589 B2:E7 4 TRUE) 说明:

B2:E7 为查找范围,这里的第一行是指 B2:E2(即非表格的首行),也就是在表格的第二行查找 589;由于找不到 589,所以选择小于等于 589 的最大值 551,然后返回该列第 4 行的值 679;由于这里的 Range_LookUp 参数设置为 True,所以查找行 B2:E2 需要从左到右按升序排列,否则可能返回不正确的值,如把 539 改为 639 后,返回值变 512,因为 589 小于 639,返回小于 589 的值只有 467,再返回同列第 4 行的值,所以返回 512。

(四)找不到 LookUp_Value,返回错误值 #N/A 实例(查找范围第一行不需排序)

1、同样要查找销量 589。双击 B8 单元格,把公式 =HLOOKUP(589 B2:E7 4 FALSE) 复制到 B8,按回车,返回数字错误值 #N/A;双击 B2,把 639 改为 539,B8 中的值仍是 #N/A;操作过程步骤,如图4所示:

excel中vlookup函数多行多列查找(HLookUp函数的使用方法及实例)(4)

图4

2、公式 =HLOOKUP(589 B2:E7 4 FALSE) 说明:

第一次执行时,查找行 B2:E2 没有从左到右按升序排列,公式返回错误值 #N/A;当把 639 改为 539 后,查找行 B2:E2 已经按升序排列,但 B8 仍然返回错误值 #N/A,说明查找行不管按不按升序排列,当 Range_LookUp 参数设置为 False 时,找不到查找值,HLookUp函数都返回错误值 #N/A,即精确匹配时,查找行不需排序。

(五)查找值有通配符问题(?)和星号(*)的实例

1、查找值有通配符问题(?)

A、假如要查找以任意两个字开头且以“衬衫”结束的销量。双击 B4 单元格,把公式 =HLOOKUP("??衬衫" B1:F3 3 FALSE) 复制到 B4,按回车,返回 962,操作过程步骤,如图5所示:

excel中vlookup函数多行多列查找(HLookUp函数的使用方法及实例)(5)

图5

B、公式 =HLOOKUP("??衬衫" B1:F3 3 FALSE) 中“??衬衫”为查找值,一个问号表示一个字符,意思是以任意两个字开头且以“衬衫”结尾;查找值中有通配符,Range_LookUp 参数需要设置为 False。

2、查找值有通配符星号(*)

A、假如要查找以任意字符任意个字开头且以“T恤”结尾的销量。双击 B4 单元格,把公式 =HLOOKUP("*T恤" B1:F3 3 FALSE) 复制到 B4,按回车,返回 640;再次双击 B4,把“*T恤”改为“*白*”,按回车,返回 458;操作过程步骤,如图6所示:

excel中vlookup函数多行多列查找(HLookUp函数的使用方法及实例)(6)

图6

B、公式 =HLOOKUP("*T恤" B1:F3 3 FALSE) 中“*T恤”为查找值,意思是以任意一个或多个字符开头且以“T恤”结尾,D1 和 E1 中的值都符合条件,但 HLookUp函数选择第一个符合条件的值,因此返回 640;把“*T恤”改为“*白*”后,B1 和 D1 中的值都符合条件,也选择第一符合条件的值,所以返回 458。

三、Excel HLookUp Match 组合与非首行实例

1、假如要查找“白色T恤”的“2月销量(件)”。双击 F8 单元格,把公式 =HLOOKUP($F$7 A1:D5 MATCH($E$8 A1:A5 0) 0) 复制到 F8,按回车,返回 543;右键第一行行号 1,在弹出的菜单中选择“插入”,则在首行插入一行,公式所在的行也自动下移,F8 变 F9,公式中的各引用单元格也自动加 1,F9 中的值仍为 543;操作过程步骤,如图7所示:

excel中vlookup函数多行多列查找(HLookUp函数的使用方法及实例)(7)

图7

2、公式 =HLOOKUP($F$7 A1:D5 MATCH($E$8 A1:A5 0) 0) 说明:

A、$F$7 为对 F7 单元格的绝对引用,即无论是往下拖还是往右拖,F7 都不会变为 F8、F9、……或 G8、H8、……,但插入行时,$F$7 会自动变为 $F$8;$E$8 与 $F$7 是一个意思。

B、MATCH($E$8 A1:A5 0) 是在 A1:A5 中查找 E8(即“2月销量(件)”),0 表示精确匹配,最后返回 4。

C、则公式变为 =HLOOKUP("白色T恤" A1:D5 4 0),接着在第一行查找“白色T恤”,在 D1 单元格找到,返回该列第 4 行的值,正好是543;HLOOKUP 公式最后一个参数 0 表示精确匹配。

D、在首行插入一行后,公式变为 =HLOOKUP($F$8 A2:D6 MATCH($E$9 A2:A6 0) 0),此时查找区域变为 A2:D6,第一行也随之变为 A2:F2,也就是在表格的第二行查找。

提示:如果要往下拖,公式的 A1:D5 要变为绝对引用,即 $A$1:$A$5,公式变为 =HLOOKUP($F$7 $A$1:$D$5 MATCH($E$8 A1:A5 0) 0)。

猜您喜欢: