30天中30个Excel函数：09 – VLOOKUP

For day 9 in the 30XL30D challenge, we'll examine the VLOOKUP function. As you can guess by its name, this is one of the Lookup functions, and works with items that are in a Vertical list.

对于30XL30D挑战的第9天，我们将研究VLOOKUP函数。 您可以通过名称来猜测，这是“查找”功能之一，并且可以与“垂直”列表中的项目一起使用。

Other functions might do a better job of pulling data from a table (see VLOOKUP traps section below), but VLOOKUP is the lookup function that people try first.

其他功能可能会更好地从表中提取数据(请参见下面的VLOOKUP陷阱部分)，但是VLOOKUP是人们首先尝试的查找功能。

Some people get the hang of it right away, and others struggle to make it work. Yes, this function has some flaws, but once you understand how it works, you'll be ready to move on to some of the other lookup options.

有些人立即掌握了要诀，另一些人则努力使其发挥作用。 是的，此功能有一些缺陷，但是一旦您了解了它的工作原理，便可以继续使用其他一些查找选项。

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).

注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。

So, let's take a look at the VLOOKUP information and examples, and if you have other tips or examples, please share them in the comments. And remember to guard your secrets!

因此，让我们看一下VLOOKUP信息和示例，如果还有其他提示或示例，请在评论中分享。 并记住要保护您的秘密！

功能09：VLOOKUP (Function 09: VLOOKUP)

The VLOOKUP function looks for a value in the first column in a table, and returns another value from the same row in that table.

VLOOKUP函数在表的第一列中查找值，然后从该表的同一行返回另一个值。

您如何使用VLOOKUP？ (How Could You Use VLOOKUP?)

The VLOOKUP function can find exact matches in the lookup column, or the closest match, so it can:

VLOOKUP函数可以在查找列中找到精确匹配项，或者是最接近的匹配项，因此它可以：

• Find the price of a selected product
  查找选定产品的价格
• Convert student percentages to letter grades
  将学生百分比转换为字母等级

VLOOKUP语法 (VLOOKUP Syntax)

The VLOOKUP function has the following syntax:

VLOOKUP函数具有以下语法：

• • lookup_value: the value that you want to look for -- it can be a value, or a cell reference.

    lookup_value ：您要查找的值-它可以是一个值或单元格引用。

  • table_array: the lookup table -- this can be a range reference or a range name, with 2 or more columns.

    table_array ：查找表-可以是范围参考或范围名称，具有2列或更多列。

  • col_index_num: the column that has the value you want returned, based on the column number within the table.

    col_index_num ：基于表中的列号，具有要返回值的列。

  • [range_lookup]: for an exact match, use FALSE or 0; for an approximate match, use TRUE or 1, with the lookup value column sorted in ascending order..

    [range_lookup] ：要完全匹配，请使用FALSE或0; 对于近似匹配，请使用TRUE或1，并且按升序排列查找值列。

  VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

  VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)

VLOOKUP陷阱 (VLOOKUP Traps)

VLOOKUP can be slow, especially when doing a text string match, in an unsorted table, where an exact match is requested. Wherever possible, use a table that is sorted by the first column, in ascending order, and use an approximate match.

VLOOKUP可能会很慢，尤其是在进行文本字符串匹配时，在要求完全匹配的未排序表中，这种速度很慢。 尽可能使用按第一列升序排列的表，并使用近似匹配。

You can use MATCH or COUNTIF to check for the value first, to make sure it is in the table's first column (see example 3 below).

您可以使用MATCH或COUNTIF首先检查该值，以确保它在表的第一列中(请参见下面的示例3)。

Other functions, such as INDEX and MATCH, can be used to return values from a table, and are more efficient. We'll look at those functions later in the challenge, and see how flexible and powerful they are.

其他函数(例如INDEX和MATCH)可用于从表中返回值，并且效率更高。 在挑战赛的后面，我们将介绍这些功能，并了解它们的灵活性和强大性。

示例1：查找所选项目的价格 (Example 1: Find the Price for a Selected Item)

The VLOOKUP function looks for a value in the left column of the lookup table.

VLOOKUP函数在查找表的左列中查找值。

In this example, we'll find the price for a selected product. It's important to get the correct price, so the following settings are used:

在此示例中，我们将找到所选产品的价格。 获得正确的价格很重要，因此使用以下设置：

• a product name is entered in cell B7
  在单元格B7中输入产品名称
• the pricing lookup table has two columns, and is in range B3:C5
  定价查询表有两列，范围为B3：C5
• price is in column 2 of the table.
  价格在表格的第2列中。
• FALSE is used in the last argument, to find an exact match for the lookup value.
  在最后一个参数中使用FALSE来查找查找值的精确匹配。

The formula in cell C7 is:

C7单元格中的公式为：

=VLOOKUP(B7,B3:C5,2,FALSE)

