【excellookup函数用法】深度解析：掌握其向量形式与数组形式，告别VLOOKUP的局限
在Excel的众多函数中，LOOKUP函数是一个强大而有时被低估的工具。它能够帮助我们在一个范围内查找某个值，并返回对应位置上的另一值。尽管现代Excel版本中出现了功能更强大的XLOOKUP，以及广为人知的VLOOKUP和HLOOKUP，但LOOKUP函数凭借其独特的机制和简洁的语法，在特定场景下依然具有不可替代的优势。本文将深入探讨LOOKUP函数的两种主要形式——向量形式（Vector Form）和数组形式（Array Form），详细解析其用法、特点、适用场景及注意事项，帮助您充分理解并掌握这一实用功能。
认识LOOKUP函数：核心概念与基本原理
LOOKUP函数主要用于在一个单行或单列区域（或整个表格）中查找一个指定值，然后返回另一个单行或单列区域（或表格的最后一个行/列）中对应位置的值。其最显著的特点是它默认执行近似匹配，这使得它非常适合处理分段查找或区间判断的任务。在使用LOOKUP函数之前，理解其工作原理至关重要：
- 近似匹配（Approximate Match）：
LOOKUP函数总是尝试找到小于或等于查找值（lookup_value）的最大值。如果找不到完全匹配的值，它会选择小于lookup_value的最大值。 - 数据排序要求： 为了确保
LOOKUP函数正确运行并返回预期的结果，其查找的范围（无论是向量还是数组的第一行/列）必须按升序排列。如果数据未排序，结果将是不可预测的，甚至可能返回错误。
LOOKUP函数的向量形式（Vector Form）
向量形式是LOOKUP函数最直接、最易于理解和使用的形式，它允许你在一个“查找向量”中找到值，然后从一个“结果向量”中返回对应的值。
语法
LOOKUP(lookup_value, lookup_vector, [result_vector])
参数解释：
lookup_value(必需)： 你想要查找的值。这个值可以是数字、文本或逻辑值。lookup_vector(必需)： 包含你想要查找的数据的区域。它必须是单行或单列的单元格区域（即一个向量）。例如，A1:A10或B1:D1。这个区域的数据必须按升序排列。[result_vector](可选)： 包含你想要返回的数据的区域。它也必须是单行或单列的单元格区域，且与lookup_vector的长度和方向（行或列）相同。如果省略此参数，LOOKUP函数会返回lookup_vector中找到的值。
工作原理
LOOKUP函数首先在 lookup_vector 中找到小于或等于 lookup_value 的最大值。然后，它会返回 result_vector 中与该匹配值在相同位置上的值。
实战案例：查找考试成绩等级
假设我们有一个学生成绩表，并希望根据分数段自动判断学生的等级（优秀、良好、及格、不及格）。
数据设置：
成绩等级标准（假设在 E2:E5 是分数下限，F2:F5 是对应等级）：
E列（分数下限） | F列（等级）
-------------- | ----------
0 | 不及格
60 | 及格
80 | 良好
90 | 优秀
学生成绩：
A列（姓名） | B列（分数） | C列（等级）
---------- | ----------- | ----------
小明 | 75 |
小红 | 92 |
小张 | 55 |
小李 | 80 |
在C2单元格输入以下公式，并向下拖动填充：
=LOOKUP(B2, $E$2:$E$5, $F$2:$F$5)
公式解释：
B2：这是我们要查找的学生分数（lookup_value）。$E$2:$E$5：这是分数下限的查找范围（lookup_vector），注意它已按升序排列。使用绝对引用$符号是为了在拖动公式时保持这个范围不变。$F$2:$F$5：这是对应的等级结果范围（result_vector）。
结果：
- 小明 (75分):
LOOKUP会在E2:E5中找到小于或等于75的最大值，即60。然后返回F2:F5中对应60位置的值，即“及格”。 - 小红 (92分): 找到90，返回“优秀”。
- 小张 (55分): 找到0，返回“不及格”。
- 小李 (80分): 找到80，返回“良好”。
通过这个例子可以看出，LOOKUP的向量形式在处理区间判断和分段查找时非常高效和简洁。
LOOKUP函数的数组形式（Array Form）
数组形式的LOOKUP函数通常用于在整个表格或一个矩形区域中进行查找。它的工作方式与向量形式略有不同，并且具有一些特殊的行为模式。
语法
LOOKUP(lookup_value, array)
参数解释：
lookup_value(必需)： 你想要查找的值。可以是数字、文本或逻辑值。array(必需)： 一个包含数据的工作表区域（一个矩形区域），或是一个命名区域。
工作原理
数组形式的LOOKUP函数会在 array 的第一行或第一列中查找 lookup_value。具体是查找行还是列，取决于 array 的形状：
- 如果
array的行数多于列数，或者行数与列数相等，LOOKUP会在array的第一列中查找lookup_value。 - 如果
array的列数多于行数，LOOKUP会在array的第一行中查找lookup_value。
一旦找到匹配（近似匹配，小于或等于 lookup_value 的最大值），LOOKUP 函数将返回 array 中最后一行或最后一列中与该匹配值对应位置的值。
与向量形式一样，array 的第一行或第一列（取决于查找方向）必须按升序排列。
实战案例：查找产品价格（基于产品ID）
假设我们有一个产品信息表，包含产品ID、名称和价格。
数据设置：
产品信息表（假设在 E2:G5）：
E列（产品ID） | F列（产品名称） | G列（价格）
-------------- | -------------- | ----------
1001 | 鼠标 | 50
1002 | 键盘 | 120
1003 | 显示器 | 800
1004 | 摄像头 | 200
查找需求：
A列（订单ID） | B列（查找产品ID） | C列（产品价格）
---------- | --------------- | ----------
ORD001 | 1003 |
ORD002 | 1001 |
ORD003 | 1002 |
在C2单元格输入以下公式，并向下拖动填充：
=LOOKUP(B2, $E$2:$G$5)
公式解释：
B2：我们要查找的产品ID（lookup_value）。$E$2:$G$5：这是我们的产品信息表（array）。此区域的行数（4行）多于列数（3列），因此LOOKUP会在第一列（E列，产品ID）中查找。查找成功后，它会返回最后一列（G列，价格）中对应位置的值。
结果：
- 订单ID ORD001 (查找产品ID 1003):
LOOKUP会在E列中找到1003。然后返回G列中对应1003位置的值，即800。 - 订单ID ORD002 (查找产品ID 1001): 找到1001，返回50。
- 订单ID ORD003 (查找产品ID 1002): 找到1002，返回120。
请注意，尽管数组形式可以完成类似VLOOKUP的任务，但它的查找和返回逻辑不如VLOOKUP直观。VLOOKUP明确指定了返回列的索引，而LOOKUP的数组形式则固定返回第一列/行或最后一列/行。
LOOKUP函数对比其他查找函数：优势与局限
LOOKUP的优势：为何仍需学习它？
- 简洁性： 在特定场景下，尤其是近似匹配且查找范围和结果范围是简单的向量时，
LOOKUP的语法比VLOOKUP或INDEX+MATCH更简洁。 - 不限查找列位置： 与
VLOOKUP不同，LOOKUP函数的向量形式允许result_vector在lookup_vector的左侧或右侧，这提供了更大的灵活性。数组形式也只关心第一列/行和最后一列/行，不要求返回列在查找列的右侧。 - 默认近似匹配： 对于需要进行区间判断和分段查找的场景，
LOOKUP函数是天生的利器，无需额外参数设置。 - 查找最后一个非空值或数值： 结合一些技巧，
LOOKUP可以非常巧妙地实现查找某一行或某一列中最后一个数值或非空文本。例如：=LOOKUP(9.99999999999999E+307,A:A)可以查找A列中最后一个数值。
LOOKUP的局限性与注意事项：
- 强制升序排序： 这是
LOOKUP函数最大的局限。如果查找范围未按升序排列，函数将返回错误结果，甚至#N/A错误。而VLOOKUP、HLOOKUP和XLOOKUP在精确匹配模式下没有此限制。 - 默认近似匹配： 虽然是优势，但在需要精确匹配的场景下，
LOOKUP无法直接实现。此时必须依赖VLOOKUP(...,FALSE)、MATCH(...,0)或XLOOKUP(...,0)。 - 数组形式的非直观性： 数组形式的查找方向（第一行/列）和返回位置（最后一行/列）是固定的，不如
VLOOKUP和HLOOKUP那样可以明确指定返回列/行索引，或者INDEX+MATCH那样高度灵活。这可能导致初学者混淆。 - 当lookup_value小于查找范围最小值时： 如果
lookup_value小于lookup_vector或array中的最小值，LOOKUP函数将返回#N/A错误。
高级应用：查找最后一个数值或非空文本
LOOKUP函数的一个鲜为人知但非常实用的高级技巧是查找某个区域（行或列）中的最后一个数值或非空文本。这通常通过利用LOOKUP的近似匹配特性和Excel对极大数的处理来实现。
查找最后一个数值：
假设你有一列数据（例如 A列），其中包含数字和一些空单元格或文本，你想要找到这列中最后一个出现的数字。
=LOOKUP(9.99999999999999E+307, A:A)
解释：
9.99999999999999E+307是Excel中能表示的最大正数。- 当
LOOKUP查找这个极大值时，它会在A列中寻找小于或等于这个极大值的最大数字。由于这个值比任何实际的数字都大，它最终会找到A列中最后一个出现的数字。
查找最后一个非空文本：
类似地，可以查找最后一个非空文本。
=LOOKUP(REPT("Z",255), A:A)
解释：
REPT("Z",255)生成一个由255个“Z”组成的字符串，这是Excel中能表示的最长字符串之一，且在字母排序上通常晚于任何实际的文本。LOOKUP会找到A列中按字母顺序排列小于或等于这个“ZZZ...”字符串的最后一个文本值。
这种技巧非常灵活，可以在处理动态数据范围时派上用场，例如在处理不确定数据行数时，需要总是引用到最后一行数据。
总结
LOOKUP函数虽然不如VLOOKUP或XLOOKUP那样万能，但它在处理近似匹配和区间查找方面具有独特的优势。理解其向量形式和数组形式的不同工作原理，并牢记其对查找范围升序排列的严格要求，是掌握此函数的关键。当您需要处理等级评定、分段佣金计算或查找最后一个非空值等任务时，LOOKUP函数无疑是一个值得考虑的简洁高效的解决方案。通过本文的详细解析和实例，相信您已对LOOKUP函数的用法有了全面的认识，并能在实际工作中灵活运用。
常见问题（FAQ）
如何判断LOOKUP函数应该使用向量形式还是数组形式？
这取决于你的数据组织方式和查找需求。如果你的查找值和结果值分别位于两个独立的单行或单列区域，且希望根据查找值在其中一个区域中找到位置，然后在另一个区域返回对应位置的值，那么向量形式（LOOKUP(lookup_value, lookup_vector, [result_vector])）更合适。如果你的数据在一个矩形表格中，并且你希望在表格的第一行或第一列查找，然后返回最后一行或最后一列的值，那么数组形式（LOOKUP(lookup_value, array)）可能更适用。一般来说，向量形式更常用且更直观。
为何LOOKUP函数要求查找范围必须升序排列？
LOOKUP函数的设计初衷就是为了高效执行近似匹配。它的内部算法依赖于查找范围的有序性来进行二分查找或类似的优化查找。当查找值时，它会快速定位到小于或等于查找值的最大值。如果数据未排序，这种查找机制将无法正常工作，导致函数返回错误的结果（可能是一个看似随机的值）或#N/A错误。因此，务必在应用LOOKUP函数前，对你的查找范围进行升序排序。
如何用LOOKUP函数实现精确匹配？
LOOKUP函数本身不直接提供精确匹配的选项，它总是进行近似匹配。如果你需要精确匹配，建议使用其他函数，例如VLOOKUP(lookup_value, table_array, col_index_num, FALSE)（将最后一个参数设为FALSE），或者更灵活的INDEX和MATCH组合，再或者Excel 365中的XLOOKUP函数。
LOOKUP函数返回#N/A错误是为什么？
LOOKUP函数返回#N/A错误通常有以下几个原因：
- 查找值小于查找范围的最小值： 如果
lookup_value比lookup_vector或array中的任何值都小，LOOKUP函数将无法找到“小于或等于”的匹配项。 - 查找范围未排序： 这是最常见的原因之一。即使数据中包含查找值，如果范围未按升序排列，函数也可能因为无法正确执行查找逻辑而返回
#N/A。 - 查找范围或结果范围为空或包含错误值。
#N/A错误。
LOOKUP函数和XLOOKUP函数有什么区别？我应该用哪个？
LOOKUP和XLOOKUP虽然都用于查找，但功能和灵活性有显著差异。
LOOKUP： 默认近似匹配，查找范围必须升序排列，支持向量和数组两种形式，但数组形式的查找和返回逻辑相对固定。XLOOKUP： 是Excel 365及更高版本中引入的更现代、功能更强大的函数。它默认精确匹配，但也可以轻松配置为近似匹配。它不限制查找范围的排序，支持从左到右或从右到左的查找，并且可以返回多个结果，处理错误等。
XLOOKUP，因为它功能更全面，更易于使用，并且解决了LOOKUP和VLOOKUP的许多局限。只有在特定场景（如需要查找最后一个值，或在旧版Excel中且数据已排序）下，LOOKUP函数才可能是一个更简洁的选择。
