Excel中LOOKUP函数的用法及实例讲解

今天我们来深入探讨一下 Excel 中的 LOOKUP 函数，尤其是它的模糊匹配功能。LOOKUP 是 Excel 中一个非常强大的查找函数，通过这篇帖子，你将掌握 LOOKUP 函数的全面用法，包括模糊匹配、布尔逻辑、排序要求等注意事项。让我们一起来学习吧！

一、 LOOKUP 函数的基本用法

LOOKUP函数最基础的用法就是在一列或一行中查找一个值，并返回同一位置的另一列或行中的值。函数的语法如下：

1、向量形式

=LOOKUP(查找值,查找向量, 返回向量)

• 用法：当你想从一列或一行数据中查找并返回对应值时，可以使用这种形式。

示例：=LOOKUP(95, C2:C4, D2:D4)，
这个公式在查找销量为 95 的数据时，会在 C2中查找，并返回对应的 D 列中的销售额

2、数组形式

=LOOKUP(查找值, 数组)

用法：当你的数据区域是一个矩阵，并且查找值在第一列（或第一行）时，可以使用这种形式。

3、关键注意事项

数据排序要求：

• 在使用 LOOKUP 函数时，查找区域必须按升序排列，否则函数会返回错误的值。尤其是在处理大量数据时，这一点显得尤为重要。

模糊匹配：

• 如果查找值不精确匹配，LOOKUP 会返回小于或等于查找值的最大值。这在处理区间数据时非常有用，但也意味着，如果你不希望返回模糊匹配结果，应使用其他查找函数如 XLOOKUP 或 INDEX MATCH。

常见错误：

• 当查找区域未排序时，LOOKUP 的结果可能是不准确的。在这种情况下，建议先对数据进行排序，或者使用支持非排序数据的函数，如 INDEX MATCH。

二、应用实例

LOOKUP 函数用于在一个向量（如行或列）中查找数值，并返回同一位置的另一个向量中的值。比如，你有一张销售数据表，需要根据销量查找相应的销售额。

示例表格：

1、基本匹配

公式：

=LOOKUP(95,D2:D7,E2:E7)

该公式查找销量为 95 的行，并返回对应的销售额 1200。

注意： 该公式假设销量（D 列）是按升序排列的。如果未排序，则结果可能不准确。

2、LOOKUP 的模糊匹配：排序要求

LOOKUP 函数支持模糊匹配，这意味着即使查找值在数据中没有找到完全匹配的值，它也会返回最接近但小于查找值的项。注意：要使用模糊匹配，数据必须按升序排列，否则返回结果可能不准确。

示例：

假设我们要查找销量为 80 的销售额，数据中并没有 80 这个数值。

公式：

=LOOKUP(80,D2:D7,B2:B7)

如果 D 列的销量数据按升序排列，公式将返回 比80小，接近80的数值，因为 75 是小于 80 的最大值，所以返回李华。

3、查找最后一个文本

假设要查询B列最后一个员工姓名

=LOOKUP("座",B$2:B7)
=LOOKUP("々",B$2:B7)

示例讲解： 假设B列的数据包括了一些员工的姓名和文本信息，我们可以使用

LOOKUP("々", B:B)或LOOKUP("座", B:B)来查找B列中最后一个文本。

这个公式的工作原理是：LOOKUP函数查找一个编码较大的字符“々”或“座”，Excel会认为这是一个比所有文本值都大的字符，因此返回A列中最后一个文本。

动态扩展查询区域： 在B列中，如果有一些合并单元格的部门信息，我们可以使用以下公式在C列中填充完整的信息：

=LOOKUP("座", B$2:B2)

示例讲解：

• B$2:B2 是一个动态范围，随着公式向下拖动，范围会自动扩展，例如，第二行是B$2:B3，第三行是B$2:B4。
• 该公式会在当前行之前的所有B列单元格中查找最后一个文本，并将其返回到C列中。

4、查找最后一个数值

=LOOKUP(9E 307,F:F)

示例讲解： 假设A列包含了数值和文本混合的数据，我们希望查找A列中的最后一个数值。公式=LOOKUP(9E307, F:F)中，9E307表示Excel可以处理的接近最大数值。LOOKUP函数会返回A列中最后一个数值。

查找A列中的最后一个非空单元格内容： 如果D列中包含了文本和数值，我们可以使用以下公式来查找最后一个非空单元格的内容：

=LOOKUP(1, 0/(A:A<>""), A:A)

示例讲解：

• 公式的条件部分(A:A<>"")会生成一个布尔数组，标识A列中哪些单元格是非空的。
• 0/TRUE 返回0，0/FALSE 返回错误值。
• 最终，LOOKUP函数会在数组中查找1，并返回最后一个0对应的A列值，即最后一个非空单元格的内容。

5、逆向查询

模式化公式：=LOOKUP(1, 0/(条件区域=指定条件), 目标区域)

公式解读

