初心者および上級ユーザー向けの10のVLOOKUPの例

VLOOKUP関数はベンチマークです。

VLOOKUP関数の使用方法を知っている場合は、Excelについて何か知っています。

そうでない場合は、履歴書の強みのXNUMXつとしてExcelを挙げない方がよいでしょう。

私はグループインタビューを行い、候補者が自分の専門分野としてExcelについて言及した後、最初に尋ねられたのは、VLOOKUP機能です。

このExcel関数の重要性がわかったので、それを完全に把握して、「ExcelでXNUMXつかXNUMXつのことを知っている」と誇らしげに言うことができるようにするのは理にかなっています。

これは（私の基準では）巨大なVLOOKUPチュートリアルになります。

それについてすべて説明してから、VLOOKUPの便利で実用的な例を示します。

ExcelでVLOOKUP関数を使用するのはいつですか？

VLOOKUP関数は、列内で一致するデータポイントを検索する場合に最適に機能し、一致するデータポイントが見つかった場合は、行の右側の列を取得して、指定したセル数から値を取得します。

ここで簡単な例を見て、ExcelでVlookupを使用するタイミングを理解しましょう。

テストのスコアが出て掲示板に貼り付けられたとき、誰もが自分の名前とスコアを必死に検索していたことを覚えておいてください（少なくとも、私が学校にいたときによく起こったことです）。

仕組みは次のとおりです。

• 掲示板に移動して、名前または登録番号を探し始めます（リストを指で上下に動かします）。
• 名前を見つけたら、名前/登録番号の右側に目を移動してスコアを確認します。

これはまさにExcelVLOOKUP機能が行うことです（次のインタビューでこの例を自由に使用してください）。

VLOOKUP関数は、列で指定された値（上記の例ではあなたの名前）を探し、指定された一致を見つけると、同じ行で値（取得したトークン）を返します。

構文

= VLOOKUP（lookup_value、table_array、col_index_num、[range_lookup]）

入力パラメータ

• 参照値 -これは、テーブルの左端の列で検索しようとしているルックアップ値です。値、セル参照、またはテキスト文字列にすることができます。スコアシートの例では、これがあなたの名前になります。
• テーブル・アレイ -これは、値を探しているテーブルの配列です。これは、セル範囲または名前付き範囲への参照にすることができます。スコアテーブルの例では、これは各サブジェクトの全員のスコアを含むテーブル全体になります
• 列インデックス –これは、一致する値を取得する列のインデックス番号です。分数テーブルの例で、数学の分数が必要な場合（これは分数を含むテーブルの最初の列です）、列1を確認できます。物理的なスコアが必要な場合は、列1と2を確認できます。
• [range_lookup] -ここで、完全一致または近似一致のどちらが必要かを指定できます。省略した場合、デフォルトはTRUE-近似一致（以下の追加の注記を参照してください）。

追加の注意事項（退屈ですが、知っておくことが重要です）

• 一致は、正確（range_lookupでFALSEまたは0）または近似（TRUEまたは1）にすることができます。
• おおよそのルックアップでは、リストが昇順（上から下）にソートされていることを確認してください。そうしないと、結果が不正確になる可能性があります。
• range_lookupがTRUE（近似ルックアップ）で、データが昇順で並べ替えられている場合：
  • VLOOKUP関数が値を見つけられない場合、lookup_valueよりも小さい最大値を返します。
  • lookup_valueが最小値よりも小さい場合、＃N / Aエラーを返します。
  • lookup_valueがテキストの場合、ワイルドカードを使用できます（以下の例を参照）。

これで、VLOOKUP関数で何ができるかについての基本的な理解ができたと思います。玉ねぎをはがして、VLOOKUP関数の実際の例をいくつか見てみましょう。

10個のExcelVLOOKUPの例（基本および高度）

ここでは、ExcelVlookupを使用して日常業務で使用する方法を示す10の便利な例を示します。

例1-ブラッドの数学のスコアを見つける

以下のVLOOKUPの例では、左端の列に学生の名前があり、列BからEにさまざまな科目の成績が記載されたリストがあります。

それでは、作業を開始し、VLOOKUP関数を使用して最適な処理を実行してみましょう。上記のデータから、ブラッドが数学で何を獲得したかを知る必要があります。

