Excel - VLOOUP、XLOOKUP和HLOOKUP函数，以及通配符使用

 

如果你用Excel，但却不知道VLOOKUP函数，那你就大错特错了，这个函数非常有用，且用的很多。

当你需要在一个表或一个范围内按行查找东西时，可以使用VLOOKUP。例如，通过零件编号查找汽车零件的价格，或者根据雇员的ID查找雇员的名字。

在其最简单的形式中，VLOOKUP的函数说明：

=VLOOKUP(你要查找的内容，你要查找的位置，范围内的包含要返回的值的列号，返回近似或精确匹配--也就是1/TRUE或0/FALSE)。

最后一个匹配模式参数可以不输入，默认是TRUE或1，表示近似或模糊匹配，一般使用FALSE或0比较好。

操作步骤如下：

1，在单元格中输入=VLOOKUP(

2，然后输入要查找的值，可以是字面值常量或者某个单元格引用(点击某个单元格即可)

3，然后选中要查找的范围，注意可以选择多列，也可以选择一个区域，但第一列中要包含你想查找的值。

4，再输入要返回的值所在的列。

5，输入false，表示精确查找。

6，回车得到结果。

注意：

1，如果第一个参数，要查找的值是空字符串 “” ， 或者是引用的单元格没有值，是无法查找的，即使查找表格里第一列有空白的没有值的单元格。

2，VLOOKUP函数只能返回找到的第一个精确匹配的值，后面的就不会再查找了。

3，要查找的内容，可以是数值、单元格引用或者是字符串。

4，搜索范围，可以是一个Range，或者已命名的Range。

5，选择近似匹配模式时，先按照精确匹配查找，如果找不到，再采取近似匹配。

当使用近似匹配时，搜索范围的第一列的值要升序排列： ...-2, -1, 0, 1, 2,... , A-Z, FALSE, TRUE ，要从左到右或从上到下（用于HLOOKUP和VLOOKUP），否则返回不正确的结果。

近似匹配返回的结果是比查找值小一点的那个最近的值，但如果小于所有的值，则返回#N/A的错误值。

6，如果是精确匹配模式，第一列的值不需要排序，找不到返回#N/A。

7，查找匹配值时，大小写字符是相等的。

8，要返回的列的序号，从左边第一列开始计算。如果小于1，返回值为#VALUE!的错误值，如果大于整个表格的列数，返回#REF!错误。

9，查找值时可以使用通配符wildcard characters，下面有介绍。

HLOOKUP

VLOOKUP函数使用的更多，而HLOOKUP这个函数和VLOOKUP差不多用法，就是查找方向是水平的。

下面这张表格，查找某一列的第几行数据，就需要用到HLOOKUP：

=HLOOKUP("Axles", A1:C4, 2, TRUE) ，返回的就是第二行的值4。

直接查找的例子：

=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)

返回的就是c，这个二维数组是3x3的，直接在大括号中表示。

XLOOKUP

试着使用新的XLOOKUP函数，这是VLOOKUP的改进版，可以在任何方向上工作，并默认返回精确匹配，使其使用起来比前者更容易、更方便。

XLOOKUP在Excel 2016和2019中是不支持的。

XLOOKUP的优点：

1，VLOOKUP只能在列的方向上查找，而XLOOKUP可以从行或列的方向查找，所以XLOOKUP函数可以代替VLOOKUP和HLOOKUP函数。

2，VLOOKUP返回的值只能选择某一列，此列必须在查找内容的列后面，而且要计算出相对是第几列。

而XLOOKUP返回的行或列的位置是随意指定的，而且可以返回多行或多列。

3，XLOOKUP的参数多，功能也多，

语法：

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

此函数有多个参数，在输入这些参数时，参数之间用逗号分开。参数可以不赋值，这样如果有默认值，就会使用默认值，如果没有，那就会返回#N/A。

使用默认参数和正常参数的顺序没有要求，可以参数输入为空而使用默认值，之后的参数继续使用输入值。

XLOOKUP函数搜索一个范围或一个数组，然后返回它找到的第一个匹配项所对应的项目。如果不存在匹配项，那么XLOOKUP可以返回最接近的（近似的）匹配项。

参数说明：

参数	名称	说明
第一个	lookup_value	要查找的值
第二个	lookup_array	在什么范围或数组里查找
第三个	return_array	返回的数组或范围值
第四个	[if_not_found] 可选参数	如果没查到匹配的值，则返回此值。没找到也没提供此参数，则返回#N/A
第五个	[match_mode] 可选参数	指定查找所用的匹配模式： 0 - 精确匹配, 没找到则返回#N/A，为默认值 -1 - 精确匹配，如果没找到，返回下一个小一些的值 1 - 精确匹配，如果没找到，返回下一个大一些的值 2 - 使用通配符模式匹配， *, ? 和 ~这些符号都有特殊含义。
第五个	[search_mode] 可选参数	指定所使用的搜索模式： 1 - 从第一个数据项开始查找，为默认操作 -1 - 从最后一个数据项开始查找，逆向查询 2 - 使用二分查找法，依赖于被查找数据是升序排列的，否则会返回无效结果。 -2 - 使用二分查找法，依赖于被查找数据是降序排列的，否则会返回无效结果。

注意：

1，如果返回多个值，选择的返回结果是列时，则返回值是一行多列；选择结果是行时，则返回值是一列多行。

2，返回值的范围和查找值的范围要保持一致，也就是列或行的长度要相同。

一些例子如下：

查B列的值，返回D列。

查B列的值，返回C列和D列两列的值。

使用if_not_found参数。

选一列查询，选一列做返回值，没找到默认值为0，匹配模式1，查询模式为1.

因为匹配模式为1，找不到就选下一个大一些的值，所以不会使用没找到的默认值。

一个比较复杂的例子，嵌套使用。在一个二维数组里，需要两个索引来查找某个值。

通过第一个Quarter，找到一列。然后通过Gross Profit，找到这一列中的某一行。

如果不使用XLOOUP，就要使用HLOOUP和VLOOKUP函数组合才能完成这个功能。

组合使用函数，通过Grape和Banana找到两个商品的价格的单元格，然后对这两个单元格之间的内容进行求和。

对于这种复杂的公式，可以使用一种方法来查看其求值过程：

Formulas > Formula Auditing > Evaluate Formula

点击Evaluate，就会按步执行求解过程。

关于查找过程中使用的通配符

使用通配符作为文本过滤器的比较条件，或者在搜索和替换内容时使用。这些也可以在条件格式化规则中使用，在格式化包含指定文本的条件中使用。

Use	To find
? (question mark)	表示单个字符，比如sm?th，会查到smith和smyth、
* (asterisk)	任意数量的字符，比如*east，会查到“Northeast”和“Southeast”
~ (tilde) followed by ?, *, or ~	用于转义?、*和~，比如fy06~?，会查到“fy06?”

参考：

VLOOKUP function

HLOOKUP function

XLOOKUP function

Using wildcard characters in searches