VLOOKUP

如果您在电子表格中有已知的信息，可以使用 VLOOKUP 按行搜索相关信息。例如，如果您想买一个橙子，可以使用 VLOOKUP 搜索其价格。

BigQuery 中的 VLOOKUP 函数

纵向查找。在搜索列中找到匹配值时，返回相应值在数据列中的对应值。

用法示例

VLOOKUP("Apple",table_name!fruit,table_name!price)

语法

VLOOKUP(search_key, range, index, is_sorted)

search_key（搜索键值）：要在搜索列中搜索的值。
search_column（搜索列）：要搜索的数据列。
result_column（结果列）：结果值所在的数据列。
is_sorted（已排序）：[可选] 查找 search_key 的匹配值的方式。
- FALSE：这表示完全匹配，建议选择此值。
- TRUE：这表示近似匹配，如果 is_sorted（已排序）未指定，则此值为默认值。
  提示：在使用近似匹配之前，请对搜索键值进行升序排序。否则，您可能会收到错误的返回值。了解您可能会遇到返回值错误的原因。

提示：如需在 BigQuery 中实现更灵活的数据库查询，请使用 XLOOKUP。

语法

=VLOOKUP(search_key, range, index, [is_sorted])

输入

search_key（搜索键值）：范围第一列中要搜索的值。
range（范围）：要搜索的上限值和下限值。
index（编号）：包含范围的返回值的列编号。编号必须是正整数。
is_sorted（已排序）：可选输入。选择一个选项：
- FALSE 表示完全匹配。这是推荐值。
- TRUE 表示近似匹配。如果 is_sorted（已排序）未指定，则此值为默认值。
  重要提示：在使用近似匹配之前，请对搜索键值进行升序排序。否则，您可能会收到错误的返回值。了解您可能会遇到返回值错误的原因。

返回值

所选 range（范围）中的第一个匹配值。

技术详情：

示例：

=VLOOKUP(G9, B4:D8, 3, FALSE)

=VLOOKUP("Apple", B4:D8, 3, TRUE)

输入	说明
`search_key`	这是您在 `range`（范围）的第一列中搜索的值。如果您希望没有错误值，搜索键值必须位于 `range`（范围）的第一列中。也支持使用单元格引用。执行简单的检查操作：如果 `search_key`（搜索键值）位于 B3，则 `range`（范围）应从 B 列开始。
`range`	这是 `range`（范围），其中：该函数会在第一列中搜索指定的搜索键值。 `VLOOKUP` 会返回按 `index`（编号）指定的列中的值。您也可以使用命名的范围。如需返回非错误值，搜索键值必须位于 `range`（范围）的第一列中。执行简单的检查操作：如果 `search_key`（搜索键值）位于 B3，则 `range`（范围）应从 B 列开始。
`index`	也称为“列号”。这是包含返回值的 `range`（范围）中列的编号。可能的最小编号为 1。可能的最大编号是该 `range`（范围）中的最大列数。您设置范围后，`VLOOKUP` 只会搜索“搜索键值”列（当编号设为 1）或更靠右的列。提示：使用 `VLOOKUP` 时，假设 `range`（范围）中的列从左到右编号，且从 1 开始。
`is_sorted`	这是一个可选输入。可用选项包括 `TRUE` 和 `FALSE`。如果 `is_sorted`（已排序）为 `TRUE`，则 `VLOOKUP` 会使用近似匹配。重要提示：在使用近似匹配之前，请对搜索键值进行升序排序。否则，可能会返回意外的值。了解您可能会遇到返回值错误的原因。如果 `is_sorted`（已排序）为 `FALSE`，则 `VLOOKUP` 会使用完全匹配。如果 `is_sorted`（已排序）未指定，则默认为 `TRUE`。我们强烈建议您：为 `is_sorted`（已排序）使用 `FALSE`，这样无论是否对“搜索键值”列进行排序，其结果都会保持一致。始终指定 `is_sorted`（已排序）可以提高可读性，即使此输入项是可选项也是如此。

输出说明

返回值

这是 VLOOKUP 根据您的输入返回的值。每个 VLOOKUP 函数只有一个返回值。

如果有多个匹配的搜索键值，则系统会从“搜索键值”列中找到第一个匹配的相关搜索键值，然后返回“返回值”列中对应的值。
如果返回“N/A”，则表示找不到值。