= VLOOKUP(B7，B3：C5,2，FALSE)

If the product name is not found in the first column of the lookup table, the VLOOKUP formula result is #N/A

如果在查找表的第一列中找不到产品名称，则VLOOKUP公式的结果为＃N / A

示例2：将百分比转换为字母等级 (Example 2: Convert Percentages to Letter Grades)

Usually, an exact match is required when using VLOOKUP, but sometimes an approximate match works better. For example, when converting student percentages to letter grades, you wouldn't want to type every possible percentage in the lookup table.

通常，使用VLOOKUP时需要精确匹配，但有时近似匹配效果更好。 例如，将学生百分比转换为字母等级时，您不想在查找表中键入所有可能的百分比。

Instead, you could enter the lowest percentage for each letter grade, and then use VLOOKUP with an approximate match. In this example:

相反，您可以输入每个字母等级的最低百分比，然后将VLOOKUP与近似匹配一起使用。 在此示例中：

• a percentage is entered in cell C9
  在单元格C9中输入一个百分比
• the percentage lookup table has two columns, and is in range C3:D7
  百分比查找表有两列，范围为C3：D7
• the lookup table is sorted by the percentage column, in ascending order
  查找表按百分比列按升序排序
• letter grade is in column 2 of the table.
  字母等级在表格的第2列中。
• TRUE is used in the last argument, to find an approximate match for the lookup value.
  在最后一个参数中使用TRUE，以查找查找值的近似匹配。

The formula in cell D9 is:

D9单元格中的公式为：

=VLOOKUP(C9,C3:D7,2,TRUE)

= VLOOKUP(C9，C3：D7,2，TRUE)

If the percentage is not found in the first column of the lookup table, the VLOOKUP formula result is the next largest value that is less than lookup_value.

如果在查找表的第一列中找不到该百分比，则VLOOKUP公式结果是小于lookup_value的第二个最大值。

The lookup value in this example is 77. That value is not in the percentage column, so the value for 75 (B) is returned.

在此示例中，查找值是77。该值不在百分比列中，因此返回75(B)的值。

示例3：找到具有近似匹配项的确切价格 (Example 3: Find Exact Price With Approximate Match)

The VLOOKUP function can be slow when doing an exact match for a text string.

对文本字符串进行完全匹配时，VLOOKUP函数可能会变慢。

In this example, we'll find the price for a selected product, without using the Exact Match setting. To prevent incorrect results:

在此示例中，我们将查找选定产品的价格，而无需使用“完全匹配”设置。 为防止错误结果：

• the lookup table is sorted by the first column, in ascending order
  查找表按第一列按升序排序
• COUNTIF checks for the value, to prevent incorrect results
  COUNTIF检查该值，以防止出现错误结果

The formula in cell C7 is:

C7单元格中的公式为：

=IF(COUNTIF(B3:B5,B7),VLOOKUP(B7,B3:C5,2,TRUE),0)

= IF(COUNTIF(B3：B5，B7)，VLOOKUP(B7，B3：C5,2，TRUE)，0)

If the product name is not found in the first column of the lookup table, the VLOOKUP formula result is 0.

如果在查找表的第一列中找不到产品名称，则VLOOKUP公式的结果为0。

下载VLOOKUP功能文件 (Download the VLOOKUP Function File)

To see the formulas used in today's examples, you can download the VLOOKUP function sample workbook. The file is zipped, and is in Excel 2007 file format.

要查看当今示例中使用的公式，可以下载VLOOKUP函数示例工作簿 。 该文件已压缩，并且为Excel 2007文件格式。

更多VLOOKUP信息和示例 (More VLOOKUP Info and Examples)

• For more VLOOKUP examples, you can visit the VLOOKUP page on the Contextures website.

  有关更多VLOOKUP的示例，您可以访问Contextures网站上的VLOOKUP页面。

• Check out Chandoo's blog, where he had an entire VLOOKUP week, with tips and examples.

  请查看Chandoo的博客，其中有整整一个星期的VLOOKUP ，其中包含提示和示例。

• For suggestions on speeding up a lookup formula, see Charles Williams' page on Optimizing Lookups.

  有关加快查找公式的建议，请参阅Charles Williams的“ 优化查找 ”页面。

观看VLOOKUP视频 (Watch the VLOOKUP Videos)

To see a demonstration of the examples in the VLOOKUP function sample workbook, you can watch these 2 short Excel video tutorials.

要查看VLOOKUP函数示例工作簿中示例的演示，您可以观看以下2个简短的Excel视频教程。

使用VLOOKUP查找产品价格 (Find Product Price with VLOOKUP)

演示地址

将百分比转换为字母等级 (Convert Percentages to Letter Grades)

演示地址

翻译自: https://contexturesblog.com/archives/2011/01/10/30-excel-functions-in-30-days-09-vlookup/