volookup函数怎么用,VLOOKUP与INDEX MATCH比较

题图来自Unsplash，基于CC0协议

导读

VLOOKUP函数是Excel中最常用和最强大的查找函数之一。它允许你根据第一列中的值（查找值），在表格或区域中搜索并返回指定列索引位置的值。想象一下它像查找一本字典的索引页，然后根据找到的位置去获取更详细的信息。掌握VLOOKUP能极大地提高你的Excel工作效率。

以下将详细介绍VLOOKUP的使用方法、常见问题以及与其他查找方法的比较：

基本用法：

VLOOKUP函数的基本语法如下：

=VLOOKUP(查找值, 表格区域, 列序号, [匹配方式])

• 查找值 (lookup_value)： 必需。你要查找的值。它通常是一个单元格引用，也可以是直接输入的值或文本。查找值必须位于你指定的“表格区域”的第一列。
• 表格区域 (table_array)： 必需。包含数据的单元格区域，VLOOKUP将在其中进行查找。这个区域必须是以左上角单元格作为起始点的连续区域，比如 A2:C100。请确保这个范围包含了所有相关的数据行。
• 列序号 (col_index_num)： 必需。一个数字，指定了你希望VLOOKUP返回哪个列的数据。列序号是相对于“表格区域”第一列开始计数的。
  • 例如，如果你的表格区域是 A2:C100，而你想获取第三列的数据，那么列序号就应该是 3。
  • 不能直接引用行号或列号，必须使用数字（1为第一列，2为第二列，以此类推）。
• 匹配方式 (range_lookup)： 可选。
  • TRUE 或 1 (默认值)：近似匹配。VLOOKUP会查找与查找值完全匹配，或者查找值小于查找范围第一列中最大值，然后返回找到的最后一行的对应数据。查找值可以是文本、数字或逻辑值，并且会进行类型转换（例如，文本数字 "5" 和数字 5 在进行近似匹配时被视为相同）。查找范围的第一列必须已排序（通常是升序）。如果找不到精确匹配，VLOOKUP会返回最近的一个小于或等于查找值的数据行（如果有），或 #N/A。
  • FALSE 或 0：精确匹配。VLOOKUP只查找查找值在“表格区域”第一列中完全一致的值，不会进行类似 TRUE 的近似查找。这是查找价格、代码或唯一ID等精确信息时更安全、更常用的方式。

示例教程：

假设你有一个学生信息表，A列为学号，B列为姓名，C列为语文成绩，D列为数学成绩。

你想查找学号为 203 的学生的数学成绩。

你可以使用=VLOOKUP(203, A2:D6, 4, FALSE)。

• 查找值：203 （学号）
• 表格区域：A2:D6 （整个数据区域，假设第一行是标题行）
• 列序号：4 （D列是这一列区域的第四列）
• 匹配方式：FALSE （要求精确匹配）

公式结果应该返回 88，表示学号203的学生的数学成绩。

常见错误及解决方法：

VLOOKUP使用中常见的错误及解决方法：

1. #N/A

   • 原因： 查找了不存在的值。查找值在“表格区域”第一列中找不到对应的数据。
   • 解决方法：
     • 检查查找值是否正确。
     • 确认“表格区域”第一列是否包含该查找值。注意大小写问题（如果比较敏感）。
     • 如果执行的是近似匹配（range_lookup 为 TRUE 或省略），可能是查找值超过了表格区域第一列的最大值，并且没有意义的“最后一行”数据。尝试使用精确匹配（range_lookup 设置为 FALSE）。

2. #VALUE!

   • 原因： col_index_num 参数不是数字。
   • 解决方法： 确保提供的是数字值或单元格引用（包含数字）。

3. 其他错误值（如 #NAME?, #REF!）

   • 原因：
     • #NAME?：公式中可能有未定义的名称，如果你使用了“表格区域”中的列名，并且VLOOKUP无法识别，Excel版本或设置可能有异，或公式有语法错误。
     • #REF!：提供的 table_array 参数无效，例如引用了已被删除的单元格或不正确的范围。
   • 解决方法：
     • 检查公式中的所有函数名是否拼写正确。
     • 检查 table_array 是否是一个定义良好的单元格范围，并且没有被意外修改过。

查找值的数据类型不匹配：

• 原因： 查找值的格式与“表格区域”第一列中的数据格式不匹配。例如，一个文本格式的单元格与一个数字格式的单元格进行比较，或者反之。
• 解决方法：
  • 如果进行精确匹配（FALSE），确保两者格式一致（都是文本或都是数字）。
  • 如果进行近似匹配（TRUE）且列索引大于1，而查找值在第一列为文本数字（如“100”）而在目标列为数值（如100），Excel会进行类型转换（文本数字会转换为数字）。有时，为了确保精确匹配和避免意外，可以将查找值也转换为相同类型（例如，使用 -- 运算符或 VALUE() 函数强制转换），但这种情况比较少见。

VLOOKUP与INDEX + MATCH比较：

虽然VLOOKUP非常强大，但也存在一些限制：

• 方向性限制： VLOOKUP只能向右查找（查找值在左，结果在右）。这是它最常用的场景之一，但如果你需要向左查找，VLOOKUP就不能直接胜任。
• 固定范围： VLOOKUP搜索的是第一列，列序号固定，返回目标列。

INDEX 和 MATCH 函数组合则灵活得多：

INDEX(array, row_num, [col_num], [area]) + MATCH(lookup_value, lookup_array, [match_type])

• INDEX 函数可以从一个区域或数组中返回一个值，可以按行和列索引指定位置，甚至可以在多维区域中灵活定位。
• MATCH 函数则可以在一个区域中查找特定值，并返回其在区域中的相对位置（行号或列号），比较灵活。
• VLOOKUP + INDEX/MATCH 优势：
  • FLEXIBLE DIRECTION: Using MATCH with INDEX, you can look up based on any column (left or right) and return any column (left or right), offering much greater flexibility than VLOOKUP.
  • COLUMN SEARCH: MATCH can search by column (like col_num in INDEX) and return a row number, then INDEX uses that row number to get a value, even from a different column area.

Conclusion:

VLOOKUP is easier to remember and use for basic right-to-left lookups, especially with numbers, codes, dates, or when the lookup column is always the first.

然而，INDEX + MATCH 提供了更强大、更灵活的查找能力，包括左查找、多条件查找以及更好的性能（尤其是在大型数据集上），是被广泛推荐的查找方式。虽然VLOOKUP是入门级的利器，但对于深入Excel而言，掌握 INDEX + MATCH 是一个重要的进阶。本文主要集焦在VLOOKUP及其常见用法和潜在陷阱上。