上記のデータから、ブラッドが数学で何を獲得したかを知る必要があります。

ブラッドの数学スコアを返すVLOOKUP式は次のとおりです。

= VLOOKUP（ "Brad"、$ A $ 3：$ E $ 10,2,0）

上記の式にはXNUMXつのパラメーターがあります。

• 「ブラッド」： -これはルックアップ値です。
• $ A $ 3：$ E $ 10 -これは私たちが見ているセルの範囲です。Excelは左端の列でルックアップ値を探すことに注意してください。この例では、指定された配列の左端の列であるA3：A10でBradという名前を探します。
• 2 –関数がBradの名前を見つけると、配列のXNUMX番目の列に移動し、Bradと同じ行の値を返します。ここでの値2は、指定された配列のXNUMX番目の列からスコアを探していることを意味します。
• 0 –これは、VLOOKUP関数に完全一致のみを検索するように指示します。

上記の例のVLOOKUP式は次のように機能します。

まず、左端の列で値Bradを探します。セルA6の値を上から下に検索します。

値が見つかると、XNUMX番目の列の右側に移動し、その中の値を取得します。

同じ数式の構成を使用して、任意の主題で誰かのスコアを取得できます。

たとえば、ChemistryでMariaのスコアを見つけるには、次のVLOOKUP式を使用します。

= VLOOKUP（ "Maria"、$ A $ 3：$ E $ 10,4,0）

上記の例では、ルックアップ値（学生名）は二重引用符で囲まれています。ルックアップ値を含むセル参照を使用することもできます。

セル参照を使用する利点は、数式が動的になることです。

たとえば、生徒の名前のセルがあり、数学の成績を取得している場合、生徒の名前を変更すると、結果が自動的に更新されます（以下を参照）。

左端の列にないルックアップ値を入力すると、＃N / Aエラーが返されます。

例2-双方向ルックアップ

上記の例1では、列の値をハードコーディングしました。したがって、列のインデックス番号として2を使用するため、数式は常に数学のスコアを返します。

ただし、VLOOKUP値と列インデックス番号の両方を動的にしたい場合はどうでしょうか。たとえば、以下に示すように、学生名または科目名を変更すると、VLOOKUP式が正しいスコアを取得します。双方向のVLOOKUP式の例を次に示します。

双方向VLOOKUP関数の例を次に示します。

この双方向ルックアップ式を作成するには、列を動的にする必要もあります。したがって、ユーザーが主題を変更すると、数式は自動的に正しい列を選択します（数学の場合は2、物理学の場合は3など）。

これを行うには、MATCH関数を列パラメーターとして使用する必要があります。

これを行うVLOOKUP式は次のとおりです。

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

上記の式では、列番号としてMATCH（H3、$ A $ 2：$ E $ 2,0）を使用しています。MATCH関数は、トピック名をルックアップ値（H3）として受け取り、A2：E2での位置を返します。したがって、Mathを使用する場合、MathはB2（指定された配列範囲の2番目のセル）にあるため、XNUMXが返されます。

例3-ルックアップ値としてドロップダウンを使用する

上記の例では、データを手動で入力する必要があります。これは、特にルックアップ値が多い場合、時間がかかり、エラーが発生しやすくなります。

この場合の良いアイデアは、ルックアップ値のドロップダウンリストを作成し（この場合は学生の名前と科目である可能性があります）、リストから選択することです。

選択に応じて、数式は結果を自動的に更新します。

以下に示すように：

バックエンドに数百人の学生の巨大なデータセットを含めることができるため、これは優れたダッシュボードコンポーネントですが、エンドユーザー（教師など）はから簡単にドロップダウンできます。

これを行うにはどうすればよいですか：

この例で使用されているVLOOKUP式は、例2で使用されているものと同じです。

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

ルックアップ値はドロップダウンリストに変換されています。

ドロップダウンリストを作成する手順は次のとおりです。

• ドロップダウンリストが必要なセルを選択します。この例では、G4で学生名が必要です。
• [データ]-> [データツール]-> [データ検証]に移動します。
• [データ検証]ダイアログの[設定]タブで、[許可]ドロップダウンリストから[リスト]を選択します。
• [ソース]で、[$ A $ 3：$ A $ 10]を選択します
• [OK]をクリックします。

