人生(Life)は散らかっていますね。財務の追跡や時間の管理などは面倒で時間がかかります。それでも、これらは、整理された場合、あなたの人生を改善するものです。スプレッドシートは、この種のタスクを毎日行うのに役立ちます。(Spreadsheets can help every day)
ただし、スプレッドシートで情報を見つけるのは難しい場合があります。そのため、 Googleスプレッドシートの(Sheets)VLOOKUP関数を使用して、スプレッドシートで何かを簡単に見つけられるようにする方法を紹介します。
VLOOKUPは、スプレッドシートの最初の列で何かを見つけるためのSheets関数です。(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に通知するため、 (VLOOKUP)FALSEを使用します。
上記のVLOOKUP関数は、セルE1にある値を検索キーとして使用します。A1からC5までのセル範囲の列Aで一致が見つかると、一致が見つかったのと同じ行の3番目の列を調べて、その中の値を返します。下の画像は、セルE1に(E1)4を入力した結果を示しています。次に、 Googleスプレッドシートで(Google Sheets)VLOOKUP関数を使用するいくつかの方法を見てみましょう。
例1：ジョブの追跡にVLOOKUPを使用する(Example 1: Using VLOOKUP For Tracking Jobs)
あなたがサービスビジネスをしていて、作業指示がいつ始まるかを知りたいとしましょう。ワークシートを1つ作成し、作業指示番号まで下にスクロールしてから、行を調べて開始時刻を確認することができます。それは退屈になり、エラーが発生しやすくなります。
または、 VLOOKUP(VLOOKUP)を使用することもできます。
- ワークシートのどこかに「作業指示書」(Work Order )と「作業日(Work Date )」という見出しを入力します。
- 作業日(Work Date )の右側のセルを選択し、数式=VLOOKUPの入力を開始します。入力するとヘルプボックスがポップアップ表示され、入力内容に一致する利用可能なGoogleスプレッドシート機能が表示されます。(Google Sheet functions)VLOOKUPが表示されたら、 Enterキー(Enter, )を押すと、入力が完了します。
- VLOOKUPが検索キー(Search Key)を見つける場所を設定するには、このすぐ上のセルをクリックします。
- 検索するデータの範囲(Range )を選択するには、 A列ヘッダーをクリックして押したままドラッグし、列Hを含むすべてを選択します。
- データを取得するインデックス(Index)または列を選択するには、 AからHまでカウントします。Hは7番目の列なので、数式に7を入力します。(7)
- 次に、範囲の最初の列をどのように検索するかを示します。完全に一致する必要があるため、FALSEと入力します。
(Notice)FALSEの後に開き角かっこを入れたいことに注意してください。それを削除するには、バックスペースを 押します。(Press)
次に、湾曲した閉じ括弧））を入力し、 ())Enterキー(Enter )を押して数式を終了します。
エラーメッセージが表示されます。それで大丈夫です; 私たちは物事を正しく行いました。問題は、検索キーの値がまだないことです。
VLOOKUP数式をテストするには、数式の上のセルに最初の作業指示番号を入力し、 Enterキー(Enter)を押します。返される日付は、作業指示書A00100のWorkDate列の日付と一致します。
これにより作業がどのように楽になるかを確認するには、A00231のように、画面に表示されていない作業指示番号を入力します。
返された日付とA00231の行の日付を比較すると、一致しているはずです。もしそうなら、式は良いです。
例2：VLOOKUPを使用して1日のカロリーを計算する(Example 2: Using VLOOKUP to Calculate Daily Calories)
作業指示書(Order)の例は良いですが単純です。毎日のカロリー計算機を作成して、Googleスプレッドシートで(Google Sheets)VLOOKUPの実際のパワーを見てみましょう。データを1つのワークシートに入れ、カロリー計算機を別のワークシートに作成します。
- (Select)食品とカロリーのリストからすべてのデータを 選択します。
- [データ(Data )] >[名前付き範囲](Named Ranges)を選択します。
- 範囲にFoodRangeという名前を付けます。名前付き範囲(Named ranges)は、範囲の実際の定義であるSheet2!A1:B:29よりも覚えやすいです。
- 食品が追跡されているワークシートに戻ります。カロリーを表示する最初のセルに、数式=VLOOKUP(A3,FoodRange,2,False)を入力できます。
動作しますが、 A3(A3)には何もないため、醜い#REF エラーが発生します。この計算機では、多くのFoodセルが空白のままになっている可能性があり、その全体に(Food)＃REFを表示したくありません。
- VLOOKUP式をIFERROR関数の中に入れましょう。IFERRORは、数式に問題がある場合は空白を返すようにSheetsに通知します。
- 数式を列の下にコピーするには、セルの右下隅にあるハンドルを選択し、必要な数のセルの上にドラッグします。
数式で列のキーとしてA3が使用されると思われる場合でも、心配する必要はありません。シートは、数式が含まれている行のキーを使用するように数式を調整します。たとえば、次の画像では、4行目に移動するとキーがA4に変更されたことがわかります。(A4 )数式(Formulas)は、列から列に移動すると、このようなセル参照(change cell references)も自動的に変更されます。
- 1日のすべてのカロリーを合計するには、[合計(Total)]の横の空白のセルで=SUM 関数を使用し、その上のカロリーのすべての行を選択します。
これで、今日のカロリー数を確認できます。
- 月曜日(Monday )のカロリーの列を選択し、火曜日(Tuesday)、水曜日などの(Wednesday)カロリー(Calories )列に貼り付けます。
月曜日より下の(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 messy, isn’t it? Things like tracking finances and managing time are messy and time-consuming. Yet, these аre 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.
