Google スプレッドシートでデータを操作するのに数式や関数を使わなければならないと、背筋が凍りつく思いでした。私はバックアップ（私の開発パートナー）を呼び、手動で作成すると 2 時間かかるデータを、数式を使用して 2 分で表示するのを驚きながら見ていました。
しかし、私は生きてきて、笑い、学び、今では、手動で追加することなくスプレッドシートのある部分から別の部分にデータを取得できる VLOOKUP などのいくつかの基本的な関数の使い方を知っています。
ここでは、Google スプレッドシートで VLOOKUP を使用する方法をステップごとに説明し、さらに機能を強化するためのヒントをいくつか紹介します。チュートリアルを進める際は、 デモ用のスプレッドシートのコピーを作成して、それに従ってください。
目次：
Google スプレッドシートの VLOOKUP 関数とは何ですか?
Google スプレッドシートに何千人もの従業員の名前と ID 番号が記載された大きな表があり、その従業員情報を組織図や業績評価などのスプレッドシートの別の部分に取り込む必要があると想像してください。VLOOKUP は、別の列からわかっている値に基づいて 1 つの列の情報を検索することにより、スプレッドシートのある部分から別の部分に情報をすばやく転送できます。
関数に含まれるコンポーネントは次のとおりです。
Table:データの行と列を含むTableがあります。
価値： 名前や ID などの特定の値がわかっていて、より関連性の高い情報を見つけたい場合。
探索： VLOOKUP を使用して、同じ行で別の列でその値を検索します。
取り戻す： 値が見つかると、VLOOKUP は情報を提供します。
電話帳で名前を調べるようなものだと考えてください (覚えていますか?検索している名前が見つかり、そのすぐ隣に電話番号が表示されます。VLOOKUP は Google スプレッドシートのデータに対しても同じことを行います。
VLOOKUP 構文と入力
データが複数のタブに分散されているのではなく 1 つのスプレッドシートに統合されている場合は、結果を入力するセルに次の VLOOKUP 数式を入力する必要があります。
=VLOOKUP(search_key、範囲、インデックス、[is_sorted])
これらの各入力の意味は次のとおりです。
search_key: これは、テーブルで探している値です。
範囲： これは、値を検索する領域、または情報が見つかると思われるテーブルのセクションです。少なくとも 2 列である必要があります。
インデックス:これは、目的のデータが配置されている、指定した範囲内の列番号です。たとえば、名前を検索していて、対応する年齢が必要な場合は、年齢がリストされている列番号がインデックスになります。
is_sorted:範囲内のデータがソートされているか (TRUE)、そうでないか (FALSE) を VLOOKUP に伝えます。
TRUE: TRUE に設定すると、Google スプレッドシートはデータが昇順（A から Z、または最小から最大）であると想定し、より高速に検索できます。ただし、これは、完全 一致 ではないが 、近い ものが検索されることも意味します。そのため、ルックアップ値以下に近い最も近い値が検索されます。
FALSE: FALSE に設定すると、Google スプレッドシートは完全一致をより徹底的に検索します。VLOOKUP が完全に一致するものを見つけられない場合は、 #N/A エラーが返されます。
この数式は、Google スプレッドシートに「表のこの特定の部分で見つけてほしい値がここにあります」と言っているようなものです。見つけたら、この列から情報を取得してください。また、データはソートされているので、[素早く見る/注意深く見る]必要があります。」
複数のシートにデータがある場合、関数は次のようになります。
=VLOOKUP(search_key、シート名!範囲、インデックス、[is_sorted])
基本的に、範囲の直前に、取得するデータが含まれているワークシート名と、それに続く感嘆符 (!) を追加します。
GoogleスプレッドシートでVLOOKUPを使う方法
ここで、2 つの基本的なデータ列を使用して、VLOOKUP が実際にどのようにアクションするかを説明します。
実生活で VLOOKUP を使用する必要がある場合は、おそらくはるかに大きく複雑なデータセットを扱うことになります。ただし、関数の使用方法を学ぶために、この例を 非常に シンプルにして、偽の従業員と ID 番号の小さなリストを使用します。私たちの目標は、特定の従業員のID番号を見つけることです。
「簡単な例 - FALSE」タブのデモスプレッドシートに従ってください。
データを整理します。スプレッドシートにデータを入力するか、既存のテーブルを見つけます。(このスプレッドシートにはすでにデータがあります。)
出力セルを選択します。 探している情報が表示されるセルをクリックします。この場合は、セル23 A をクリックします。
VLOOKUP 関数を入力します。そのセルに VLOOKUP 関数を入力します。
=VLOOKUP(search_key, range, index, [is_sorted])
search_keyを入力します。search_keyを探している従業員の名前に置き換えます。この例では Mia を探すので、検索キーとして
A17を入力します。
値の範囲を設定します。ここで、範囲を検索したいデータが含まれるセルに置き換えます。この場合、データは列 A と列 B にあるので、範囲を
A:Bに置き換えます。
インデックス列を設定します。次に、index を必要なデータが含まれている列番号に置き換えます。インデックス列を見つけるには、左端の列からカウントします。左から 2 番目の列である ID 番号列からの情報が必要です。そこで、インデックスに
2を入力します。
is_sorted値を決定します。この例では、データは順番にソートされていないため、[is_sorted] には
FALSEを使用する必要があります。
関数を実行します。すべての入力を入力したら、Enter キーを押します。すべてを正しく行った場合、関数は探していた値を返すはずです。この場合、Mia の従業員 ID 番号 123789が返されました。
VLOOKUPの例とテンプレート
基本を理解したところで、より複雑な例を試してみましょう。デモスプレッドシートの「例 - TRUE」タブに移動して、手順に従ってください。
この例では、従業員とその給与のリストがあります。これらの従業員も給与に基づいて利益分配を受けており、その割合を埋める必要があります。各従業員が受け取る利益分配率を手動で入力するのは非常に時間がかかりますが、VLOOKUP を使用すれば自動的に入力できます。その方法は次のとおりです。
データを整理します。最初の表は、給与範囲とそれに対応する利益分配率を示しています。しかし、VLOOKUP はダッシュとスペースがあるため給与範囲データを理解できません。代わりに、範囲の下限の数字のみを使用して、2 番目の表のように給与データを整理します。この数式をショートカットとして使用します:
=LEFT(A2, SEARCH("-", A2)-1)。2は給与範囲の開始セルであり (したがって、独自の開始セル番号に置き換えます)、すべての範囲の下限番号が得られるまで数式を下にドラッグできます。
出力セルを選択します。 情報を入力したいセルをクリックします。ほら、それはF 2 だ。ここから各従業員の利益配分率が始まる。
VLOOKUP 関数を入力します。VLOOKUP 関数をそのセルに貼り付けます。
=VLOOKUP(search_key, range, index, [is_sorted])
search_keyを入力します。 私たちはジョンの利益分配率、より具体的にはジョンの給与を望んでいます。そこで、search_keyを
E2に置き換えます。
値の範囲を設定します。 私たちの価値範囲は、対応する給与と利益分配率で作成した 2 番目の表になります。したがって、range を
A15:B25に置き換えます。
インデックス列を設定します。 選択した表の 2 列目にある利益分配率を知りたいです。そこで、index を
2に置き換えます。
is_sorted値を決定します。 データはソートされており、完全一致を求めているわけではないので、[is_sorted] を
TRUEに置き換えます。TRUE を入力すると、Google スプレッドシートは最も近い一致、つまりこの場合に必要な値以下の値を検索します。
関数を実行します。 Enterキーを押します。すべてが正しく行われていれば、Google スプレッドシートで正しい利益分配率（この場合は1 %）が表示されます。
関数をドラッグします。 全員の対応する利益分配率を知りたい場合は、関数 (F 2 ) を入力したセルの右下隅にある青い点をクリックして、一番下までドラッグします。以下のエラー コードは無視してください。最初の数式が正しく実行されていれば、提案の横にあるチェック マークをクリックすると、すべてが正しく読み込まれるはずです。
その結果、各従業員の機能をマニュアルで入力しなくても、各従業員の正しい利益分配率が得られます。
VLOOKUPが機能しませんか?一般的なエラーのトラブルシューティング
VLOOKUPは想定どおりに動作していませんか?誰も驚かない。ここでは、ほぼ確実に遭遇する一般的なエラーと、その修正方法をいくつか紹介します。
VLOOKUP は予期しない値を返します。 VLOOKUP 関数が探していない値を返した場合は、数式を再確認してください。これは、[is_sorted] が
TRUEに設定されているが、選択した範囲の最初の列が数値またはアルファベット順に昇順に並べ替えられていない場合に発生する可能性があります。トラブルシューティングするには、[is_sorted] をFALSEに変更します。VLOOKUP は、最初に一致する値のみを返します。 設計上、VLOOKUP は常に最初に見つかった結果を返します。一致する検索キーが複数ある場合は、VLOOKUP が適切に検索できるように、それらに一意の値を割り当てる必要があります。
不正確なデータを含む VLOOKUP:余分なスペースやその他の誤植のある値を検索する場合、VLOOKUP は正しく機能しない可能性があります。シート内の不要なスペースを必ず削除してください。まず、 「データ」 > 「データのクリーンアップ」 > 「空白のトリム」に進みます。
VLOOKUP #N/A エラーを返します。 このエラーは、VLOOKUP を使用して、最初の列に完全一致が存在しない値を検索した場合に発生する可能性があります。
複数の条件で VLOOKUP を使用する方法
顧客が購入した商品の数を確認したいが、同じ名前の顧客が複数いるとします (Tim Johnson さん)。通常、VLOOKUP 関数は 1 つの検索値に制限されていますが、少し余分な作業で複数の条件をスキャンできます。その方法は次のとおりです。
ルックアップ列の左側に新しい「ヘルパー」列を挿入します。これは、テーブルの左端の列になります。
この列の最初のセル（データが行2から始まる場合は A 2 ）に、数式
=B 2 & " " & C 2を入力します。これにより、既存の列の値が結合され、スペースで区切られます。その数式をヘルパー列の残りのセルにコピーします。
標準の VLOOKUP 式を使用して、検索する両方の条件をスペースで区切って lookup_value 引数に配置します。この例では、ID 番号が 112233の John という名前のユーザーを検索したいと思います。私が使用した式は次のとおりです。
=VLOOKUP("John 112233", A2:D22, 4, FALSE)数式は、検索条件(私の例では数量 4)に基づいて値を返します。
A2:D22 を、ヘルパー列を含むテーブルの実際の範囲に置き換える必要があります。数字の 4 は、テーブルの 4 番目の列から値を返すことを示します。必要に応じてこれを調整することもできます。
別のシートからVLOOKUPする方法
データが 2 つのシートに分散されている場合 (たとえば、1 つのシートに利益分配の変換が保存され、もう 1 つのシートに従業員のリストと給与が保存されている場合)、VLOOKUP 数式を調整する必要があります。この関数の例は、デモ スプレッドシートの「シート1 」タブと「シート2 」タブにあります。
この例では、データを 2 つのシート (シート1とシート2 ) に分割する場合、数式を次のように調整します: =VLOOKUP(B 2 , Sheet 2 ! A 15 :B 25 , 2 , TRUE)
これにより、範囲の直前にワークシート名と感嘆符が追加され、データの取得に使用するワークシートが VLOOKUP に通知されます。
次に、数式は、シート2の A15:B25範囲の B2 の値を検索します。次に、シート1の指定されたセルの列 2 から一致するものを返します。
GoogleスプレッドシートでVLOOKUPを使用するためのその他のヒント
あなたが私と同じで、プロセスを通して誰かに手を握ってもらう必要がある場合は、VLOOKUP を使用するための追加のヒントをいくつか紹介します。
データを整理しておく
整理されたデータにより、VLOOKUP 数式でエラーが発生する可能性が減ります。ここにいくつかのヒントがあります。
ヘッダーを使用します。 列に明確でわかりやすいヘッダーを使用すると、各列が何を表しているのかを理解しやすくなります。その後、VLOOKUP 関数を実行すると、データを取得する必要がある場所がすぐにわかります。
データを並べ替えます。完全一致を探していない場合は、データを昇順で並べ替えて、is_sorted パラメータを TRUE としてマークする必要があります。
空のセルを削除します。 データセット内の空のセルは、エラーや不正確な一致につながる可能性があります。
左端の列の値を事前に並べ替える
VLOOKUP関数の厄介な癖の1つは、左を見ることができないことです。開始する前に、search_keyを格納する列が範囲の左端の列であることを確認してください。
先ほどの簡単な例を見てみましょう。名前の前にID番号が来るように列の順序を切り替えると、関数はエラーを返します。これは、search_key として B 17選択し、その左側の列からデータを取得するように要求したためです。
高度な機能に INDEX/MATCH を使用する
しかし、柱を動かすのが大きな苦痛だとしたらどうでしょうか?データセットが非常に大きいか、スプレッドシート内に移動できない数式がある可能性があります。
その場合は、VLOOKUP を廃止し、INDEX/MATCH を使用して柔軟性を高める必要があります。その式は次のとおりです。 =INDEX(array or reference,MATCH(lookup_value,lookup_array,[match_type])
このアクションは、テンプレートの「INDEX/MATCH の例」タブで確認できます。
同じ単純な例では、その式は次のようになります =INDEX(A2:A21,MATCH(B17,B2:B21,0))
その理由は次のとおりです。
配列または参照は、探しているデータが存在する可能性のある範囲です。この場合、それは A2:A21 (ID 番号列) です。
lookup_valueはあなたのsearch_key、またはあなたが求めている価値です。これは、この例では B17 (Mia) です。
lookup_arrayは、lookup_valueが配置されている範囲です。それが私たちにとっての B2:B21 (名前列) です。
[match_type] は、Mia に対応する最初の値を返す必要があるため、0です。より複雑なテーブルでは、その数値の後の値のみを返す場合は、別の数値に設定できます。
ワイルドカード文字を考慮する
ワイルドカードを使用して、VLOOKUP 関数で部分一致を実行できます。ワイルドカードは、検索パターン内の不明な文字または可変文字を表す特殊文字です。
たとえば、アスタリスク (*) は、文字なしを含む任意の文字シーケンスを表すことができます。
デモ テンプレートの「ワイルドカード * の例」タブでこの例を確認してください。ID 番号と従業員名のリストがあり、各 ID の最初の文字が従業員の部署を表しているとします。ID 番号が H で始まる人事担当者をリストで検索します。* ワイルドカードを使用して、この部分一致を実行できます。
search_keyを「H*」に置き換えるだけです。次のようになります。 =VLOOKUP("H*",A2:B10,2,FALSE)
ただし、ID 番号に数字のみが含まれ、文字が含まれていない場合は、別の数式を使用する必要があります。ID番号が 4で始まるとしましょう。あなたの式は次のようになります。 =VLOOKUP("4*",ArrayFormula(TO_TEXT(A1:B10)),2,FALSE)
トラブルシューティングにはFALSEを使用してください
エラー応答や誤った結果が得られますか?最初に完全に一致するis_sortedパラメータでFALSEを使用してみてください。それでも問題が解決しない場合は、機能しなかった他の理由を調べてください。
VLOOKUP を大文字と小文字を区別する
VLOOKUP は大文字と小文字を区別しないため、小文字と大文字の違いには注意を払いません。検索でそれが重要な場合は、別の数式を使用する必要があります。 ArrayFormula(INDEX(return_range, MATCH (TRUE,EXACT(lookup_range, search_key),0)))
自動化を活用して Google スプレッドシートをさらに活用しましょう
Google スプレッドシートの VLOOKUP は画期的な機能ですが、Google スプレッドシートでは手動で行う以外にも多くのことができます。Zapier の Google Sheets インテグレーションを使用すると、Google Sheets を何千もの他のアプリに接続できます。Google Sheets を自動化する方法を学習すれば、これらすべての機能を実行する方法を学ぶ時間を減らし、やりたいこと（おそらく文字通り他の何でも）に多くの時間を費やすことができます。始めるためのいくつかのツールを次に示します。
Zapierは、アプリを自動化されたワークフローに接続して、すべての人と企業が成長速度で前進できるようにするノーコードの自動化ツールです。その仕組みの詳細については、こちらをご覧ください。
VLOOKUP に関するよくある質問
スプレッドシート関数の世界は常に複雑で、学ぶべきことが常にあります ( KPI ダッシュボードの構築、 スプレッドシート CRM の作成、 スプレッドシート カンバン ボードの DIYなどに関する Zapier の恐ろしく詳細なガイドを参照してください)。有利なスタートを切るために、よくある質問に対する簡単な回答をいくつか紹介します。
VLOOKUPは複数の値を返すことができますか?
VLOOKUP 自体では、一度に 1 つの値しか返さない。ただし、VLOOKUP を INDEX や MATCH などの他の関数と組み合わせて、複数の値を返すことができます。
VLOOKUPとHLOOKUPの違いは何ですか?
VLOOKUP はテーブル配列を垂直方向に検索し (VLOOKUP の "V" は垂直を表します)、HLOOKUP はテーブル配列を水平方向に検索します (HLOOKUP の "H" は水平を表します)。
VLOOKUPは複数の列を調べることができますか?
VLOOKUP は複数の列を調べることができますが、常に左端の列から最初の一致を返します。予期しない結果が得られた場合は、列の並べ替えが必要になることがあります。
VLOOKUP が複数の一致を見つけるとどうなりますか?
技術的には、VLOOKUP は複数の一致を見つけることができません。テーブルで最初に完全に一致するもののみが返されるため、検索する項目ごとに一意の値を使用する必要があります。
VLOOKUP が値を見つけられない場合はどうなりますか?
VLOOKUP が検索している値を見つけられない場合、#N/A エラーが返されます。検索値にタイプミスや余分なスペースがないことを再確認してください。
関連資料：
この記事はもともと 2023 年 8 月にセシリア ギレンによって公開され、ディラン リーバーからの寄稿も受けています。最新のアップデートは 2024年9月。