セルG4にドロップダウンリストが表示されます。同様に、H3でテーマ用に作成できます。

例4-スリーウェイルックアップ

スリーウェイルックアップとは何ですか？

例2では、​​さまざまな科目の学生のスコアを含むルックアップテーブルを使用しました。これは、スコアを取得するためにXNUMXつの変数（学生名と科目名）を使用するため、双方向ルックアップの例です。

さて、XNUMX年で、学生がXNUMXつの異なるレベルの試験、ユニット試験、中間試験、および最終試験（これは私の学生時代の試験です）を受験するとします。

XNUMX方向ルックアップでは、指定された試験レベルから指定された科目の学生のスコアを取得できます。

以下に示すように：

上記の例では、VLOOKUP関数は、XNUMXつの異なるテーブル（単体テスト、中間、および決勝）で、指定された科目の指定された学生の成績を検索して返すことができます。

セルH4で使用される数式は次のとおりです。

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

この式は、CHOOSE関数を使用して、正しいテーブルが参照されていることを確認します。式のCHOOSE部分を分析してみましょう。

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

数式の最初の引数はIF（H2 =”ユニットテスト”、1、IF（H2 =”中期”、2,3））で、セルH2を調べ、参照されたテストレベルを確認します。単体テストの場合は、単体テストのスコアを含む$ A $ 3：$ E $ 7が返されます。中間試験の場合は$ A $ 11：$ E $ 15を返し、それ以外の場合は$ A $ 19：$ E $ 23を返します。

これを行うと、VLOOKUPテーブル配列が動的になり、XNUMX方向のルックアップになります。

例5-リストから最後の値を取得する

VLOOKUP式を作成して、リストの最後の値を取得できます。

Excelで使用できる最大の正の数は 9.99999999999999 + 307 。 これは、VLOOKUP番号の最大ルックアップ番号が同じであることも意味します。

このような大きな数値を含む計算は必要ないと思います。これはまさに、リストの最後の番号を取得するために使用できるものです。

次のようなデータセットがあるとします。（A1：A14）、およびリストの最後の番号を取得する必要があります。

使用できる式は次のとおりです。

= VLOOKUP（9.99999999999999E + 307、$ A $ 1：$ A $ 14、TRUE）

上記の式は、近似一致のVLOOKUPを使用していることに注意してください  （数式の最後にあるTRUEに注意してください。FALSEや0ではありません）。また、このVLOOKUP式は、リストを並べ替えなくても機能することに注意してください。

近似VLOOKUP関数のしくみは次のとおりです。左端の列を上から下にスキャンします。

• この値は、完全に一致するものが見つかった場合に返されます。
• ルックアップ値よりも高い値が見つかった場合は、その上のセルに値を返します。
• ルックアップ値がリスト内のすべての値より大きい場合、最後の値が返されます。

上記の例では、XNUMX番目のケースが機能しています。

のため9.99999999999999 + 307はExcelで使用できる最大の数値であるため、ルックアップ値として使用される場合は、リストの最後の数値を返します。

同様に、これを使用してリストの最後のテキスト項目を返すこともできます。これを行うことができる式は次のとおりです。

= VLOOKUP（ "zzz"、$ A $ 1：$ A $ 8,1 TRUE )

同じロジックに従います。Excelはすべての名前を調べ、zzzはzzzの前の文字で始まる名前/テキストよりも大きいと見なされるため、リストの最後の項目を返します。

例6-ワイルドカードとVLOOKUPを使用した部分的なルックアップ

Excelワイルドカードは、多くの状況で役立ちます。

あなたのレシピに超能力を与えるのはこの魔法のポーションです。

リスト内の値を検索する必要があり、完全に一致するものがない場合は、部分的な検索が必要です。

たとえば、次のようなデータセットがあり、リストで会社ABCを検索したいが、リストにABCではなくABCLtdが含まれているとします。

列Aに完全に一致するものがないため、ルックアップ値としてABCを使用することはできません。近似一致も誤った結果につながる可能性があるため、リストを昇順で並べ替える必要があります。

ただし、VLOOKUP関数でワイルドカードを使用して一致を取得できます。

