Microsoft Excel中的VLOOKUP函数字面意思是 垂直查找(vertical lookup)。它是一种用于查询列单元格中的值的搜索功能。此函数搜索与左侧第一列中的条目相关的数据。
在处理具有大量列和行的表时，垂直数据搜索最为重要。Excel 的VLOOKUP(VLOOKUP)功能无需滚动和分析数百个单元格，而是通过从上到下查找值来帮助您找到要查找的数据。
创建(Create)、构建和使用 Excel 的VLOOKUP函数
在我们的示例中，我们将使用VLOOKUP函数来搜索有关七名员工工资的信息。本节向您展示如何通过以下方式使用VLOOKUP功能：(VLOOKUP)
- 编写 Excel VLOOKUP 函数。
- 在 Excel 中构建 VLOOKUP 函数。
事不宜迟，让我们开始吧。在第一种方法中，我们将手动创建函数。接下来，我们将从 Excel 的内置 函数参数(Functions Arguments)向导中使用它。
1]编写Excel VLOOKUP函数
启动Microsoft Excel并为充当唯一标识符的值创建一列。我们将其称为 参考列(reference column)。
在第一步中创建的第一个列的右侧添加更多列，并为这些列中的单元格插入值。
单击(Click)电子表格中的空白单元格，然后从您希望为其搜索数据的员工的参考列中输入员工 ID 。(Employee ID)
(Select)在Excel(Excel)将存储公式并因此显示返回值的电子表格中选择另一个空单元格。在这里，输入以下公式：
=VLOOKUP()
输入上述公式后，Excel建议使用VLOOKUP语法：
=VLOOKUP(vlookup_value,table_array,col_index_num,range_lookup)
参数或参数
以下是上述参数在语法中定义的内容：
- lookup_value： 具有来自参考列的产品标识符的单元格。
- table_array：从 with 到 search 的数据范围。它必须包含引用列和包含您正在查找的值的列。在大多数情况下，您可以使用整个工作表。您可以将鼠标拖到表的值上以选择数据范围。
- col_index_num：要从中查找值的列的编号。你把它从左到右放进去。
- range_lookup: TRUE表示近似匹配，FALSE表示完全匹配。该值默认为TRUE，但您通常使用FALSE。
有了这些信息，我们现在将括号中的参数替换为我们希望查找的信息。例如，要返回Wayne Creed的薪水，请输入以下公式：
=VLOOKUP(C14,B5:E11,6,FALSE)
在使用VLOOKUP(VLOOKUP)公式离开单元格时，它会返回您查询的值。如果您收到 #N/A错误，请阅读此Microsoft 指南以了解如何更正它(Microsoft guide to learn how to correct it)。
2]在Excel中(Excel)构建(Build)VLOOKUP函数
第一部分向您展示了如何手动创建VLOOKUP函数。(VLOOKUP)如果您认为上述方法很简单，请等到您阅读本文。在这里，您将学习如何使用用户友好的函数参数 (Functions Arguments )向导快速构建VLOOKUP函数。(VLOOKUP)
首先打开Microsoft Excel，然后创建一个包含唯一标识符的引用列。
接下来，在参考列的右侧创建更多列。在这里，我们将在参考列中插入项目的相关值。
选择(Select)一个空单元格并从参考单元格中输入一个值。这是我们将查找其属性的值。
单击(Click)另一个空单元格。选中后，单击“ 公式(Formulas)”选项卡。
从函数库(Functions Library)中选择 查找和引用 (Lookup & Reference )工具，然后从下拉菜单中选择VLOOKUP 。这将打开函数参数(Functions Arguments)向导。
在第一种方法中指定的函数参数(Functions Arguments)向导中填写Lookup_value、Table_array、Col_index_num和Range_lookup字段。
完成后点击 OK按钮，VLOOKUP函数将返回您输入的参数的结果。
如果Excel 公式无法自动更新，本指南将为您提供帮助。
这两种方法都将成功查询您需要参考第一列的数据。公式参数(Formulas Argument)向导可以轻松输入变量以使VLOOKUP函数(VLOOKUP)工作。
但是，VLOOKUP功能也适用于Excel 的 Web 版本。您还可以使用函数参数(Functions Argument)向导或在 Web 版本上手动创建VLOOKUP函数。(VLOOKUP)
现在让我们看一下Excel中的HLOOKUP函数(HLOOKUP function in Excel)。
How to write, build, and use VLOOKUP function in Excel
The VLOOKUP function in Microsoft Excel literally means vertical lookup. It’s a search function for querying values in the cell of a column. This function searches for the data relative to the entries in the first column from the left.
A vertical data search is most vital when dealing with tables with numerous columns and rows. Instead of scrolling through and analyzing hundreds of cells, Excel’s VLOOKUP function helps you find the data you’re looking for by looking up the values from top to bottom.
Create, build & use Excel’s VLOOKUP function
In our example, we’ll work with a VLOOKUP function that searches for information about seven employees’ salaries. This section shows you how to use the VLOOKUP function in the following ways:
- Write the Excel VLOOKUP function.
- Build a VLOOKUP function in Excel.
Without further ado, let’s get to it. In the first method, we’ll create the function manually. Next, we’ll use it from Excel’s inbuilt Functions Arguments wizard.
1] Write the Excel VLOOKUP function
Launch Microsoft Excel and make a column for the values that act as unique identifiers. We’ll call this the reference column.
Add some more columns to the right-hand side of the first one you created in the first step and insert values for the cells in these columns.
Click on an empty cell in the spreadsheet and type in an Employee ID from the reference column of an employee for whom you wish to search for data.
Select another empty cell on the spreadsheet in which Excel will store the formula and hence display the returned value. Here, enter the following formula:
=VLOOKUP()
On entering the above formula, Excel suggests the VLOOKUP syntax:
=VLOOKUP(vlookup_value,table_array,col_index_num,range_lookup)
Arguments or parameters
Here are what the above arguments define in the syntax:
- lookup_value: the cell with the product identifier from the reference column.
- table_array: the data range from with to search. It must contain the reference column and the column containing the value you’re looking up. In most cases, you can use the entire worksheet. You can drag your mouse over the values of the table to select a data range.
- col_index_num: the number of the column from which to look up a value. You put this in from left to right.
- range_lookup: TRUE for an approximate match, FALSE for an exact match. The value is TRUE by default, but you generally use FALSE.
With this information, we’ll now replace the parameters in the parenthesis with the information we wish to look up. For example, to return Wayne Creed‘s salary, enter the following formula:
=VLOOKUP(C14,B5:E11,6,FALSE)
On navigating away from the cell with the VLOOKUP formula, it returns the value for which you queried. If you get a #N/A error, read this Microsoft guide to learn how to correct it.
2] Build a VLOOKUP function in Excel
The first part showed you how to create a VLOOKUP function manually. If you thought the above method was easy, wait till you read this. Here, you’ll learn how to build a VLOOKUP function quickly using the user-friendly Functions Arguments wizard.
Open Microsoft Excel first, and create a reference column that will contain unique identifiers.
Next, create some more columns on the right-hand side of the reference column. Here, we’ll insert the relevant values for the items on the reference column.
Select an empty cell and type in a value from the reference cell. This is the value whose properties we’ll lookup.
Click on another empty cell. With that selected, click on the Formulas tab.
Select the Lookup & Reference tool from the Functions Library and choose VLOOKUP from the dropdown menu. This opens the Functions Arguments wizard.
Fill in the Lookup_value, Table_array, Col_index_num, and Range_lookup fields in the Functions Arguments wizard specified in the first method.
Hit the OK button when you’re done, and the VLOOKUP function will return the results from the arguments you entered.
This guide will help you if the Excel formula fails to update automatically.
Both methods will successfully query the data you need in reference to the first column. The Formulas Argument wizard makes it easy to input the variables to make the VLOOKUP function work.
However, the VLOOKUP function also works on the web version of Excel. You also get to use the Functions Argument wizard or create the VLOOKUP function manually on the web version.
Let us take a look at the HLOOKUP function in Excel now.
