How to Use VLOOKUP Function in Microsoft Excel [+ Video Tutorial]

Coordinating a massive amount of data inMicrosoft Excel是耗时的头痛。当您需要在多个电子表格中比较数据时，这种头痛就会变得更糟。您要做的最后一件事是使用复制和粘贴手动传输单元格。值得庆幸的是，您不必。Vlookup功能可以帮助您自动执行此任务并节省大量时间。

我知道，“ vlookup函数”听起来像是有史以来最怪异，最复杂的事情。但是，当您阅读本文时，您会想知道没有它在Excel中幸存下来。

Microsoft Excel's VLOOKUP function is easier to use than you think. What's more, it is incredibly powerful, and is definitely something you want to have in your arsenal of analytical weapons. Vlookup到底做什么？这是一个简单的说明：vlookup函数搜索数据中的特定值，一旦标识该值，它就会找到并显示与该值相关的其他一些信息。

Vlookup如何工作？

VLOOKUP stands for "vertical lookup." In Excel, this means the act of looking up dataverticallyacross a spreadsheet, using the spreadsheet's columns -- and a unique identifier within those columns -- as the basis of your search. When you look up your data, it must be listed vertically wherever that data is located.

The formula always searches to the right.

When conducting a VLOOKUP in Excel, you're essentially looking for new data in a different spreadsheet that is associated with old data in your current one. When VLOOKUP runs this search, it always looks for the new data to the正确的of your current data.

For instance, if one spreadsheet has a vertical list of names, and another spreadsheet has an unorganized list of those namesand their email addresses, you can use VLOOKUP to retrieve those email addresses in the order you have them in your first spreadsheet. Those email addresses must be listed in the column to the right of the names in the second spreadsheet, or Excel won't be able to find them. (Go figure ... )

The formula needs a unique identifier to retrieve data.

The secret to how VLOOKUP works? Unique identifiers.

唯一的标识符是您两个数据源共享的一项信息，并且（如其名称所暗示）是唯一的（即标识符仅与数据库中的一个记录相关联）。唯一标识符包括产品代码，库存单元（SKU）和客户联系人。

Alright, enough explanation: let's see another example of the VLOOKUP in action!

VLOOKUP Example

In the video below, we'll show an example in action, using the VLOOKUP function to match email addresses (from a second data source) to their corresponding data in a separate sheet.

Author's note:Excel有许多不同的版本，因此您在上面的视频中看到的可能并不总是与您在版本中看到的内容完全匹配。这就是为什么我们鼓励您遵循下面的书面说明。

For your reference, here's what a VLOOKUP function looks like:

vlookup（lookup_value，table_array，col_index_num，range_lookup）

在下面的步骤中，我们将使用客户名称作为我们唯一的标识符来找到每个客户的MRR。

1. Identify a column of cells you'd like to fill with new data.

请记住，您希望从另一张纸上检索数据并将其存入该数据。考虑到这一点，请在单元格旁边的一列标记您想要的更多信息，并在顶部单元格中使用一个适当的标题，例如“ MRR”，以进行每月重复的收入。这本新列是您要获取的数据所在的位置。

2. Select 'Function' (Fx) > VLOOKUP and insert this formula into your highlighted cell.

To the left of the text bar above your spreadsheet, you'll see a small function icon that looks like a script:Fx。单击您的列标题下方的第一个空单元格，然后单击此功能图标。一个标题的盒子配方制造商orInsert Function将出现在屏幕右侧（取决于您拥有的Excel版本）。

从公式构建器中包含的选项列表中搜索并选择“ vlookup”。然后，选择OKorInsert Function开始构建您的vlookup。您目前在电子表格中突出显示的单元格现在应该看起来像这样：”=VLOOKUP()“

您也可以通过将上面的大胆文本精确地输入所需的单元格中输入该公式。

With the =VLOOKUP text entered into your first cell, it's time to fill the formula with four different criteria. These criteria will help Excel narrow down exactly where the data you want is located and what to look for.

3. Enter the lookup value for which you want to retrieve new data.

第一个标准是查找值,这是the value of your spreadsheet that has data associated with it, which you want Excel to find and return for you. To enter it, click on the cell that carries a value you're trying to find a match for. In our example, shown above, it's in cell A2. You'll start migrating your new data into D2, since this cell represents the MRR of the customer name listed in A2.