1、条件区域=指定条件:

这一部分会生成一个逻辑数组，用来判断“条件区域”中的每个单元格是否满足“指定条件”。

如果某个单元格满足条件，它将在数组中生成TRUE；如果不满足，则生成FALSE。

例如，如果“条件区域”为A1:A5，而“指定条件”为"苹果"，并且A列的值为{苹果, 香蕉, 苹果, 葡萄, 橘子}，那么结果将是 {TRUE, FALSE, TRUE, FALSE, FALSE}。

2、0/(条件区域=指定条件):

在这个步骤中，布尔值TRUE和FALSE将被分别转换为1和0。

0除以TRUE（即0/1）结果为0，而0除以FALSE（即0/0）将会导致错误值#DIV/0!。

因此，上面例子中的结果数组会变成{0, #DIV/0!, 0, #DIV/0!, #DIV/0!}。

3、LOOKUP(1, ...):

LOOKUP函数尝试在数组中查找1。

由于数组中并没有1存在，所以LOOKUP函数会返回数组中最后一个0所对应的“目标区域”的值。

4、目标区域:

这是从中返回值的区域，即当LOOKUP找到最后一个0时，它会在“目标区域”中返回相应行的值。

示例讲解： 假设在A列到D列中有员工的基础信息（部门、姓名、职务），现在根据J5单元格中的员工姓名，查找其所属的部门。

公式：

=LOOKUP(1,0/(D2:D7=J2),C2:C7)

这个公式利用布尔逻辑生成一个数组，LOOKUP 将查找数组中值为 1 的行，并返回对应的B部门。

公式解读

1. D2:D7=J2:这部分逻辑检查D列中的每一个值是否等于J2单元格中的值。结果是一个由TRUE和FALSE值组成的数组。例如，如果J2等于55，而D2的值是{55, 71, 75, 88, 95, 97}，那么结果将是{TRUE, FALSE, FALSE, FALSE, FALSE, FALSE}。
2. 0/(D2:D7=J2):在这个部分，布尔数组TRUE和FALSE将会被分别转换为1和0。0除以TRUE（即0/1）结果为0，而0除以FALSE（即0/0）结果为错误值#DIV/0!。因此，前面的例子将生成{0, #DIV/0!, #DIV/0!, #DIV/0!, #DIV/0!, #DIV/0!}。
3. LOOKUP(1, ...):LOOKUP函数尝试在数组中查找1，但是由于数组中没有1，LOOKUP会返回数组中最后一个0的位置所对应的值。这是LOOKUP函数的特点，它总是会返回最后一个有效的匹配项。
4. C2:C7:这个部分定义了要返回的值的范围。在查找到满足条件的位置后，LOOKUP函数将在C列中返回对应行的值。

6、 多重条件查找

模式化公式=LOOKUP(1, 0/(条件1)/(条件2)/.../(条件N), 目标区域)

根据多条件进行查询

示例：目前我们有两个条件张强，产品C，求部门

7、LOOKUP 进行区间查找

LOOKUP 非常适合区间查找。比如，你想根据销售额来分配销售等级：

示例：

公式：

=LOOKUP(G2,N$2:N$4,O$2:O$4)

这个公式会根据不同的销售额区间返回相应的等级。

8、关键字查询

=LOOKUP(1,-SEARCH(D$2:D$5,A2),D$2:D$5)

• SEARCH(D$2:D$5,A2):
• SEARCH 函数用于在单元格 A2 中查找范围 D$2:D$5 中每个值的起始位置。它返回一个数组，其中包含每个值在 A2 中的起始位置。如果找不到，返回错误值。
• -SEARCH(D$2:D$5,A2):

• 通过将 SEARCH 的结果取负值，得到一个新的数组。这个数组的作用是为了将查找值的起始位置转换成负值，使得在 LOOKUP 函数中较大的负值（即距离较近的匹配）会被优先选中。
• LOOKUP(1,-SEARCH(D$2:D$5,A2),D$2:D$5):

• LOOKUP 函数用于查找数组中的匹配项。在这个公式中，它查找的是值 1 在负值数组中的位置。由于负值数组是按升序排列的（实际是按原始数组的降序排列），因此 LOOKUP 会找到第一个非错误的（即，找到的起始位置最接近的）匹配项，并返回该匹配项在 D$2:D$5 中的值。

9、 注意事项总结

• 排序要求：对于模糊匹配，LOOKUP 函数要求查找向量必须按升序排列，否则返回结果可能不准确。
• 布尔逻辑：可以利用布尔逻辑和 IF 函数实现复杂的多条件查找。
• 模糊匹配：LOOKUP 会在查找不到确切匹配项时，返回小于查找值的最大项。

结论

今天我们全面解析了 LOOKUP 函数的多种用法，包括模糊匹配的排序要求、布尔逻辑的灵活运用等。希望这个帖子能够帮助你更好地掌握 LOOKUP 函数的应用，在工作中提升效率！