セルD2に次の数式を入力し、他のセルにドラッグします。

= VLOOKUP（ "*"＆C2＆ "*"、$ A $ 2：$ A $ 8,1、FALSE）

この式はどのように機能しますか？

上記の式では、ルックアップ値をそのまま使用する代わりに、ワイルドカードのアスタリスク（*）で囲まれています- "*"＆C2＆ "*"

アスタリスクはExcelのワイルドカード文字であり、任意の数の文字を表すことができます。

ルックアップ値の前後にアスタリスクを使用して、C2の単語を含むテキストを検索する必要があることをExcelに通知します。C2のテキストの前後に任意の数の文字を含めることができます。

たとえば、セルC2にはABCが含まれているため、VLOOKUP関数はA2：A8の名前を調べて、ABCを検索します。ABC LtdのABCが含まれているため、セルA2で一致が見つかります。 ABCの左または右に文字があるかどうかは関係ありません。テキスト文字列にABCが含まれるまで、一致と見なされます。

注：VLOOKUP関数は常に最初に一致する値を返し、それ以上のルックアップを停止します。したがって、リストにABCが含まれている場合 （株）ABC Corporationの場合、最初のものを返し、残りは無視します。

例7-ルックアップ値が一致しているにもかかわらず、VLOOKUPがエラーを返す

一致するルックアップ値があり、VLOOKUP関数がエラーを返すことがわかると、気が狂います。

たとえば、以下の場合、一致（Matt）がありますが、VLOOKUP関数はエラーを返します。

一致するものがあることはわかりますが、肉眼では見えないのは、先頭または末尾にスペースがある可能性があることです。ルックアップ値の前、後、または間にこれらの余分なスペースがある場合、それは完全には一致しません。

これは通常、データベースからデータをインポートするとき、または他の誰かからデータを取得するときに発生します。これらの先頭/末尾のスペースは忍び寄る傾向があります。

ここでの解決策はTRIM関数です。単語間の先頭または末尾のスペース、または余分なスペースを削除します。

これが正しい結果を与える式です。

= VLOOKUP（ "Matt"、TRIM（$ A $ 2：$ A $ 9）、1,0）

これは配列数式であるため、Enterの代わりにControl + Shift + Enterを使用してください。

別のアプローチは、最初にTRIM関数を使用してルックアップ配列を処理し、余分なスペースがすべてなくなっていることを確認してから、通常どおりVLOOKUP関数を使用することです。

例8-大文字と小文字を区別するルックアップを実行する

デフォルトでは、VLOOKUP関数のルックアップ値では大文字と小文字が区別されません。たとえば、ルックアップ値がMATT、matt、またはMattの場合、VLOOKUP関数でもすべて同じです。大文字と小文字に関係なく、最初に一致する値を返します。

ただし、大文字と小文字を区別するルックアップが必要な場合は、EXACT関数とVLOOKUP関数を使用する必要があります。

これは例です：

ご覧のとおり、2つのセルの名前は同じですが（A4、A5、およびAXNUMX）、大文字と小文字が異なります。右側には、XNUMXつの名前（Matt、MATT、およびmatt）とそれらの数学のスコアがあります。

VLOOKUP関数は現在、大文字と小文字を区別するルックアップ値を処理するように装備されていません。上記の例では、常に38を返します。これは、A2でのマットのスコアです。

大文字と小文字を区別するには、ヘルパー列を使用する必要があります（以下を参照）。

ヘルプ列の値を取得するには、= ROW（）関数を使用します。セル内の行番号のみを取得します。

ヘルパー列ができたら、大文字と小文字を区別するルックアップ結果を与える式を次に示します。

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

それを分解して、それが何をするのかを理解しましょう：

• EXACT（E2、$ A $ 2：$ A $ 9）–この部分では、E2のルックアップ値をA2：A9のすべての値と比較します。TRUE / FALSEの配列を返します。完全に一致する場合は、TRUEが返されます。この場合、次の配列が返されます：{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}。
• EXACT（E2、$ A $ 2：$ A $ 9）*（ROW（$ A $ 2：$ A $ 9）-この部分は、TRUE / FALSE配列に行番号を乗算します。TRUEがある限り、行番号が返されます。 、それ以外の場合は0になります。この場合、{2; 0; 0; 0; 0; 0; 0; 0}が返されます。
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。この場合、2（正確な行番号）が返されます。
• ここで、この数値をルックアップ値として使用し、ルックアップ配列をB2：C9として使用します。

