- VLOOKUPを使ったら、同じ商品が2つあるのに、1つしか出てこない…
- 複数該当するデータを、カンマ区切りでまとめて表示したいんだけど、どうすればいいの？
- 複数該当するデータを縦並びにしたい。
このような悩みはありませんか？
今回は上記の悩みを解決するための記事です。
- VLOOKUP関数が複数該当に対応しない理由
- 複数のデータを横に並べて表示する方法
- 複数のデータを縦に並べて表示する方法
- 複数該当を1つのセルにカンマ区切りで表示する方法（Excel 365限定）
【VLOOKUPの基本】複数該当するデータは抽出できない理由
VLOOKUP関数の使い方を簡単におさらいしておきましょう。
VLOOKUP関数は、以下の4つの引数（ひきすう）を使って設定します。
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)
- 検索値：探したいデータ
- 範囲：検索する表全体
- 列番号：取得したいデータが範囲の左から何番目にあるか
- 検索方法：完全一致か近似一致か
指定された範囲の左端の列から、上から順番に検索し、最初に一致したデータを見つけると、そこで検索を終了する仕組みになっています。
だからVLOOKUP関数だけで複数該当するデータをすべて抽出することができないです。
例えば、商品リストに「りんご」が3つあったとしても、VLOOKUPは一番最初に登場する「りんご」の情報しか返してくれません。
これが、「VLOOKUPは複数該当に対応しない」と言われる理由です。
「えっ、じゃあVLOOKUPは使えないの？」と思ってしまうのではないでしょうか？
その方法を次のセクション以降で具体的に解説していきます。
VLOOKUP関数についてより理解を深めたい方は以下の記事を参考にしてください。
【解決策1】複数該当を「横並び」で表示する方法
ここからは、VLOOKUPの代わりに使える、複数該当に対応した関数を紹介します。
まずは、複数のデータを横に並べて表示する方法です。
今回は、以下の商品リストを使ってみましょう。
【表1】
このリストから、「商品名：りんご」に該当する「色」のデータをすべて抽出して、横に並べてみましょう。
使用するのは、INDEX関数とSMALL関数、IF関数を組み合わせた少し高度なテクニックです。 少し難しそうに聞こえるかもしれませんが、ひとつずつ見ていきましょう。
ステップ1：数式の入力
まず、結果を表示したいセル（今回はG2セル）に、以下の数式を入力し、配列数式（Ctrl + Shift + Enter）の処理をします。
=IFERROR(INDEX($C$2:$C$7,SMALL(IF($B$2:$B$7=$G$1,ROW($B$2:$B$7)-ROW($B$2)+1),COLUMN(A1))),””)
- IF($B$2:$B$7=$G$1,ROW($B$2:$B$7)-ROW($B$2)+1)
- $B$2:$B$7=$G$1で、B列のデータが検索したい値（G1セルに入力された「りんご」）と一致するかを判定します。
- 一致する行が見つかると、ROW($B$2:$B$7)-ROW($B$2)+1でその行の相対的な位置（2行目なら1、4行目なら3…）を返します。
- 一致しない場合は「FALSE」が返されます。
- この部分が、{1;FALSE;3;FALSE;5;6} のような配列を返します。
- SMALL(…,COLUMN(A1))
- SMALL関数は、配列の中から「〇番目に小さい値」を抽出します。
- COLUMN(A1)は、セルF2では「1」を返します。右にコピーすると「2」「3」と増えていきます。
- これにより、1番目に小さい行番号（1）、2番目に小さい行番号（3）、3番目に小さい行番号（5）…と順番に抽出できます。
- INDEX($C$2:$C$7,…)
- INDEX関数は、指定した範囲の中から、SMALL関数が返した行番号（1, 3, 5…）に対応するデータを抽出します。
- IFERROR(…,””)
- 該当するデータがなくなった場合にエラー（#NUM!など）が表示されるのを防ぎ、代わりに空白「””」を返します。
ステップ2：数式を右にコピー
G2に入力した数式を、右隣のセル（H2, I2…）にドラッグしてコピーします。 すると、「りんご」に該当する「色」のデータが、右にずらっと並んで表示されます。
下記のようになっていればOKです！
G2セル：赤、H2セル：緑、I2セル：赤、J2セル：黄
【解決策2】複数該当を「縦並び」で表示する方法
次に、複数該当するデータを縦に並べて表示する方法です。
これは先ほどの数式を少し変えるだけで簡単に実現できます。
【表2】
今度は、店舗ごとの売上データを使って、「支店名：東京」に該当する「売上月」をすべて抽出してみましょう。
ステップ1：数式の入力
結果を表示したいセル（今回はF2セル）に、以下の数式を入力します。
=IFERROR(INDEX($C$2:$C$7,SMALL(IF($B$2:$B$7=$F$1,ROW($B$2:$B$7)-ROW($B$2)+1),ROW(A1))),””)
先ほどの横並びの数式と違うのは、COLUMN(A1)がROW(A1)に変わっているだけです。
- ROW(A1)は、セルF2では「1」を返します。下にコピーすると「2」「3」と増えていきます。
- これにより、1番目、2番目、3番目…と、下方向にデータを順番に抽出できます。
ステップ2：数式を下にコピー
F2に入力した数式を、下のセル（F3, F4…）にドラッグしてコピーします。 すると、「東京」に該当する「売上月」のデータが、縦に並んで表示されます。
【解決策3】複数該当を「カンマ区切り」でまとめて表示する方法（Excel 365限定）
もし、Excel 365の最新バージョンを使っている場合より簡単に複数該当を1つのセルにカンマ区切りでまとめて表示できます。
使用するのは、TEXTJOIN関数とIF関数です。
解決策1で使用した表を使い複数該当を抽出します。
ステップ1：数式の入力
結果を表示したいセル（今回はG2セル）に、以下の数式を入力します。
=TEXTJOIN(“,”,TRUE,IF($B$2:$B$7=$G$1,$C$2:$C$7,””))
- IF($B$2:$B$7=$G$1,$C$2:$C$7,””)
- B列のデータが検索値と一致する場合、C列のデータを返します。
- これにより、{“赤”;””;”緑”;””;”赤”;”黄”}のような配列が生成されます。
- TEXTJOIN(“,”,TRUE,…)
- TEXTJOIN関数は、複数の文字列を結合して1つの文字列にする関数です。
- 第1引数の”,”は、結合するときの区切り文字に「カンマ」を指定しています。
- 第2引数のTRUEは、空白のセルを無視するという設定です。
この数式は配列数式（Ctrl + Shift + Enter）での確定は不要です。Enterキーだけで大丈夫です。
これにより、「赤,緑,赤,黄」のように、1つのセルにデータがまとめて表示されます。
1つのセル（G2)にのみ出力されているのは見た目が綺麗ですね！
まとめ：3つの方法を使い分けよう
| 方法 | 抽出結果 | 特徴 |
|---|---|---|
| INDEX+SMALL+IF | 横並び or 縦並び | ほとんどのExcelバージョンで使える。行や列を分けて表示したい場合に便利。 |
| TEXTJOIN+IF | カンマ区切り | Excel 365以降限定。1つのセルにまとめて表示したい場合に最適。 |
「VLOOKUPでできないなら、もう諦めよう…」と思っていた方も、これで解決できたのではないでしょうか。
今回ご紹介した方法は少し複雑に見えるかもしれませんが、仕組みが分かればとても便利です。ぜひあなたのエクセル業務に取り入れてみてくださいね。
コメント