Keep in mind your lookup value can be anything: text, numbers, website links, you name it. As long as the value you're looking up matches the value in the referring spreadsheet -- which we'll talk about that in the next step -- this function will return the data you want.

4. Enter the table array of the spreadsheet where your desired data is located.

在“表阵列”字段旁边，使用上面的屏幕截图中显示的格式，输入要搜索的单元格和这些单元格的位置范围。上面的条目意味着我们要查找的数据位于标题为“页面”的电子表格中，可以在B列和K列之间找到任何地方。

The sheet where your data is located must be within your current Excel file.This means your data can either be in a different table of cells somewhere in your current spreadsheet,or如下所示，在工作簿底部的不同电子表格中。

For example, if your data is located in "Sheet2" between cells C7 and L18, your table array entry will be "Sheet2!C7:L18."

5. Enter the column number of the data you want Excel to return.

在表格数组字段下方，您将输入您要搜索的表数组的“列索引号”。例如，如果您专注于B列通过K（在“表数组”字段中输入时“ B：K”），但是您想要的特定值是在K列中，则将输入“ 10” IN“列索引号”字段，因为列列是左侧的第10列。

6. Enter your range lookup to find an exact or approximate match of your lookup value.

In situations like ours, which concerns monthly revenue, you want to findexactmatches from the table you're searching through. To do this, enter "FALSE" in the "range lookup" field. This tells Excel you want to find only the exact revenue associated with each sales contact.

要回答您的燃烧问题：是的，您可以允许Excel寻找近似match instead of an exact match. To do so, simply enter TRUE instead of FALSE in the fourth field shown above.

When VLOOKUP is set for an approximate match, it's looking for data that most closely resembles your lookup value, rather than data that is identical to that value. If you're looking up data associated with a list of website links, for example, and some of your links have "https://" at the beginning, it might behoove you to find an approximate match just in case there are links that do not have this "https://" tag. This way, the rest of the link can match without this initial text tag causing your VLOOKUP formula to return an error if Excel can't find it.

7.单击“完成”（或“输入”），然后填写新列。

In order to officially bring in the values you want into your new column from Step 1, click "Done" (or "Enter," depending on your version of Excel) after filling the "range lookup" field. This will populate your first cell. You might take this opportunity to look in the other spreadsheet to make sure this was the correct value.

If so, populate the rest of the new column with each subsequent value by clicking the first filled cell, then clicking the tiny square that appears on the bottom-right corner of this cell. Done! All your values should appear.

vlookup不起作用？

如果你遵循以上steps and your VLOOKUP is still not working, it will either be an issue with your:

• 句法（即您如何结构公式）
• Values(i.e. whether the data it's looking up is good and formatted correctly)

Troubleshooting VLOOKUP Syntax

首先查看您在指定单元格中写的Vookup公式。

• 它是指其关键标识符的正确查找值吗？
• Does it specify the correct table array range for the values it needs to retrieve
• 它是否为该范围指定正确的表格？
• Is that sheet spelled correctly?
• Is it using the correct syntax to refer to the sheet? (e.g. Pages!B:K or 'Sheet 1'!B:K)
• 是否指定了正确的列号？（例如a是1，b是2，依此类推）
• 是对还是错，是如何设置工作表的正确路线？

Troubleshooting VLOOKUP Values

If the syntax is not the problem, how you may have an issue with the values you're trying to receive themselves. This often manifests as an#N/A errorwhere the VLOOKUP cannot find a referenced value.

• Are the values formatted vertically and from right to left?
• Do the values match how you refer to them?

例如，如果您要查找URL数据，则每个URL必须是同一行中其左侧的相应数据的行。如果您将URL作为列标题具有垂直移动的数据，则Vlookup将无法工作。

Keeping with this example, the URLs must match in format in both sheets. If you have one sheet including the "https://" in the value while the other sheet omits the "https://", the VLOOKUP will not be able to match the values.

VLOOKUPs as a Powerful Marketing Tool

Marketers have to analyze data from a variety of sources to get a complete picture of lead generation (and more). Microsoft Excel is the perfect tool to do this accurately and at scale, especially with the VLOOKUP function.

Editor's note: This post was originally published in March 2019 and has been updated for comprehensiveness.

Originally published Jul 14, 2021 1:15:00 PM, updated July 14 2021