XLOOKUP函数使用指南

XLOOKUP函数使用指南

XLOOKUP函数

XLOOKUP函数在指定范围内搜索特定值，并返回同一行中另一列的值。

XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)

• search-value：在search-range中搜索的值。search-value可以包含任何值，或一个正则表达式字符串。
• search-range：要搜索的单元格范围。
• return-range：要返回的单元格范围。
• if-not-found：可选参数，用于指定未找到匹配项时的显示信息。
• match-type：可选参数，指定要搜索的匹配类型。
• exact or next smallest(-1)：如果没有匹配项，则返回错误。
• exact match(0或省略)：如果没有精确匹配，则返回错误。
• exact or next largest(1)：如果没有匹配项，则返回错误。
• wildcard(2)：*、?和~具有特殊含义。如果要在XLOOKUP中使用正则表达式，必须使用通配符。
• search-type：可选参数，指定搜索范围的顺序。
• Binary descending(-2)：二进制搜索，要求范围按降序排序，否则返回错误。
• Last to first(-1)：从后向前搜索范围。
• First to last(1或省略)：从前向后搜索范围。
• Binary ascending(2)：二进制搜索，要求范围按升序排序，否则返回错误。

注意事项

• 如果search-range或return-range是跨度引用（如"B"），则自动忽略标题和页脚。
• 要从数组返回结果，可以使用INDEX与XLOOKUP结合。

示例

下表"Products"列出了产品及其属性，如尺寸和价格：

使用XLOOKUP进行搜索

使用XLOOKUP，你可以在电子表格中插入一个公式，通过首先提供产品名称，然后提供要返回的列，来返回任何相关值。例如，如果你想返回Product 1的宽度，可以使用以下公式，该公式返回17 cm：

在这个公式中，使用了以下参数：

• search-value：Products::$A2，对Products表中包含Product 1的单元格的绝对引用。
• search-range：Products::A，要搜索Product 1的列。
• return-range：Width，包含与Product 1关联的值的列。
• match-type：省略。如果省略match-type，XLOOKUP默认搜索精确匹配。

设置if-not-found字符串

如果你想搜索特定的产品长度并返回其匹配的宽度，以及在未找到匹配项时返回的字符串，可以使用以下公式，该公式返回"No match"：

在这个公式中，使用了if-not-found参数来执行更具体的搜索：

• search-value：13，要在search-range中搜索的值。
• search-range：Length，要搜索的列。
• return-range：Width，如果找到search-value的匹配项，则返回的列。
• if-not-found："No match"，如果未找到长度为13 cm的产品，则显示的字符串。
• match-type：精确匹配(0)。这仅搜索长度为13 cm的值。

查找最接近的值

XLOOKUP还可以基于特定值和与其接近的值进行广泛搜索。如果你更改上述公式的match-type，可以返回长度为13 cm的宽度，或下一个最小值。以下公式返回11 cm：

在这个公式中，参数与上述相同，但使用了不同的match-type值来更改搜索方式：

• match-type：精确或下一个最小值(-1)。这搜索长度为13 cm的值，如果没有找到该值，则在Length列中查找下一个最小值。

更改搜索顺序

在某些情况下，更改XLOOKUP搜索表的顺序可能很有用。例如，在上述表中，有两个长度为16 cm的产品，因此如果在Length列中使用search-value和search-range搜索16 cm，可能会有两个潜在匹配项。你可以使用以下公式更改搜索顺序，该公式返回20 cm：

在这个公式中，使用search-type参数来设置XLOOKUP搜索表的顺序：

• search-value：16，要在search-range中搜索的值。
• search-range：Length，要搜索的列。
• return-range：Width，如果找到search-value的匹配项，则返回的列。
• if-not-found："No match"，如果未找到长度为16 cm的产品，则显示的字符串。
• match-type：精确或下一个最大值(1)。这搜索长度为16 cm的值，如果没有找到该值，则在Length列中查找下一个最大值。
• search-type：从后向前(-1)。这从最后一个值到第一个值搜索列。

与其他函数结合使用

XLOOKUP还可以与其他函数结合使用，如SUM。例如，你可以使用以下公式返回Product 1、Product 2和Product 3的价格总和，即$247：

在这个示例中，第一个XLOOKUP搜索Product 1的价格，第二个XLOOKUP搜索Product 3的价格。XLOOKUP函数之间的冒号(:)表示SUM应该返回的不仅是Product 1和Product 3的总价格，还包括它们之间的任何值。

在以下公式中，XLOOKUP与REGEX结合使用，返回Product 2，这是第一个宽度以"2"开头的产品：

在这个示例中，使用"通配符(2)"作为match-type来利用REGEX函数中的通配符。

额外示例

给定以下表格：

• =XLOOKUP(49,B2:B11,C2:C11) 返回"62000"，这是第一个年龄为49岁的员工的薪水。
• =XLOOKUP(60000,C2:C11,B2:B11,"No match") 返回"No match"，因为没有薪水为$60,000的员工。
• =XLOOKUP(REGEX("^C.*"), A2:A11, B2:B11, FALSE, 2) 返回"42"，这是"Chloe"的年龄，她是范围内第一个名字以"C"开头的员工。
• =INDEX(XLOOKUP(A2,A2:A11,B2:C11),2) 返回71000，这是XLOOKUP返回的数组中的第二个值。