如何在Google表格中使用臭名昭著的VLOOKUP函数？

电子表格/ VLOOKUP (SPREADSHEETS / VLOOKUP)

One of the most amazing (and confusing) function of Google Sheets (or Microsoft Excel) is VLOOKUP. Although not perfect in any sense of the word, it does provide powerful features to join two different tables, or append one table with the values of another.

VLOOKUP是Google表格(或Microsoft Excel)最令人赞叹(且令人困惑)的功能之一。 尽管从任何意义上说都不是完美的，但它确实提供了强大的功能来连接两个不同的表 ，或者将一个表的值附加到另一个表。

什么是VLOOKUP？ (What is VLOOKUP?)

VLOOKUP is a function in spreadsheets which allows the user to establish a relationship between two tables. Let us try and understand it with an example.

VLOOKUP是电子表格中的一项功能，允许用户在两​​个表之间建立关系 。 让我们尝试通过一个示例来理解它。

Consider that you work as an analyst for an upcoming gaming studio and they want you to create a database of top selling game titles in the year 2002. Luckily for you, you found an amazing dataset with all the information and here is how it looks:

考虑到您是一家即将成立的游戏工作室的分析师，他们希望您创建2002年最畅销游戏的数据库。幸运的是，您发现了一个包含所有信息的令人惊叹的数据集，它的外观如下：

It contains information in two separate tables:

它在两个单独的表中包含信息：

• One table consists the information about name of the game and the platform it is released on.
  一张表包含有关游戏名称和发布平台的信息。
• The second table contains the game title and it’s North American and European sales (in Millions).
  第二个表包含游戏标题及其在北美和欧洲的销售额(以百万为单位)。

For more information on the above dataset, you can visit Kaggle.

有关以上数据集的更多信息，您可以访问Kaggle 。

How do we combine the two tables? We can simply copy and paste the values from one to the other. But look closely, the names are not in the same order in the two tables! Well, we can always copy and paste one by one. But that’s too much task! Why don’t we ask the spreadsheets to do it for us? Sounds like a plan! And this is where the almighty VLOOKUP function comes up!

我们如何结合两个表 ？ 我们可以简单地将值从一个复制并粘贴到另一个。 但是仔细观察，两个表中的名称顺序不同 ！ 好吧，我们总是可以一一复制和粘贴。 但这是太多的任务！ 我们为什么不要求电子表格为我们做呢？ 听起来像是个计划！ 这就是万能的VLOOKUP功能出现的地方！

如何使用VLOOKUP？ (How to use VLOOKUP?)

Here is how the VLOOKUP function looks like:

这是VLOOKUP函数的样子：

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

There are 3 mandatory parameters that are required in the function. Here is what they are and how they work:

该功能需要3个必需参数 。 它们是什么以及它们如何工作：

VLOOKUP function in action

VLOOKUP功能正在发挥作用

• Search Key: Select the cell that you want to find the value for. In this instance, we want to find the NA_Sales figures for the game title mentioned in the cell A2. So the search key is A2.

  搜索关键字：选择要为其查找值的单元格。 在这种情况下，我们要查找单元格A2中提到的游戏标题的NA_Sales数字。 因此搜索键为A2。

• Range: Where is the value that you want? In our case, it is somewhere in the table range D2 to F16. So we select the complete table from where we want to copy the value from. Notice how we append a lot of $ signs. Once you have selected the table range, to do this, simply press F4 on your keyboard and you will have the same formatting.

  范围 ：您想要的值在哪里 ？ 在我们的情况下，它在表范围D2至F16中。 因此，我们选择要从中复制值的完整表。 注意我们如何附加很多$符号。 选择表格范围后，只需在键盘上按F4键，便会具有相同的格式。

A small note on why to use $ signs: Once you have created your function, the next natural step is to drag the function down and apply it on all the cells below. In case you didn’t press F4, along with dragging the search key in the formula, you are also dragging the range that you have selected. This would mean that for finding the value corresponding to A4, you are actually searching in the range D3:F17 and not your original D2:F16. So in case your A4 value is there at D2, your function will not read it and throw an error. This is where the $ sign comes in. By pressing F4 once, you are freezing the selected range so that once you drag the formula to the cells below, it will not change the range, but will only change the search key.

关于为什么使用$符号的小注释：创建函数后，下一步自然是将函数向下拖动并将其应用于下面的所有单元格。 如果您没有按F4 键 ，同时在公式中拖动了搜索键 ，那么您还将拖动选定的范围 。 这意味着要查找与A4相对应的值，实际上是在D3：F17范围内搜索，而不是原始的D2：F16。 因此，如果D2处有A4值，则函数将不会读取它并引发错误。 这是$符号出现的地方。按一次F4键， 冻结选定的范围，这样，一旦将公式拖动到下面的单元格中，它就不会更改范围，而只会更改搜索键。

