【ExcelVBA】VLOOKUPではできない複数該当したセルを全て表示させる方法。自作関数MULTIVLOOKUP。

趣味のプログラム

2022.06.10 2022.05.03

ExcelのVLOOKUPは表から検索して１つのセルを検索結果として表示する関数です。
使っていると分かるのが、複数一致するセルがあっても最初に見つけた１つのセルしか結果として表示されません。

今回はそんなVLOOKUPで出来ない複数該当するもの全てを表示させる方法を説明します。

この記事は、

VLOOKUPの結果を複数表示させたい

そんな方に読んでもらいたいです。

マルチVLOOKUP使い方
自作関数の設定
VBAはどうやって覚える？
まとめ

マルチVLOOKUP使い方

VLOOKUP関数のように表から文字を検索します。
二つの違いを比べてみます。

ここに表があります。

E２セルには普通のVLOOKUPで赤を検索した結果を表示しています。

これでは複数の赤があっても一番上のいちごしか表示されません。

次にE３セルにMULTIVLOOKUPで赤を検索してみます。

※MULTIVLOOKUP（マルチブイルックアップ）は自作関数です。
自作関数の設定方法は後で説明します。

赤に該当する全ての結果、いちご、りんご、さくらんぼが１つのセルに表示されました。

通常のVLOOKUPでは該当した文字の一番上しか結果に表示されませんが、MULTIVLOOKUPではカンマ区切りで全てを１行に結果として表示することができます。

動きが思い通りだった場合は、次の自作関数の設定を行ってください。
自作関数の設定はプログラムのコピペでできるので簡単にできます。

自作関数の設定

プログラムをコピペする画面を開く操作の説明です。
ツールの[開発]から[Visual Basic]を選択して「Microsoft Visual Basic for Applications」を開く

開発タブが無い場合は、[ファイル]-[オプション]-[リボンのユーザー設定]で開発のチェックを入れて下さい。

ツールの[挿入]から[標準モジュール]を選択

真っ白な画面が出るので下のコードをコピーして貼り付けます。

このコードをコピーして貼り付けます。

Sub test()
    a = MULTIVLOOKUP("ゴールデンウィーク", Range("B:C"), 3, False)
End Sub
Function MULTIVLOOKUP(検索文字, 検索範囲, 列番号, 一致区分)
    開始行 = 検索範囲.Cells(1).Row
    最終行 = 検索範囲.Cells(検索範囲.Count).Row
    検索列 = 検索範囲.Cells(1).Column
    抽出列 = 検索列 + 列番号 - 1
    
    '最終行をデータのある行に見直す
    最終行 = Cells(最終行, 検索列).End(xlUp).Row
    
    検索リスト = WorksheetFunction.Transpose(Range(Cells(開始行, 検索列), Cells(最終行, 検索列)))
    抽出リスト = WorksheetFunction.Transpose(Range(Cells(開始行, 抽出列), Cells(最終行, 抽出列)))
    一致数 = Filter(検索リスト, 検索文字)
    
    ReDim 結果リスト(1 To UBound(一致数) + 1)
    j = 1
    For 捜査位置 = LBound(検索リスト) To UBound(検索リスト)
        If Not 一致区分 Then
            If 検索リスト(捜査位置) = 検索文字 Then
                結果リスト(j) = 抽出リスト(捜査位置)
                j = j + 1
            End If
        End If
        If 一致区分 Then
            If 検索リスト(捜査位置) Like "*" & 検索文字 & "*" Then
                結果リスト(j) = 抽出リスト(捜査位置)
                j = j + 1
            End If
        End If
    Next
    MULTIVLOOKUP = Join(結果リスト, ",")
End Function

コピーが出来たら一度Excelの拡張子を.xlsmで保存して、MULTIVLOOKUPを試してみてください。

VBAはどうやって覚える？

プログラムのコードが読めないと、参考にしているコードの理解や自分でプログラムを書くときに時間がかかってしまいます。
そんな時は基礎から学ぶと応用もできるようになり自分の作りたいプログラムがスムーズに作れるようになります。

無料でやるならネット上の学習サイトを参考にするといいです。
こちらはVBAの基礎から説明している無料のWEBサイトになります。

>>基礎から学べるWEBサイトを見てみる

過去にネットでの学習でつまずいた方には専門書をオススメします。
本なら基礎から学べる事はもちろん、読者視点で分かりやすい解説になっています。
一人で集中してコツコツ進めたい方は書籍を試してください。