これらの例では、複数の条件をVlookupする方法、特定のインスタンスまたはすべての一致を返す方法、動的なVlookupを実行する方法を説明します。複数のシートなど。
これは、ExcelVLOOKUPのパワーを活用するのに役立つシリーズの第2部です。これらの例は、この関数がどのように機能するかを知っていることを意味します。そうでない場合は、ExcelでのVLOOKUPの基本的な使用法から始めるのが妥当です。
先に進む前に、構文について簡単に説明します。
全員が同じページにいるので、高度なVLOOKUP式の例を詳しく見てみましょう。
- Excelで複数の条件をVlookupする方法
- Vlookupとn番目の一致を返す
- Vlookupの複数の値
- 行と列の値に基づくVlookup
- ネストされたVlookup式
- 複数のシートからデータをプルするためのVLOOKUPとINDIRECT
複数の条件をVlookupする方法
Excel VLOOKUP関数は、データベース全体を検索する場合に非常に役立ちます。ただし、その構文では1つのルックアップ値しか使用できません。ただし、複数の条件でルックアップする場合はどうすればよいでしょうか。選択できるソリューションはいくつかあります。
式1.twに基づくVLOOKUP o値
注文のリストがあり、顧客名と製品の2つの基準に基づいて数量を検索するとします。複雑な要因は、次の表に示すように、各顧客が複数の製品を注文したことです。
通常のVLOOKUP式はこれでは機能しません指定した単一のルックアップ値に基づいて最初に見つかった一致を返すためです。
これを克服するには、ヘルパー列を追加し、そこに2つのルックアップ列（顧客と製品）の値を連結します。ヘルパー列は、Excel VLOOKUPが常にルックアップ値を検索する場所であるため、テーブル配列の左端の列であることが重要です。
したがって、テーブルの左側に列を追加してコピーします。その列全体で以下の式。これにより、ヘルパー列に列BとCの値が入力されます（読みやすくするためにスペース文字は間に連結されています）：
=B2&" "&C2
次に、標準のVLOOKUP式を使用して、両方の基準をスペースで区切ってlookup_value引数に配置します。
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
または、個別のセル（この場合はG1とG2）に基準を入力し、それらのセルを連結します。
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
テーブル配列の4番目である列Dから値を返したい場合は、col_index_numに4を使用します。 range_lookup引数はFALSEに設定され、Vlookupが完全に一致します。以下のスクリーンショットは結果を示しています。
ルックアップテーブルが別のシートにある場合は、シートの名前をVLOOKUP式に含めます。例：
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
または、ルックアップテーブルの名前付き範囲（たとえば、Orders）を作成して、数式を簡単にします-読むべきもの：
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
詳細については、Excelの別のシートからルックアップする方法を参照してください。
式2.複数の条件を持つExcelVLOOKUP
理論的には、上記のアプローチを使用して、3つ以上の基準をVlookupに使用できます。Hただし、いくつかの注意点があります。第一に、ルックアップ値は255文字に制限されており、第二に、ワークシートのデザインではヘルパー列を追加できない場合があります。
幸いなことに、MicrosoftExcelは同じことを行うための複数の方法を提供します。複数の条件をVlookupするには、INDEXMATCHの組み合わせまたはOffice365で最近導入されたXLOOKUP関数のいずれかを使用できます。
たとえば、3つの異なる値（日付、顧客名、製品）に基づいて検索するには、次のいずれかの式を使用します。
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
場所：
数式の詳細な説明については、以下を参照してください。
- 複数の基準を持つXLOOKUP
- 複数の基準を持つINDEXMATCH数式
VLOOKUPを使用して2番目、3番目、またはn番目の一致を取得する方法
すでにご存知のとおり、Excel VLOOKUPは一致する値を1つだけフェッチできます。より正確には、最初に見つかった一致を返します。しかし、ルックアップ配列に複数の一致があり、2番目または3番目のインスタンスを取得したい場合はどうでしょうか。タスクは非常に複雑に聞こえますが、解決策は存在します！
式1.VlookupN番目のインスタンス
1つの列に顧客名があり、別の列に購入した製品があり、特定の顧客が購入した2番目または3番目の製品を探しています。
最も簡単な方法は、最初の例で行ったように、テーブルの左側にヘルパー列を追加することです。ただし、今回は、「John Doe1」、「John Doe2」などの顧客名とオカレンス番号を入力します。
オカレンスを取得するには、混合範囲参照を指定したCOUNTIF関数を使用します（最初の参照は絶対的で、2番目は$ B $ 2：B2のように相対的です。相対参照は数式がコピーされるセルの位置に基づいて変化するため、3行目では$ B $ 2：B3になり、4行目では$ B $ 2：B4になります。
顧客名（B2）と連結すると、式は次の形式になります。
=B2&COUNTIF($B$2:B2, B2)
上記の式はA2に適用され、次に、必要な数のセルにコピーします。
その後、ターゲット名とオカレンス番号を別々のセル（F1とF2）に入力し、次の数式を使用して特定のオカレンスをVlookupします。
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
式2.Vlookupの2回目の出現
ルックアップ値の2番目のインスタンスを探している場合は、ヘルパー列なしで実行できます。代わりに、MATCHと一緒にINDIRECT関数を使用して、テーブル配列を動的に作成します。
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
場所：
- E1はルックアップ値です
- A2：A11はルックアップ範囲です
- B11はルックアップテーブルの最後（右下）のセルです
上記の数式は、ルックアップテーブルのデータセルが行2で始まる特定の場合のために記述されていることに注意してください。テーブルがどこかにある場合シートの中央で、次のユニバーサル数式を使用します。ここで、A1は、列ヘッダーを含むルックアップテーブルの左上のセルです。
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
この式のしくみ
動的なvlookup範囲を作成する式の重要な部分は次のとおりです。
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
完全一致（最後の引数の0）用に構成されたMATCH関数は、ターゲット名（E1）を名前のリスト（A2：A11）と比較し、最初に見つかった一致の位置（3）を返します。 oであなたの場合。この番号はvlookup範囲の開始行座標として使用されるため、2を追加します（最初のインスタンスを除外する場合は+1、列ヘッダーのある行1を除外する場合は+1）。または、1 + ROW（A1）を使用して、ヘッダー行（この場合はA1）の位置に基づいて必要な調整を自動的に計算できます。
結果として、次のテキスト文字列が得られます。どのINDIRECTが範囲参照に変換するか：
INDIRECT("A"&5&":B11") -> A5:B11
この範囲はVLOOKUPのtable_array引数に移動し、行の検索を開始します。 5、ルックアップ値の最初のインスタンスを省略します：
VLOOKUP(E1, A5:B11, 2, FALSE)
Excelで複数の値をVlookupして返す方法
Excel VLOOKUP関数は、一致するものを1つだけ返すように設計されています。複数のインスタンスをVlookupする方法はありますか？はい、簡単ではありませんが、あります。これには、INDEX、SMALL、ROWなどのいくつかの関数を組み合わせて使用する必要があります。
たとえば、以下では、ルックアップ範囲B2：B16内のルックアップ値F2のすべてのオカレンスを検索し、列Cからの複数の一致：
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
ワークシートに数式を入力する方法は2つあります。
- 最初のセルに数式を入力し、Ctrl + Shift + Enterを押して、さらにいくつかのセルにドラッグします。
- 1つの列で隣接する複数のセルを選択します（F1：F11 in下のスクリーンショット）、数式を入力し、Ctrl + Shift + Enterを押して完了します。
どちらの場合も、数式を入力するセルの数は、以上である必要があります。一致する可能性のある最大数。
数式ロジックの詳細な説明とその他の例については、Excelで複数の値をVLOOKUPする方法を参照してください。 。
行と列をVlookupする方法（双方向ルックアップ）
双方向ルックアップ（別名マトリックスルックアップまたは2次元ルックアップ）は、特定の行と列の交点で値をルックアップするためのファンシーワードです。Excelで2次元ルックアップを実行するにはいくつかの方法がありますが、このチュートリアルの焦点はVLOOKUP関数にあるため、自然に使用します。
この例では、以下の表の月間売上高を示し、VLOOKUP式を計算して、特定の月の特定のアイテムの売上高を取得します。
A2：A9のアイテム名、B1：F1の月名、ターゲットアイテムI1とI2のターゲット月では、式は次のようになります。
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
この数式のしくみ
数式の中核は、I1のルックアップ値と完全に一致するものを検索する標準のVLOOKUP関数です。特定の月の売上が正確に列になっている場合、列番号をcol_index_num引数に直接指定することはできません。その列を見つけるには、次のMATCH関数を使用します。
MATCH(I2, A1:F1, 0)
英語に翻訳すると、式は次のようになります。 A1：F1のI2値であり、配列内の相対位置を返します。 3番目の引数に0を指定すると、ルックアップ値と完全に等しい値を見つけるようにMATCHに指示します（VLOOKUPのrange_lookup引数にFALSEを使用するようなものです）。
Marは4番目の列にあるためルックアップ配列では、MATCH関数は4を返し、VLOOKUPのcol_index_num引数に直接移動します。
VLOOKUP(I1, A2:F9, 4, FALSE)
注意してください月の名前は列Bで始まりますが、ルックアップ配列にはA1：I1を使用します。これは、MATCHによって返される番号がVLOOKUPのtable_array内の列の位置に対応するために行われます。
Excelでマトリックスルックアップを実行するその他の方法については、INDEX MATCHMATCHおよびその他の2次元ルックアップの式を参照してください。
Excelで複数のVlookupを実行する方法（ネストされたVlookup）
メインテーブルとルックアップテーブルに共通の単一の列がない場合があります。これにより、2つのテーブル間でVlookupを実行できなくなります。ただし、探している情報が含まれていないが、メインテーブルとの共通列とルックアップテーブルとの共通列がある別のテーブルが存在します。
以下の画像は、状況を示しています。
目標は、アイテムIDに基づいて価格をメインテーブルにコピーすることです。問題は、価格を含むテーブルにアイテムIDがないことです。つまり、1つの式で2つのVlookupを実行する必要があります。
便宜上、最初にいくつかの名前付き範囲を作成しましょう。 ：
- ルックアップテーブル1の名前はProducts（D3：E3）です。
- ルックアップテーブル2の名前はPrices（G3：H3）です。
テーブルは同じワークシートでも異なるワークシートでもかまいません。
次に、いわゆるダブルVlookup、別名ネストされたVlookupを実行します。
まず、VLOOKUPを作成します。アイテムID（A3）に基づいてルックアップテーブル1（Productsという名前）で製品名を見つける式：
=VLOOKUP(A3, Products, 2, FALSE)
次に、上記の式を別のVLOOKUP関数のlookup_value引数に入れて、ネストされたVLOOKUPによって返された製品名に基づいてルックアップテーブル2（Pricesという名前）から価格を取得します。
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
以下のスクリーンショットは、ネストされたVlookup式の動作を示しています。
複数のVlookupを実行する方法シートを動的に
同じ形式のデータを複数のワークシートに分割する場合があります。また、特定のセルのキー値に応じて特定のシートからデータを取得することを目的としています。
これは例から理解しやすい場合があります。たとえば、同じ形式の地域の売上レポートがいくつかあり、特定の地域の特定の製品の売上高を取得しようとしているとします。
前の例と同様に、いくつかの名前を定義することから始めます。
- CAシートの範囲A2：B5の名前はCA_Salesです。
- 範囲A2 ：FLシートのB5の名前はFL_Salesです。
- 範囲A2：KSシートのB5の名前はKS_Salesです。
ご覧のとおり、すべての名前付き範囲には共通の名前が付いています。パーツ（販売）と固有のパーツ（CA、FL、KS）。作成するフォーミュラに不可欠であるのと同じ方法で範囲に名前を付けてください。
フォーミュラ1。異なるシートからデータを動的にプルする間接VLOOKUP
タスクが複数のシートからデータを取得することである場合、VLOOKUP INDIRECT式が最適なソリューションであり、コンパクトで理解しやすいです。
この例では、要約テーブルを次のように編成します。
- A2とA3に関心のある製品を入力します。これらはルックアップ値です。
- 名前付き範囲の一意の部分をB1、C1、およびD1に入力します。
次に、一意の部分（B1）を含むセルを共通の部分（ “_Sales”）と連結し、結果の文字列をINDIRECTにフィードします。
INDIRECT(B$1&"_Sales")
INDIRECT関数は、文字列をExcelが理解できる名前に変換し、VLOOKUPのtable_array引数に入力します。
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
上記の式はB2に適用され、次にそれを右下にコピーします。
注意してください。ルックアップ値（$ A2）では、数式が右にコピーされたときに列が固定されたままになるように、絶対セル参照で列座標をロックしました。B$ 1参照では、列が必要なため、行をロックしました。数式がコピーされる列に応じて、適切な名前部分を変更してINDIRECTに指定するように調整します。
メインテーブルが整理されている場合異なる方法で、行のルックアップ値と数式の範囲名の一意の部分mnの場合、ルックアップ値（B $ 1）の行座標と名前部分（$ A2）の列座標をロックする必要があります。
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
式2.複数のシートを検索するためのVLOOKUPとネストされたIF
2つまたは3つのルックアップシートでは、ネストされたIF関数を含む非常に単純なVLOOKUP式を使用して、特定のセルのキー値に基づいて正しいシートを選択できます。
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
ここで、$ A2はルックアップ値（アイテム名）、B $ 1はキー値（状態）です：
この場合、必ずしも名前を定義する必要はなく、外部参照を使用して別のシートまたはワークブックを参照できます。
その他の数式の例については、Excelで複数のシート間でルックアップする方法を参照してください。 。
これがExcelでVLOOKUPを使用する方法です。読んでいただきありがとうございます。来週のブログでお会いできることを楽しみにしています。
ダウンロード用の練習用ワークブック
高度なVLOOKUP式の例（.xlsxファイル）
- 初心者向けのExcelVLOOKUPチュートリアル
- Excelで複数の一致をVlookupする方法
- VLOOKUP & SUMまたはSUMIF関数を使用する方法Excel
- INDEX & ExcelのMATCH関数-VLOOKUPのより良い代替手段
- Excelで大文字と小文字を区別するVlookupを実行する4つの方法
- ExcelVLOOKUPが機能しない-N / A、NAMEおよびVALUEエラー