您是否曾经在Excel中使用过包含数据的大型电子表格，并且需要一种简单的方法来从中过滤和提取特定信息？如果您了解如何在Excel中使用^(Excel)VLOOKUP，则只需一个强大的Excel 函数^{(Excel function)}即可完成此查找。

Excel中的VLOOKUP函数^{(VLOOKUP function)}让很多人感到害怕，因为它有很多参数，并且有多种使用方法。在本文中，您将了解在Excel中使用^(Excel)VLOOKUP的所有方法以及该功能为何如此强大的原因。

Excel 中的 VLOOKUP 参数^{(VLOOKUP Parameters In Excel)}

当您开始在Excel中的任何单元格中键入=VLOOKUP(时，您将看到一个显示所有可用函数参数的弹出窗口。

让我们检查每个参数及其含义。

• lookup_value：您要从电子表格中查找的值
• table_array：要搜索的工作表中的单元格范围
• col_index_num：要从中提取结果的列
• [range_lookup]：匹配模式^{(Match mode)}（TRUE = approximate，FALSE = exact）

这四个参数使您可以对非常大的数据集中的数据进行许多不同的、有用的搜索。

一个简单的 VLOOKUP Excel 示例^{(A Simple VLOOKUP Excel Example)}

VLOOKUP 不是^{(VLOOKUP isn)}您可能学过的基本Excel功能之一，所以让我们看一个简单的示例来开始。

对于以下示例，我们将使用美国^(United)学校的 SAT 成绩大型电子表格^(SAT)。^(States)该电子表格包含 450 多所学校以及个人SAT阅读、数学和写作成绩。 随意^(Feel)下载跟随。有一个外部连接可以拉取数据，因此打开文件时会收到警告，但它是安全的。

搜索如此庞大的数据集以找到您感兴趣的学校将非常耗时。

相反，您可以在表格一侧的空白单元格中创建一个简单的表单。要进行此搜索，只需为School设置一个字段，并为阅读、数学和写作分数添加三个附加字段。

接下来，您需要使用Excel中的^(Excel)VLOOKUP 函数^{(VLOOKUP function)}来使这三个字段起作用。在阅读^(Reading)字段中，创建VLOOKUP 函数^{(VLOOKUP function)}，如下所示：

1. 输入=VLOOKUP(
2. 选择School 字段^{(School field)}，在本例中为I2。键入逗号。
3. 选择包含要查找的数据的整个单元格区域。键入逗号。

选择范围时，您可以从用于查找的列（在本例中为学校名称列^{(school name column)}）开始，然后选择包含数据的所有其他列和行。

注意^(Note)：Excel中的VLOOKUP 功能^{(VLOOKUP function)}只能通过搜索列^{(search column)}右侧的单元格进行搜索。在此示例中，学校名称列^{(school name column)}需要位于您正在查找的数据的左侧。

1. 接下来，要检索Reading score，您需要从最左边的选定列中选择第三列。因此，键入3，然后键入另一个逗号。
2. 最后，键入FALSE以获得完全匹配，并使用 a )关闭该函数。

您最终的VLOOKUP 函数^{(VLOOKUP function)}应如下所示：

=VLOOKUP(I2,B2:G461,3,FALSE)

当您第一次按Enter并完成该功能时，您会注意到Reading 字段^{(Reading field)}将包含一个#N/A。

这是因为School 字段^{(School field)}是空白的，VLOOKUP 函数^{(VLOOKUP function)}找不到任何内容。但是，如果您输入要查找的任何高中的名称，您将在该行中看到阅读分数^{(Reading score)}的正确结果。

如何处理 VLOOKUP 区分大小写^{(How To Deal With VLOOKUP Being Case- Sensitive)}

您可能会注意到，如果您键入的学校名称的大小写与它在数据集中的列出方式不同，您将看不到任何结果。

这是因为VLOOKUP 函数^{(VLOOKUP function)}区分大小写。这可能很烦人，尤其是对于一个非常大的数据集，其中您正在搜索的列与事物的大写方式不一致。

为了解决这个问题，您可以在查找结果之前强制将要搜索的内容切换为小写。为此，请在您正在搜索的列旁边创建一个新列。键入函数：

=TRIM(LOWER(B2))

这将小写学校名称^{(school name)}并删除可能位于名称左侧或右侧的任何无关字符（空格）。

按住Shift 键并将^{(Shift key and place)}鼠标光标^{(mouse cursor)}放在第一个单元格的右下角，直到它变为两条水平线。双击^(Double)鼠标自动填充整列。

最后，由于VLOOKUP将尝试使用公式而不是这些单元格中的文本，因此您只需将它们全部转换为值。为此，请复制整个列，右键单击第一个单元格，然后仅粘贴值。

现在您的所有数据都已在此新列中清除，稍微修改Excel中的^(Excel)VLOOKUP 函数^{(VLOOKUP function)}以使用此新列而不是前一列，方法是从 C2 而不是 B2 开始查找范围^{(the lookup range)}。

=VLOOKUP(I2,C2:G461,3,FALSE)

现在您会注意到，如果您始终以小写形式输入搜索内容，您将始终获得良好的搜索结果^{(search result)}。

这是一个方便的 Excel 技巧，可以克服^{(handy Excel tip)}VLOOKUP区分大小写的事实。

VLOOKUP 近似匹配

虽然本文第一部分中描述的精确匹配LOOKUP 示例^{(LOOKUP example)}非常简单，但近似匹配稍微复杂一些。

近似匹配最适合用于搜索数字范围。要正确执行此操作，需要对搜索范围^{(search range)}进行正确排序。最好的例子是VLOOKUP 函数^{(VLOOKUP function)}，用于搜索与数字等级^{(number grade)}对应的字母^{(letter grade)}等级。

如果老师有一长串全年的学生作业成绩列表，并带有最终^{(student homework)}平均列^{(averaged column)}，那么自动显示与该最终成绩相对应的字母成绩会很好。

这可以通过VLOOKUP 功能^{(VLOOKUP function)}实现。所需要的只是右侧的查找表^{(lookup table)}，其中包含每个数字分数范围的适当^{(score range)}字母等级^{(letter grade)}。

现在，使用VLOOKUP 函数^{(VLOOKUP function)}和近似匹配，您可以找到与正确数字范围相对应的正确字母等级。

在这个 VLOOKUP 函数中：

• lookup_value : F2，最终平均成绩
• table_array : I2:J8，字母等级查找范围^{(letter grade lookup range)}
• index_column : 2，^{(index_column)}查找表^{(lookup table)}中的第二列
• [range_lookup]：真，近似匹配

在G2 中完成VLOOKUP 功能^{(VLOOKUP function)}并按 Enter^{(G2 and press Enter)}后，您可以使用上一节中描述的相同方法填写其余单元格。您会看到正确填写的所有字母等级。

请注意，Excel 中的VLOOKUP函数^(Excel)从^{(VLOOKUP function)}具有指定字母分数的^{(letter score)}年级范围^{(grade range)}的底部搜索到下一个字母分数^{(letter score)}的范围顶部。

因此，“C”需要是分配给较低范围（75）的字母，而B被分配给它自己的字母范围^{(letter range)}的底部（最小） 。VLOOKUP将“找到” 60 (D) 的结果作为 60 到 75 之间任何值的最接近的近似值。

^(VLOOKUP)Excel中的^(Excel)VLOOKUP是一个非常强大的功能，已经使用了很长时间。它对于在Excel 工作簿^{(Excel workbook)}中的任何位置查找匹配值也很有用。

但是请记住，每月订阅Office 365的^{(Office 365)}Microsoft用户现在可以访问更新的 XLOOKUP 功能。这个函数有更多的参数和额外的灵活性。半年订阅的用户将需要等待更新在2020 年 7 月^{(July 2020)}推出。

How to Use VLOOKUP in Excel

Have уou ever had a large spreadsheet with data іn Excel and need an eаsy way tо filter and extract specifіc information from it? If yoυ learn how to use VLOOKUP in Excel, you can do this lookup with just a single, powerful Excel function.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• 将线性Regression Trendline添加到Excel Scatter Plot

• 如何在Excel中制作Histogram

• 如何安全Password Protect Excel File

• 如何在Word and Excel中创建Flowchart

• 来自Excel Spreadsheet的Word中如何Create Labels

• 所有 Excel 文件扩展名及其含义的指南

• 如何删除、破解或破解忘记的 Excel XLS 密码

• 如何在 Excel 中查找匹配值

• Microsoft Excel 基础教程 - 学习如何使用 Excel

• 如何在 Excel 中添加误差线

• 如何在 Excel 中使用 COUNTIFS、SUMIFS、AVERAGEIFS

• 使用汇总函数汇总 Excel 中的数据

• 如何Automatically Backup A Word Document到Onedrive

• 如何在Excel中Group Worksheets

• 如何在 Excel 中使用 YEARFRAC 函数

• 如何在Microsoft Teams中更改Background

• 如何在 Excel 中使用迷你图

• 如何在 Excel 中过滤数据

• 使用 Excel Mobile 新的“从图片插入数据”功能

• 如何在 Excel 中隐藏工作表、单元格、列和公式