VLOOKUPとは？
VLOOKUPは、Microsoft Excelで生まれた関数で、ある列で特定の値を検索し、その情報を使って同じ行にある別の値を引き出すことができます。
例えば、「名前」「番号」「住所」という3つの列があり、これらの列は情報で満たされている可能性があります。VLOOKUPを使えば、「名前」列で特定の名前を検索し、その名前と同じ行にある番号や住所を表示することができる。ただし、VLOOKUPは大文字と小文字を区別しないので注意が必要だ。
前述の例のように、小さなデータのプールで使用する場合は特に便利とは思えないかもしれませんが、シート上に大量の情報があり、特定の値を他の領域で使用したい場合には、非常に便利な機能です。
あるシートで情報のマスターリストを作成し、後続のシートでVLOOKUPを使ってマスターリストからデータを引っ張ってくるだけでいいのです。そうすれば、1つのシートを更新するだけで、他のシートにも自動的に値が反映される。
簡単に説明すると、VLOOKUPの文字列は次のようになります。
=VLOOKUP(
調べたい値。
探したいセルの範囲。
表示したい値の列番号。
完全一致か近似一致か
)
VLOOKUPをExcelやGoogle Sheetsで使うには
VLOOKUPの文字列は一見難しそうに見えますが、実は見た目よりもずっとシンプルです。以下の詳細な手順でご紹介します。
- 引き出すための情報の表が必要です。ここでは、先に提供した例と同じ考え方で、3つの列の下に情報を追加します。名前」「住所」「電話番号」の3つです。
2. あるフィールドにある名前に対応する電話番号を引き出したい。この場合、“Iris Watson “の電話番号を引き出したい。
3. 空のセルをダブルクリックして、=VLOOKUP(.)‘と入力し、文字列を開始します。最初に必要な値は “lookup_value. “で、これが電話番号の検索に使用する情報であることがわかるでしょう。
4. VLOOKUPの前のセルに “Iris Watson “という名前を追加したので、そのセルをlookup_valueとして使用します。文字列は現在このような感じになっているはずです。=VLOOKUP(e12,)
5. 次に、table_arrayです。これはデータを引き出す情報の表全体です。データがあるテーブル全体をハイライトするだけです。この段階では、以下のような文字列になっているはずです。=VLOOKUP(e12,A1:C5,)
6. 文字列の3番目の値は、“colxx_index_number”、つまり列のインデックス番号です。これは、情報を抽出しようとするカラムの番号である。今回選択したテーブル配列では、「Name」が1、「Address」が2、「Phone Number」が3とみなされる。 電話番号をVLOOKUPしようとしているので、文字列は次のようになる。=VLOOKUP(e12,A1:C5,3,)
7. 最後に、文字列の最後の部分は、検索する値に対して完全一致か近似一致かを指定します。近似値ならTRUE、完全一致ならFALSEを入力する必要があります。私たちの場合は、後者にしました。最終的な文字列は =VLOOKUP(E12,A1:C5,3,FALSE) です。
8. 見ての通り、VLOOKUPは “Iris Watson “に対応する電話番号を引き出すことに成功しました。
注：このガイドではMicrosoft Excelを使用してVLOOKUPを実行していますが、Google Sheetsでも同じ方法を使用することができます。
複数の基準でデータをフィルタリングする方法
VLOOKUPは単一の値だけを引き出すために設計されており、複数の情報源を調べるための他の関数があります - VLOOKUPを使用して複数の条件を検索できないわけではありません。ヘルパー列を使えば、複数のセルの情報を格納する一意の識別子を作成し、その一意の識別子を代わりに検索するようにVLOOKUPを調整することができます。
これは、同じ値を持つ複数のセルがある場合に便利です。たとえば、このテーブルに “Iris Watson “という名前のセルが複数あるとします。通常であれば、VLOOKUP は最初に見つかった “Iris Watson” をリストアップするだけですが、別のセルを探している場合もあるでしょう。 ヘルパー・カラムを使用すると、VLOOKUPがシート内の異なる「アイリス・ワトソン」を区別するのに役立つ一意の識別子を得ることができます。 この例では、VLOOKUPを使って、電話番号の代わりに住所を表示しています。また、住所と電話番号が異なる2人目の「アイリス・ワトソン」をシートに追加しています。- まず、「名前」と「電話番号」のセルを組み合わせて、一意な識別子を作るヘルパー列を作りました。CONCATENATE関数を使いました。これは、異なるセルの文字列を単純に結合するものです。文字列は次のようなものになるはずです。
=CONCATENATE(B2," | ",D2)分かりやすいように、パイプ記号"|"と名前と電話番号の間にいくつかのスペースを入れました。この関数についてもっと詳しく知りたい方は、CONCATENATEの使い方の記事をご覧ください。
2. 連結に成功したら、ハイライトされたセルの右下をクリックし、ヘルパー列の他のセルまでドラッグダウンしてください。そうすると、それらのセルにも同じように連結が適用されます。
3. VLOOKUPに必要なフィールドは2つです。名前」「番号」のルックアップフィールドと、VLOOKUPの結果を表示する「住所」フィールドを追加しました。
4. VLOOKUPは、「名前」と「番号」の検索フィールドの情報を、ヘルパーカラムで使っているのと同じ形式に合うように組み合わせたいのです。そうすれば、VLOOKUPはヘルパー・カラムのユニークな識別子を見抜き、対応する住所を引き出すことができる。
最初はこのような文字列でした。VLOOKUP(F9&”) | “&F10,`
F9とF10はそれぞれ「名前」と「住所」の検索フィールドで、&記号は両方のフィールドを結合するという意味で、連結のように機能します。文字列の” | “部分は、ヘルパーカラムの連結で使用したものと同じ区切りです。
5. あとは、VLOOKUP式の残りの部分に従うだけです。テーブル配列を選択し、列のインデックス番号を入力し、EXACT matchに設定する必要があります。これが私たちの終了文字列でした。=vlookup(f9&" | "&f10,a1:d6,3,false) となります。
6. 検索フィールドに名前や住所を入力するだけでは不十分であることがわかるでしょう。検索を成功させるためには、両方が埋まっている必要があります。下図では、2種類の “Iris Watsons “があるため、2種類のVLOOKUPの結果を見ることができます。2つ目の検索フィールドにどちらの電話番号を入力するかによって、異なる結果が得られます。
VLOOKUP は INDEX-MATCH よりも優れているか？
この質問は、Microsoft Excelの初期から激しく議論されてきました。この質問に答える前に、INDEX-MATCHが何であるかを理解することが重要です。INDEXとMATCHは2つの別々の関数で、VLOOKUPよりも多目的な検索システムを作るためによく組み合わされます。
MATCH関数は、セルの範囲内で特定の値の位置番号を求めたい場合に使用します。一方、INDEXは、表の配列やセル範囲から値を表示するために、2つの可能性のある形式を使用します。両者を組み合わせることで、MATCH関数で提供される情報の位置番号を決定し、INDEXでその位置番号を使って値を返すことができるのです。
どちらが優れているかは、その関数を使う人によります。VLOOKUPは、ExcelやGoogleシートの初心者や中級者にとっては、設定や活用が簡単な分、より親しみやすいと言えます。しかし、INDEX-MATCHの方がはるかに柔軟性があり、さまざまな場面で活用できます。
結局のところ、VLOOKUPの使い方はINDEX-MATCHよりも多くの人が知っているので、上級者ではない多くの人がアクセスするシートであれば、前者の方が適していると言えるかもしれません。しかし、もしそのシートが上級者向けであれば、INDEX-MATCHを使うべきでしょう。
VLOOKUPを使用する際の一般的な注意事項
VLOOKUP関数に慣れていないうちは、多少の失敗をすることは恥ずかしいことではありません。Excelのベテランの多くは、人生のある時点でそのような経験をしたことがあります。ここでは、VLOOKUPを試すときに気をつけるべきことをいくつか紹介します。
1. **1. LOOKUP_VALUEがテーブルの最初の列であることを確認します。
VLOOKUPは、Lookup_valueがテーブル配列の最初のカラムにあることを前提に機能します。その値が同じ行の次のセルにある場合のみ、情報を表示することができます。lookup_valueを先頭以外の場所に置くと、関数は失敗します。
2. 2. 完全に一致する場合は、FALSEを使用することを忘れないでください。
VLOOKUP文字列の最後の部分で、完全一致の場合はFALSE、部分一致の場合はTRUEを指定することができます。多くのユーザーは、間違ってTRUEを使用して、不正確な結果につながる可能性があり、また、値を設定するのを全く忘れてしまいます。
3. 3. 列インデックス番号を再確認してください。
VLOOKUPが何を表示するかは、文字列を入力するときに「col_index」の値を正しく設定するかどうかに大きく依存します。col_index_enumとは、テーブル配列の各カラムに設定されている数値のことです。1列目の値を1、2列目の値を2、といった具合です。col_index_numの値を間違えて入力すると、全く違う結果が表示されます。
4. 4. 数式を他のセルにコピーするときは、必ずF4キーを使用してください。
VLOOKUPの便利なところは、数式を下にドラッグして、いろいろなセルにコピーできることです。問題は、関数列で指定した値も下に移動してしまい、数式全体が台無しになってしまうことです。これを防ぐには、数式内の値にカーソルを置いて、「F4」キーを押します。これは、数式をコピーしてもずれない絶対的な値に変換します。
VLOOKUPのよくあるエラーとその対処法
VLOOKUPを使用する際に最もよく遭遇するエラーは「#NA」エラーですが、このエラーが表示される理由はさまざまなので注意が必要です。
1. Lookup_valueがテーブル配列の最初のカラムにない。
VLOOKUPの最大の制限事項の1つは、テーブル配列の最初の列にある値しか検索できないことです。もしLOOKUP_VALUEがそこにない場合、#NAエラーになります。これを解決するには、式を微調整して別の列を参照するか、列を移動してルックアップテーブルの値が正しい場所に来るようにします。
2. 2. VLOOKUP が完全一致を見つけられませんでした。
VLOOKUP式の最後の値は、range_lookup引数で、近似一致の場合はTRUEに、完全一致の場合はFALSEに設定する。この引数をFALSEに設定した場合、VLOOKUPで完全一致を見つけることができないと、#NAエラーが表示されます。
lookup_valueが絶対に一致すると確信している場合、テーブル配列の情報が適切にフォーマットされているか、不要なスペースがないかを再確認してください。印字されない文字も、VLOOKUPが正しく項目を見つけるのを妨げる可能性があります。3. 3. 浮動小数点数が大きすぎる
本来、浮動小数点数とは、小数点がある数値のことです。VLOOKUPでは、小数点以下の数字が多すぎる場合、#NAエラーになります。これを解決するのはとても簡単で、小数点以下5桁まで数字を切り上げればうまくいくはずです。これは ROUND 関数を使用して行うことができます。
よくある質問
1. range_lookupの引数を空白にするとどうなりますか？
VLOOKUP文字列の4番目の引数は、“TRUE “または “FALSE “のどちらかを設定する必要がありますが、オプションとして考えられています。TRUE “に設定すると、VLOOKUPは近似値を検索し、“FALSE “に設定すると、値は正確でなければなりません。この引数を空白にすると、VLOOKUPはデフォルトで “TRUE “になり、期待する結果を台無しにする可能性があるということです。
2. VLOOKUPを使用する代替案は何ですか？
VLOOKUP関数の代わりになるのは、INDEX-MATCHコンボでしょう。ただし、この関数の組み合わせは、2つの異なる関数を使いこなす必要があるため、習得が少し難しくなります。
3. 3. 列ではなく行で値を検索することはできますか。
ExcelやGoogle Sheetsでは、「HLOOKUP」（水平方向の検索）を使って検索できます。この機能は、特定の行から値を検索し、別の行の同じ列の値を表示します。
もし、検索対象を1つの行や列に限定したい場合は、LOOKUP関数を使用することができます。