如果您遇到非预期值或错误，如“#N/A”或“#VALUE!”，请开始进行问题排查。如果您想用其他值替换 #N/A，请详细了解如何在 VLOOKUP() 中使用 IFNA()。

VLOOKUP 基本示例：

使用 VLOOKUP 查找不同的搜索键值

使用 VLOOKUP 查找“Orange”和“Apple”的价格。

VLOOKUP on different search keys example

试试看

说明：

使用 VLOOKUP 时，您可以使用不同的搜索键值，如“Apple”和“Orange”。

如需返回非错误值，这些搜索键值必须位于 range（范围）的第一列中。如果您不想为搜索键值填充值，也可以使用单元格引用，例如“G9”。

`search_key`（搜索键值）为“Orange”	=VLOOKUP("Orange", B4:D8, 3, FALSE) 返回值 = $1.01
`search_key`（搜索键值）为“Apple”	=VLOOKUP("Apple", B4:D8, 3, FALSE) 返回值 = $1.50
`search_key`（搜索键值）使用“Apple”的单元格引用 (G9)	=VLOOKUP(G9, B4:D8, 3, FALSE) 返回值 = $1.50

使用 VLOOKUP 查找不同列编号

使用 VLOOKUP 在第二个编号列中查找“Orange”的数量。

VLOOKUP on different column indexes example

试试看

说明：

使用 VLOOKUP 时，假设 range（范围）中的列从左到右编号，且从 1 开始。如需查找目标信息，您必须指定其列编号。例如，第 2 列表示数量。

Index（编号）为 2

找出橙子的数量，也就是 range（范围）的第二列。

=VLOOKUP(G3, B4:D8, 2, FALSE)

返回值 = 5

VLOOKUP 完全匹配或近似匹配

使用 VLOOKUP 完全匹配查找确切 ID。
使用 VLOOKUP 近似匹配查找近似 ID。

VLOOKUP exact match or approximate match example

试试看

说明：

当您搜索最佳匹配而非完全匹配时，请使用近似匹配或 is_sorted（已排序）设为 TRUE。

如果您要搜索的 ID 为 102，但其在表格中并不存在，则近似匹配会退一步向您提供近似的 101 作为结果。这是因为在“搜索键值”列中，101 是最接近且小于 102 的值。

近似匹配会从上向下搜索“搜索键值”列，直到找到大于搜索键值的值。然后，它会停在更大值前的行，然后从该行的“返回值”列中返回值。这意味着，如果您的“搜索键值”列没有按升序排序，您很可能会收到错误的返回值。

重要提示：在使用近似匹配之前，请对搜索键值进行升序排序，以便返回正确的值。否则，可能会返回意外的值。

当您搜索完全匹配的值时，例如 is_sorted（已排序）为 FALSE，则会返回完全匹配的值。例如，“ID = 103”的水果名称是“Banana”。如果没有完全匹配的值，您会收到“#N/A”错误。由于结果更具有预测性，我们建议您使用完全匹配。

完全匹配	=VLOOKUP(G6, A4:D8, 2, FALSE) 返回值 =“Apple”
近似匹配	=VLOOKUP(G3, A4:D8, 2, TRUE) OR（或） =VLOOKUP(G3, A4:D8, 2) 返回值 =“Banana”

VLOOKUP 常见应用场景

替换 VLOOKUP 中的错误值

如果您的搜索键值不存在，您可能需要替换 VLOOKUP 返回的错误值。在这种情况下，如果您不想使用“#N/A”，可以使用 IFNA() 函数替换“#N/A”。详细了解 IFNA()。

Replace error value from VLOOKUP example

试试看

一开始，VLOOKUP 会返回“#N/A”，因为“Fruit”列中不存在搜索键值“Pencil”。

IFNA() 会将“#N/A”错误替换为函数中指定的第二个输入项。在我们的示例中，其为“NOT FOUND”。

=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE),"NOT FOUND")

返回值 =“NOT FOUND”

提示：如果您想替换其他错误（例如“#REF!”），请详细了解 IFERROR()。

在 VLOOKUP 中使用多个条件

VLOOKUP 无法直接运用多个条件。您可以改为创建新的辅助列，以在 VLOOKUP 中直接运用多个条件，从而合并多个现有列。

试试看

1. 如果您使用“&”将“First Name”和“Last Name”合并，则可以创建“Helper”列。	=C4&D4 并将其从 B4 向下拖动到 B8，即可得到“Helper”列。
2. 将单元格引用 B7 (JohnLee) 用作搜索键值。	=VLOOKUP(B7, B4:E8, 4, FALSE) 返回值 =“Support”

