生活(Life)很混乱，不是吗？跟踪财务和管理时间之类的事情既麻烦又耗时。然而，如果把这些事情整理好，就会改善你的生活。电子表格可以帮助每天(Spreadsheets can help every day)完成这些类型的任务。
但是，在电子表格中查找信息可能具有挑战性。这就是为什么我们将向您展示如何使用Google表格中的(Sheets)VLOOKUP功能来更轻松地在电子表格中查找内容。
VLOOKUP 是一个表格功能(Sheets function)，用于在电子表格的第一列中查找内容。V 代表垂直，因为就像建筑物上的列一样，电子表格的列是垂直的。因此，当VLOOKUP找到我们要查找的关键内容时，它会告诉我们该行中特定单元格的值。
VLOOKUP 函数解释(The VLOOKUP Function Explained)
下图中是VLOOKUP函数的语法。这就是函数的布局方式，无论它在哪里使用。
该函数是=VLOOKUP( ) 部分。函数内部有：
- 搜索键(Search Key )-告诉 VLOOKUP(Tells VLOOKUP)它需要查找什么。
- Range –告诉 VLOOKUP(Tells VLOOKUP)在哪里寻找它。VLOOKUP将始终在范围的最左侧列中查找。
- 索引(Index)-告诉 VLOOKUP(Tells VLOOKUP)如果找到搜索键的匹配项，则该范围内最左侧列的右侧有多少列来查找值。最左侧的列始终为 1，其右侧的下一列始终为 2，依此类推。
- 排序了吗？(Is sorted?)–告诉 VLOOKUP(Tells VLOOKUP)第一列是否已排序。这默认为TRUE，这意味着VLOOKUP将找到与搜索键最近的匹配项。这可能导致不太准确的结果。FALSE告诉VLOOKUP它必须是完全匹配的，所以使用FALSE。
上面的VLOOKUP函数将使用单元格(VLOOKUP)E1中的任何值作为其搜索键。当它在从A1到C5的单元格范围的A列中找到匹配项时，它将在找到匹配项的同一行的第三列中查找并返回其中的任何值。下图显示了在单元格E1中输入(E1)4的结果。接下来，让我们看看在Google 表格中使用(Google Sheets)VLOOKUP功能的几种方法。
示例 1：使用 VLOOKUP 跟踪作业(Example 1: Using VLOOKUP For Tracking Jobs)
假设您有一家服务企业，并且您想了解工单何时开始。您可以有一个工作表，向下滚动到工作订单号，然后查看该行以了解它何时开始。这会变得乏味且容易出错。
或者你可以使用VLOOKUP。
- 在工作表的某处输入标题工作订单(Work Order )和工作日期。(Work Date )
- 选择工作日期(Work Date )右侧的单元格并开始输入公式=VLOOKUP。我们键入时会弹出一个帮助框，向我们显示与我们键入的内容相匹配的可用Google 表格功能。(Google Sheet functions)当它显示VLOOKUP时，按Enter，它将完成输入。
- 要设置 VLOOKUP 将在何处找到Search Key，请单击其正上方的单元格。
- 要选择要搜索的数据范围(Range )，请单击并按住A列标题并拖动以选择所有内容，包括H列。
- 要选择我们要从中提取数据的索引(Index)或列，请从A计数到H。H是第 7 列，所以在公式中输入7 。
- 现在我们说明我们希望如何搜索范围的第一列。我们需要完全匹配，所以输入FALSE。
请注意(Notice)，它想在FALSE之后放置一个开口弯曲括号。按(Press)退格键将其删除。
然后输入一个弯曲的右括号)，然后按Enter完成公式。
我们会看到一条错误消息。没关系; 我们做事正确。问题是我们还没有搜索键值。
要测试VLOOKUP公式，请在公式上方的单元格中输入第一个工作订单号，然后按Enter。返回的日期与工作订单 A00100的WorkDate列中的日期匹配。(WorkDate )
要了解这如何让生活更轻松，请输入一个在屏幕上不可见的工单编号，例如A00231。
比较返回的日期和A00231行中的日期，它们应该匹配。如果他们这样做，则公式很好。
示例 2：使用 VLOOKUP 计算每日卡路里(Example 2: Using VLOOKUP to Calculate Daily Calories)
工单(Order)示例很好但很简单。让我们通过创建每日卡路里计算器来看看VLOOKUP在Google 表格(Google Sheets)中的实际威力。我们将把数据放在一个工作表中，然后将卡路里计算器放在另一个工作表中。
- 选择(Select)食物和卡路里列表上的所有数据。
- 选择数据(Data )>命名范围(Named Ranges)。
- 将范围命名为FoodRange。命名范围比(Named ranges)Sheet2!A1:B:29更容易记住， Sheet2!A1:B:29是范围的实际定义。
- 返回到跟踪食物的工作表。在我们想要显示卡路里的第一个单元格中，我们可以输入公式=VLOOKUP(A3,FoodRange,2,False)。
它会起作用，但因为A3中没有任何内容，所以会有一个丑陋的#REF 错误。这个计算器可能有很多食物(Food)单元格留空，我们不想看到#REF 到处(REF)都是。
- 让我们将VLOOKUP公式放入IFERROR函数中。IFERROR告诉表格，如果公式出现任何问题，则返回空白。
- 要将公式复制到列中，请选择单元格右下角的句柄并将其向下拖动到所需数量的单元格上。
如果您认为公式将使用 A3 作为列向下的键，请不要担心。表格将调整公式以使用公式所在行中的键。例如，在下图中，您可以看到键在移动到第 4 行时变为A4 。当从一列移动到另一列时，公式(Formulas)也会像这样自动更改单元格引用。(change cell references)
- 要将一天中的所有卡路里加起来，请在Total旁边的空白单元格中使用=SUM 函数，然后选择其上方的所有卡路里行。
现在我们可以看到我们今天摄入了多少卡路里。
- 选择星期一(Monday )的卡路里列并将其粘贴到星期二(Tuesday)、星期三等的(Wednesday)卡路里(Calories )列。
对Monday(Monday)下面的Total单元格执行相同的操作。所以现在我们有一个每周卡路里计数器。
总结 VLOOKUP(Summing Up VLOOKUP)
如果这是您第一次深入了解Google 表格(Google Sheets)和功能，您会看到VLOOKUP等功能的实用性和强大性。将它与其他功能（如IFERROR或许多其他功能）结合使用将帮助您做任何您需要的事情。如果您喜欢这个，您甚至可以考虑从 Excel 转换为 Google 表格(converting from Excel to Google Sheets)。
How to Use VLOOKUP in Google Sheets
Life is mesѕy, isn’t it? Things like tracking financеs and managing time are messy and time-consuming. Yet, these are things that, if put in order, wоuld improve your life. Spreadsheets can help every day with these sorts of tasks.
It can be challenging to find information in spreadsheets, however. That’s why we’re going to show you how to use the VLOOKUP function in Google Sheets to make finding something in a spreadsheet a lot easier.
VLOOKUP is a Sheets function to find something in the first column of a spreadsheet. The V is for vertical because, like columns on a building, spreadsheet columns are vertical. So when VLOOKUP finds the key thing we’re looking for, it will tell us the value of a specific cell in that row.
The VLOOKUP Function Explained
In the image below is the VLOOKUP function’s syntax. This is how the function is laid out, regardless of where it’s being used.
The function is the =VLOOKUP( ) part. Inside the function are:
- Search Key – Tells VLOOKUP what it needs to find.
- Range – Tells VLOOKUP where to look for it. VLOOKUP will always look in the leftmost column of the range.
- Index – Tells VLOOKUP how many columns to the right of the left-most column in the range to look for a value if it finds a match of the search key. The left-most column is always 1, the next to its right is 2, and so on.
- Is sorted? – Tells VLOOKUP if the first column is sorted. This defaults to TRUE, which means VLOOKUP will find the nearest match to the search key. This can lead to less accurate results. FALSE tells VLOOKUP that it must be an exact match, so use FALSE.
The VLOOKUP function above will use whatever value is in cell E1 as its search key. When it finds a match in column A of the range of cells from A1 to C5, it will look in the third column of the same row as it found the match and return whatever value is in it. The image below shows the results of entering 4 in cell E1. Next, let’s look at a couple of ways to use the VLOOKUP function in Google Sheets.
Example 1: Using VLOOKUP For Tracking Jobs
Let’s say you have a service business and you want to find out when a work order starts. You could have a single worksheet, scroll down to the work order number and then look across the row to find out when it starts. That can become tedious and prone to error.
Or you could use VLOOKUP.
- Enter the headings Work Order and Work Date somewhere on the worksheet.
- Select the cell to the right of Work Date and start entering the formula =VLOOKUP. A help box will pop up as we type, showing us available Google Sheet functions that match what we’re typing. When it shows VLOOKUP, press Enter, and it will complete the typing.
- To set where VLOOKUP will find the Search Key, click on the cell right above this.
- To select the Range of data to search in, click and hold on the A column header and drag to select everything over to, including column H.
- To select the Index, or column, that we want to pull data from, count from A to H. H is the 7th column so enter 7 in the formula.
- Now we state how we want the first column of the range to be searched. We need an exact match so enter FALSE.
Notice that it wants to put an opening curved bracket after FALSE. Press backspace to remove that.
Then enter a curved closing bracket ), and press Enter to finish the formula.
We’ll see an error message. That’s ok; we did things correctly. The issue is that we don’t have a search key value yet.
To test the VLOOKUP formula, enter the first work order number in the cell above the formula and press Enter. The date returned matches the date in the WorkDate column for work order A00100.
To see how this makes life easier, enter a work order number that isn’t visible on the screen, like A00231.
Compare the date returned and the date in the row for A00231, and they should match. If they do, the formula is good.
Example 2: Using VLOOKUP to Calculate Daily Calories
The Work Order example is good but simple. Let’s see the actual power of VLOOKUP in Google Sheets by creating a daily calorie calculator. We’ll put the data in one worksheet and make the calorie calculator in another.
- Select all the data on the food and calorie list.
- Select Data > Named Ranges.
- Name the range FoodRange. Named ranges are easier to remember than Sheet2!A1:B:29, which is the actual definition of the range.
- Go back to the worksheet where food is tracked. In the first cell in which we want calories to show, we could enter the formula =VLOOKUP(A3,FoodRange,2,False).
It would work, but because there’s nothing in A3, there will be an ugly #REF error. This calculator might have many Food cells left blank and we don’t want to see #REF all over it.
- Let’s put the VLOOKUP formula inside an IFERROR function. IFERROR tells Sheets that if anything goes wrong with the formula, return a blank.
- To copy the formula down the column, select the handle at the bottom-right corner of the cell and drag it down over as many cells as needed.
If you think that the formula will use A3 as the key down the column, don’t worry. Sheets will adjust the formula to use the key in the row that the formula is in. For example, in the image below, you can see that the key changed to A4 when moved to the 4th row. Formulas will automatically change cell references like this when moved from column to column, too.
- To add up all the calories in a day, use the =SUM function in the blank cell next to Total, and select all the rows of calories above it.
Now we can see how many calories we had today.
- Select the column of calories from Monday and paste it to the Calories column for Tuesday, Wednesday, and so on.
Do the same for the Total cell below Monday. So now we have a weekly calorie counter.
Summing Up VLOOKUP
If this is your first dive into Google Sheets and functions, you can see how useful and powerful functions like VLOOKUP can be. Combining it with other functions like IFERROR, or so many others, will help you do whatever you need. If you enjoyed this, you might even consider converting from Excel to Google Sheets.