注：これは配列数式であるため、入力するだけでなく、Control + Shift + Enterを使用します。

例9-複数の条件でVLOOKUPを使用する

Excel VLOOKUP関数は、基本的な形式で、ルックアップ値を検索し、指定された行から対応する値を返します。

ただし、通常、Excelでは複数の基準を持つVLOOKUPが必要です。

学生の名前、テストの種類、数学のスコア（以下に表示）を含むデータがあるとします。

VLOOKUP関数を使用して、それぞれのテストレベルで各生徒の数学のスコアを取得するのは難しい場合があります。

たとえば、ルックアップ値としてMattを使用してVLOOKUPを使用しようとすると、リスト内で最初に出現したMattのスコアである91が常に返されます。各試験タイプ（ユニット、中間、および最終）のマットスコアを取得するには、一意のルックアップ値を作成する必要があります。

これは、ヘルパー列を使用して実行できます。最初のステップは、フラクションの左側にヘルパー列を挿入することです。

ここで、名前のインスタンスごとに一意の修飾子を作成するには、C2で次の式を使用します。= A2＆” |”＆B2

この数式をヘルパー列のすべてのセルにコピーします。これにより、名前のインスタンスごとに一意のルックアップ値が作成されます（以下を参照）。

現在、重複する名前がありますが、名前を試験レベルと組み合わせると、重複することはありません。

ヘルパー列の値をルックアップ値として使用できるようになったため、これは簡単です。

これは、G3：I8の結果を与える式です。

= VLOOKUP（$ F3＆ "|"＆G $ 2、$ C $ 2：$ D $ 19,2,0）

ここでは、学生名と試験レベルを組み合わせてルックアップ値を取得します。このルックアップ値を使用して、一致するレコードのヘルパー列をチェックインします。

注：上記の例では、|を使用しました 補助列にテキストを追加するときに区切り文字として使用されます。非常にまれな（ただし可能性のある）ケースでは、XNUMXつの異なる基準がある場合がありますが、それらを組み合わせると同じ結果が得られます。これは例です：

A2とA3は異なり、B2とB3は異なりますが、組み合わせは最終的に同じであることに注意してください。ただし、区切り文字を使用すると、組み合わせも異なります（D2とD3）。

これは、ヘルパー列を使用せずに複数の条件でVLOOKUPを使用する方法に関するチュートリアルです。

例10-VLOOKUP関数を使用する際のエラーの処理

指定されたルックアップ値が見つからない場合、ExcelVLOOKUP関数はエラーを返します。VLOOKUPが値を見つけられない場合は、醜いエラー値がデータの美観を妨げることを望まないでしょう。

「利用不可」や「見つかりません」など、あらゆる意味の全文を含むエラー値を簡単に削除できます。

たとえば、次の例では、リストでBradのスコアを検索しようとすると、Bradの名前がリストにないため、エラーが返されます。

このエラーを削除して意味のあるものに置き換えるには、VLOOKUP関数をIFERROR関数でラップします。

式は次のとおりです。

= IFERROR（VLOOKUP（D2、$ A $ 2：$ B $ 7,2,0）、 "見つかりません"）

IFERROR関数は、最初の引数（この場合はVLOOKUP関数）によって返される値がエラーであるかどうかをチェックします。エラーでない場合は、VLOOKUP関数を使用して値を返します。それ以外の場合は、「見つかりません」を返します。

IFERROR関数は、Excel2007から使用できます。以前のバージョンを使用している場合は、次の機能を使用してください。

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

このVLOOKUPチュートリアルは以上です。

ExcelでVlookup関数を使用する主な例を紹介しようとしています。このリストにさらに例を追加したい場合は、コメントセクションでお知らせください。

関連するExcel関数：

• ExcelのHLOOKUP関数。
• ExcelXLOOKUP関数
• Excelインデックス機能。
• Excelの間接関数。
• Excelの一致機能。
• Excelオフセット関数。