• Index: In the range that you have just selected, which is the column that you want to grab? In our case, it is the NA_Sales column, which is the second column in the selected range of D2 to F16. Note that the index number starts from the first column of the selected range in step 2.

  索引 ：在您刚刚选择的范围内，您想获取哪一列 ？ 在我们的示例中，它是NA_Sales列，它是D2到F16所选范围内的第二列。 请注意，索引号从步骤2中所选范围的第一列开始。

• Is Sorted: This is an optional field. For all practical purposes, set it to zero or false. I have personally never come across a scenario where I have set this field otherwise.

  已排序：这是一个可选字段。 出于所有实际目的，请将其设置为零或false。 我个人从来没有遇到过另外设置此字段的情况。

And press Enter! This function now finds the value in your Search Key, in the range that you mentioned, and as soon as it finds the match, it grabs the index column mentioned and gives you the result. In this case, it will print the value 8.41, since that is the sales figure for North American region for the first game mentioned. Here is how the final solution will look like:

然后按Enter！ 现在，此函数将在您 提到的范围内的“ 搜索关键字”中查找值 ，并在找到匹配项后立即获取所提到的索引列并为您提供结果。 在这种情况下，它将打印值8.41，因为这是提到的第一个游戏在北美地区的销售额。 最终的解决方案如下所示：

Final result after implementing VLOOKUP successfully

成功实施VLOOKUP后的最终结果

常见的陷阱以及如何避免 (Common pitfalls and how to avoid them)

Here are a few important points to keep in mind while applying VLOOKUP:

应用VLOOKUP时，请牢记以下几点：

1. How does the lookup function know what to match in the mentioned range? Always keep in mind that the lookup function will ONLY match the values mentioned in the first column of the specified range in the formula.

   查找功能如何知道在上述范围内要匹配的内容？ 请始终记住，查找功能将仅与公式中指定范围的第一列中提到的值匹配 。

   In our example, we mentioned our range as D2:F16. The search key will only be matched with the values in the D column of the D2:F16 range. As a rule,

   在我们的示例中，我们将范围称为D2：F16。 搜索键将仅与D2：F16范围的D列中的值匹配。 通常

   your first column of the range should be the one where the search key should lie.

   您范围的第一列应该是搜索键所在的列。

2. The lookup function will ALWAYS provide the first result it gets.

   查找功能将始终提供它获得的第一个结果 。

   For instance, in case there are multiple mentions of a game in the sales table, it would grab the first value it matches and provide the result accordingly.

   例如，如果在销售表中多次提及某个游戏，则它将获取与之匹配的第一个值并相应地提供结果。

   In case you have

   如果你有

   multiple instances of the same search key, VLOOKUP is not the way to go!

   同一搜索关键字的多个实例 ，VLOOKUP 并非可行之路 ！

3. You need to make sure that the search key and the value that it is finding in the range matches EXACTLY. There should be no extra spaces. The string case should be the same. If it is not, it will not match the value and you won’t get the desired result.

   你需要确保搜索键 ，它的范围寻找值 完全一致 。 不应有多余的空格。 字符串大小写应该相同。 如果不是，它将与值不匹配，您将无法获得理想的结果。

4. VLOOKUP generally throws a #N/A error when it doesn’t find the value that you are looking for in the range. It simply means that the value doesn’t exist in the mentioned range, or that there are some extra spaces or case issues with the range (as explained in the above point).

   当VLOOKUP在范围内找不到所需的值时，通常会引发＃N / A错误 。 这仅表示该值在上述范围内不存在，或者该范围存在一些额外的空格或大小写问题(如上所述)。

VLOOKUP also comes with another variant which is called HLOOKUP. Where VLOOKUP matches the values vertically, HLOOKUP does it horizontally. The working of this function is exactly the same.

VLOOKUP还带有另一个变体，称为HLOOKUP。 VLOOKUP在垂直方向上匹配值，而HLOOKUP在水平方向上匹配。 此功能的工作原理完全相同。

VLOOKUP is nowhere your perfect solution for joining two tables. But in most of the cases, with some careful execution and keeping in mind the common pitfalls, you can use VLOOKUP in a lot of scenarios. There is another, more powerful way to combat the issue of joining two tables which is often called the INDEX-MATCH function, but more on that in the next article.

VLOOKUP并不是连接两个表的完美解决方案。 但是在大多数情况下，只要谨慎执行并记住常见的陷阱 ，就可以在许多情况下使用VLOOKUP。 还有另一种更强大的方法来解决联接两个表的问题，通常称为INDEX-MATCH函数，但在下一篇文章中将对此进行更多介绍。

Meanwhile, keep analyzing!

同时，继续分析！

翻译自: https://towardsdatascience.com/how-to-use-the-notorious-vlookup-function-in-google-sheets-3dd6f825d4fe