VLOOKUP関数で複数列の値を一度に抽出する方法！VBAで同じ結果も可！

VLOOKUP関数で複数列の値を一度に抽出をしたいときはないでしょうか。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する
VLOOKUP関数で複数列の値を一度に抽出する方法とVBAで同じ結果を得る方法について
まとめます！

VLOOKUP関数で複数列の値を一度に抽出をする方法

VLOOKUP関数で複数列の値を一度に抽出をする方法について説明をします。

まず結論としてはVLOOKUP関数ではなくXLOOKUP関数を使います。

その理由はVLOOKUP関数では複数列抽出に対応していないので列数分設置があり煩雑になってしまいますが、XLOOKUP関数は、VLOOKUP関数で足りない部分を拡張した関数で、VLOOKUP関数の機能に加え複数列に対応しているため、設置件数も最小限にすることができます。

次にVBAですが、VLOOKUP関数、XLOOKUP関数を使わずに、カンタンな設定で同じ結果を出力していきます。

今回のサンプルデータと実行結果について

今回のサンプルデータは以下の通り、A列に商品名、B列に「単価」、C列に「産地」、D列に「生産者」が入力されていて、「商品マスター」とします。

次にF～L列を「売上表」として、G列に売れた商品名、I~K列に商品マスターから取得した各商品ごとの単価を商品マスタより取得し入力していきます。

売上金額は単価が入力された時点で個数と掛け合わせ算出されます。

各処理の実行結果となります。

■XLOOKUP関数

■VBA実行

さらにXLOOKUP関数では指定できない隣接しない列番号指定で出力をしていきます。

それでは早速使ってみましょう！

XLOOKUP関数を使い、複数列の値を取得する

XLOOKUP関数を使い、一つの関数設定で複数の値を取得し、複数のセルへ結果を出力していきます。

XLOOKUP関数の書式

XLOOKUP関数の書式は以下の通りです。

参考：https://support.microsoft.com/ja-jp/office/xlookup-%E9%96%A2%E6%95%B0-b7fd680e-6d10-43e6-84f9-88eae8bf5929

XLOOKUP関数を実装する

XLOOKUP関数を実装してみましょう。

I2セルを選択し、以下を入力します。

まずは一つの値が取得できました。

次に複数列の値を取得できるようにしましょう。

同じくI2セルへ以下を入力します。

戻り範囲の指定で複数列を含めるとによりその列の値を取得し、同時に隣接セルへ結果を出力することができます。

表の最下部まで数式をドラッグしましょう。

はい、複数列の値が取得できましたね。

タカヒロ

XLOOKUP関数を隣接セルに設定しなくてもよいのは手間が省けてうれしいですね。

つづいてVBAでXLOOKUP関数と同じ結果を取得していきましょう。

そしてさらに隣接列ではない単体の列指定で複数列の値を取得していきましょう！

XLOOKUP関数と同じ結果を出力するVBA

これまでXLOOKUP関数にて行ってきた処理をXLOOKUP関数を使わずにVBAだけで処理をしていきます。

まずはXLOOKUP関数と同様の結果である、1つの検索値に対して1つの検索結果の値を取得していき、隣接する複数列の値も同時にシートへ出力していきます。

フォーマットは関数と同じものを利用します。出力先を空欄にしましょう。

コード

VBAは以下の通りです。

Sub VLOOKUP関数と同様の結果を取得_複数列出力()

    Dim strSerchRange As Variant
    Dim strSerchKeys As Variant
    Dim strSerchResults As Variant
    Dim strSerchResultsRange As String
    Dim arrColNums As Variant


    Dim i As Integer
    Dim j As Integer
    Dim l As Integer
    
    
    '検索値の範囲を指定します。
    strSerchKeys = Range("G2:G12")
    
    '検索結果出力先を指定します。※2つ以上の場合は列を変更してください。隣り合わせの列のみ指定できます。
    strSerchResultsRange = "I2:K12"
    
    '検索対象セルを範囲指定します。なお、1列目が主キーになるようにします。
    strSerchRange = Range("A2:D7")
    
    '検索結果に出力する複数の列番号を指定します。
    arrColNums = Array(2, 3, 4)
    
    '値のみをクリアします。
    Range(strSerchResultsRange).ClearContents
    
    '検索結果用の配列を作成します。
    strSerchResults = Range(strSerchResultsRange)

    '配列に格納された値すべてを対象に処理します。
    For i = LBound(strSerchRange, 1) To UBound(strSerchRange, 1)
    
        For j = LBound(strSerchKeys, 1) To UBound(strSerchKeys, 1)
        
                '検索キーと表の主キーがマッチしているか確認します。
                If strSerchRange(i, 1) = strSerchKeys(j, 1) Then
                    '検索結果を配列へ格納します。同時に指定列番号分の値も格納します。
                    For k = LBound(arrColNums) To UBound(arrColNums, 1)
                        strSerchResults(j, k + 1) = strSerchRange(i, arrColNums(k))
                    Next
                    
               End If
               
               'マッチしない場合代わりの値を格納します。
               If strSerchResults(j, 1) = "" Then
                    For k = LBound(arrColNums) To UBound(arrColNums, 1)
                        strSerchResults(j, k + 1) = "該当なし"
                    Next
               End If
        Next
    Next
    
    
    '配列に格納した検索結果を指定Rengeへ出力します。
    Range(strSerchResultsRange) = strSerchResults

End Sub

VBAを設定する

VBAの設定をしましょう。

検索値の範囲を指定します。
サンプルでは売上表の「売れた商品列」を指定しています。

検索結果出力先を指定します。※2つ以上の場合は列を変更してください。隣り合わせの列のみ指定できます。

検索対象セルを範囲指定します。
サンプルでは商品マスタの範囲をして指定ます。
なお、1列目が主キーになるようにします。

検索結果に出力する複数の列番号を指定します。
サンプルでは商品マスタのB,C,D列を指定したことになります。

VBAを実行する

VBAを実行してみましょう。

はい、XLOOKUP関数と同様の結果が出力されていますね！

戻り値の列の内、隣接しない列番号を指定する

続いて、戻り値の列の内、隣接しない列番号を指定して、列の値をしシートへ出力してみます。

具体的には、「単価」、「産地」、「生産者」とマスタの列の並び順で取得していましたが、「単価」、「生産者」と列番号を指定し絞り込んでみます。

VBAを設定する

検索結果に出力する複数の列番号を指定します。

タカヒロ

2，3，4から3を抜かした形ですね。

VBAを実行する

はい、隣接しない列の値も取得できていますね！

VBAの説明

検索値の範囲を指定します。

検索結果出力先を指定します。※2つ以上の場合は列を変更してください。隣り合わせの列のみ指定できます。

検索対象セルを範囲指定します。なお、1列目が主キーになるようにします。

検索結果に出力する複数の列番号を指定します。

値のみをクリアします。

検索結果用の配列を作成します。

配列に格納された値すべてを対象に処理します。

検索キーと表の主キーがマッチしているか確認します。

検索結果を配列へ格納します。同時に指定列番号分の値も格納します。

マッチしない場合代わりの値を格納します。

配列に格納した検索結果を指定Rengeへ出力します。

VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。

さいごに

いかがでしょうか。

今回は、
VLOOKUP関数で複数列の値を一度に抽出する方法とVBAで同じ結果を得る方法について
まとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。