在 VLOOKUP 中使用通配符或部分匹配项

在 VLOOKUP 中，您还可以使用通配符或部分匹配项。您可以使用以下通配符：

问号“?”可匹配任何单个字符。
星号“*”可匹配任何字符序列。

如需在 VLOOKUP 中使用通配符，您必须使用完全匹配：“is_sorted（已排序）设为 FALSE”。

试试看

“St*”用于匹配任何以“St”开头的字符，而不考虑字符数，例如“Steve”“St1”“Stock”或“Steeeeeeve”。

=VLOOKUP("St*", B4:D8, 3, FALSE)

返回值 =“Marketing”

排查错误和最佳做法：

返回值有误

返回非预期值：如果您将 is_sorted（已排序）设置为 TRUE，但此范围中的首列未按数字或字母升序排序，则要将“is_sorted”（已排序）更改为 FALSE。
VLOOKUP 提供第一个匹配项：VLOOKUP 仅返回第一个匹配项。如果您有多个匹配搜索键值，则会返回某个值，但此值可能不是预期的值。
带有开头/结尾空格/空白的数据：有时，包含开头/结尾空格的值可能看起来相似，但 VLOOKUP 会将其视为不同的值。例如，以下值对 VLOOKUP 来说是不同的：
- “ Apple”
- “Apple ”
- “Apple”

为了获得您期望的结果，请在使用 VLOOKUP 之前移除空格。

如需了解详情，请参阅我们的最佳做法部分。

#N/A

如果使用近似匹配或 is_sorted（已排序）设为 TRUE，且 VLOOKUP 中的搜索键值小于第一列中的最小值，则 VLOOKUP 会返回“#N/A”。
如果使用完全匹配或 is_sorted（已排序）设为 FALSE，则在第一列中找不到与 VLOOKUP 中的搜索键值完全匹配的值。如果您不希望在第一列中找不到搜索键值时出现“#N/A”，则可以使用函数 IFNA()。

#REF!

您可能会错误地指定一个 range（范围），包含多于 range（范围）最大列数的数字。为避免出现这种情况，请确保您：

计算所选 range（范围）（而非整个表格）中的列数。
从 1 开始，而不是从 0 开始。

#VALUE!

如果收到“#VALUE！”错误，您可能：

为 index（编号）错误输入了文本或列名称。
为 index（编号）输入小于 1 的数字。index（编号）必须至少等于 1，并且小于 range（范围）中的最大列数。VLOOKUP 只会在“搜索键值”列中进行搜索（当 index[编号] 设为 1），或更靠右的列。

重要提示：index（编号）仅接受数字。

#NAME?

当 search_key（搜索键值）是文本数据时，您可能会忘记在搜索键值中添加引号。

最佳做法

待办事项	原因
对 `range`（范围）使用绝对引用	您应该使用：对 `VLOOKUP` 的 `range`（范围）使用绝对引用 VLOOKUP(G3, $B$3:$D$7, 3, FALSE) 您不应使用： VLOOKUP(G3, B3:D7, 3, FALSE) 这样可以防止在复制或向下拖动 `range`（范围）时发生不可预知的更改。
使用近似匹配时，例如 `is_sorted`（已排序）设为 `TRUE`，第一列应按升序排序。	如果您使用近似匹配或 `is_sorted`（已排序）设为 `TRUE`，则必须对第一列进行升序排序。否则，您很有可能会收到错误的返回值。详细了解如何排序。
请在使用 `VLOOKUP` 之前清理您的数据	在使用 `VLOOKUP` 之前，请记得清理数据。带有开头/结尾空格/空白的数据可能会导致 `VLOOKUP` 返回不可预测的值。以下是一些带有开头/结尾空格/空白的数据的常见错误：开头空格：“ apple” 结尾空格：“apple ” 空白或空格：“”和“ ”不相同如需去除多余的开头空格和结尾空格，您可以使用数据数据清理去除多余空格。
不要将数字或日期值存储为文本	确保 `VLOOKUP` 范围第一列中的日期或数值（例如“搜索键值”列）未存储为文本值。您可能会收到意外的返回值。在表格顶部，选择您的“搜索键值”列。因此点按格式菜单数字。根据所需数据类型选择选项：日期数字