Excel 面试 04 查找函数 XLOOKUP

Excel 的 XLOOKUP 函数是一个功能强大的查找工具，用于从一个范围或数组中搜索值并返回对应结果。它是 VLOOKUP 和 HLOOKUP 的更高级替代品，支持更多灵活功能。

语法

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

参数

1. lookup_value：要查找的值。
2. lookup_array：要搜索的范围或数组。
3. return_array：返回结果的范围或数组，与 lookup_array 对应。
4. [if_not_found]（可选）：如果未找到 lookup_value，返回的自定义值（默认返回 #N/A）。
5. [match_mode]（可选）：控制查找匹配的方式。
   • 0（默认）：精确匹配。如果找不到，则返回 #N/A。
   • -1：精确匹配，若无精确值，返回小于的最大值。
   • 1：精确匹配，若无精确值，返回大于的最小值。
   • 2：支持通配符匹配（* 和 ?）。
6. [search_mode]（可选）：控制搜索方向。
   • 1（默认）：从首到尾。
   • -1：从尾到首。
   • 2：二分法升序搜索（数据需按升序排列）。
   • -2：二分法降序搜索（数据需按降序排列）。

功能特点

1. 支持双向查找：

   • 可以在列中或行中查找，替代 VLOOKUP 和 HLOOKUP。

2. 默认精确匹配：

   • 不需要像 VLOOKUP 那样手动设置匹配模式。

3. 支持缺失值处理：

   • 可使用 if_not_found 参数自定义未找到值时的返回内容。

4. 灵活的匹配模式：

   • 提供通配符匹配和近似匹配选项。

5. 高效的方向控制：

   • 支持从前向后或从后向前搜索。

用法示例

示例 1：基本查找

公式：

=XLOOKUP("香蕉", A2:A4, B2:B4)

• 查找 "香蕉" 在 A2:A4 的位置，并返回对应的价格（B2:B4）。
• 结果：3。

示例 2：未找到值的自定义返回

公式：

=XLOOKUP("橙子", A2:A4, B2:B4, "未找到")

• 如果 lookup_value 不存在，则返回 "未找到"。
• 结果：未找到。

示例 3：近似匹配

假设有以下表格：

公式：

=XLOOKUP(250, A2:A4, B2:B4, , -1)

• 查找小于或等于 250 的最大值的折扣。
• 结果：10%。

示例 4：从尾到首搜索

公式：

=XLOOKUP(150, B2:B4, A2:A4, , 0, -1)

• 从尾到首查找 150 并返回对应日期。
• 结果：2023-01-03。

示例 5：通配符匹配

公式：

=XLOOKUP("李*", A2:A4, B2:B4, , 2)

• 使用通配符 * 查找以 "李" 开头的名字。
• 结果：90。

注意事项

1. 数组大小一致：

   • lookup_array 和 return_array 的大小必须一致，否则会返回错误。

2. 通配符匹配需启用：

   • 必须设置 match_mode 为 2。

3. 支持动态数组：

   • XLOOKUP 可以与动态数组功能结合，返回多值结果。

4. 替代 VLOOKUP/HLOOKUP 的高效选择：

   • 无需担心列索引号或手动设置升序/降序等限制。

总结

XLOOKUP 是 Excel 的现代查找函数，结合了 VLOOKUP 和 HLOOKUP 的功能，具有更高的灵活性和功能性。无论是单向查找、条件查找还是复杂匹配，XLOOKUP 都可以轻松应对，是进行表格分析时不可或缺的工具。