この記事では「VLOOKUP関数にて複数結果が該当する際に全て抽出する方法」について解説していきます。
結論から申し上げますと「VLOOKUP関数」と「他の関数」を組み合わせることで複数該当があっても該当する全てのレコードを見つけることが可能になります。
具体的には、
・「VLOOKUP関数」＋「COUNTIF関数」
→検索項目（上図の場合は学年クラス名）に枝番をつけた新たな項目を作成します。
・「VLOOKUP関数」＋「MATCH関数」
既に検索抽出されたレコードを含まない範囲を指定してVLOOKUP関数を使用する。
にて処理するといいです。
それでは実際のサンプルを用いて詳細を確認していきましょう。
エクセルのVLOOKUP関数にて複数結果が該当する際に全て抽出する方法1【VLOOKUP関数＋COUNTIF関数】
それでは以下で「VLOOKUP関数」＋「COUNTIF関数」にて複数結果をすべて抽出していきましょう。
一言で言うと検索項目の同一値の中で枝番をつけることにより、該当する項目が一つしかない「新たな項目」（下図の場合「クラス・枝番号」）を作成してから、VLOOKUP関数を用いる方法です。
（専門的な用語では、値が一つしかない項目（重複していない項目）を「ユニークな項目」と表現します。）
- 【STEP１】枝番の採番
検索項目（学年クラス）が同じ値の場合にCOUNTIF関数を用いて枝番を付けます。
※COUNTIF関数を用いて各行の「学年クラス」の値が何番に出てきたのかを表すことによって「枝番」とします。
B5セル：COUNTIF(C$5:C5,C5)
B6セル：COUNTIF(C$5:C6,C6)
B7セル：COUNTIF(C$5:C7,C7)
- 【STEP２】「学年クラス」と「枝番」を文字列結合
STEP１で作成した枝番号と「学年クラス」を文字列結合させた新たな項目をA列に作成します。
（※見た目でわかりやすくする為に間に“-”（ハイフン）を挟んでいます。）
A5セル：C5&”-“&B5
A6セル：C6&”-“&B6
A7セル：COUNTIF(C$5:C7,C7)
・
・
A21セル：COUNTIF(C$5:C21,C21)
この項目は、同一の値が存在しない（重複しない）ユニークな項目となっていますので、この項目を利用してVLOOKUP関数で引当てます。
- 【STEP3】検索結果表示欄の枝番号採番
検索結果を表示する欄にも「検索値」を作成するために「枝番号」を採番しておきます。（I列）
これら3つのSTEPを経たうえで以下のVLOOKUP関数で引当てます。
H9セル：VLOOKUP($H$6&”-“&I9,$A$4:$D$21,4,FALSE)
H10セル：VLOOKUP($H$6&”-“&I10,$A$4:$D$21,4,FALSE)
H11セル：VLOOKUP($H$6&”-“&I11,$A$4:$D$21,4,FALSE)
H12セル：VLOOKUP($H$6&”-“&I12,$A$4:$D$21,4,FALSE)
H13セル：VLOOKUP($H$6&”-“&I13,$A$4:$D$21,4,FALSE)
これで該当する複数結果の全てを表示（抽出）表示できました。
エラー処理方法
尚、上図から一目瞭然のごとく、抽出する検索結果がなくなってしまった（抽出しつくした）行より下は、エラー表示「＃N/A」になってしまいますので、IFERROR関数をつかってエラーを表示させないようにしましょう。
H9セル：IFERROR(VLOOKUP($H$6&”-“&I9,$A$4:$D$21,4,FALSE),””)
H10セル：IFERROR(VLOOKUP($H$6&”-“&I10,$A$4:$D$21,4,FALSE),””)
H11セル：IFERROR(VLOOKUP($H$6&”-“&I11,$A$4:$D$21,4,FALSE),””)
H12セル：IFERROR(VLOOKUP($H$6&”-“&I12,$A$4:$D$21,4,FALSE),””)
H13セル：IFERROR(VLOOKUP($H$6&”-“&I13,$A$4:$D$21,4,FALSE),””)
エクセルのVLOOKUP関数にて複数結果が該当する際に全て抽出する方法２
続いて、VLOOKUP関数とMATCH関数を用いて、複数結果のすべてを表示させていきましょう。
この方法を一言で言うと、既に検索抽出されたレコードを含まない範囲を指定してVLOOKUP関数を使用する方法です。
1回検索抽出されたらその行は含まない（※検索行の次の行から）範囲でVLOOKUP関数を使用します。
このように調整を施した範囲でVLOOKUP関数を使用すれば、常に該当結果が1つ（もしくは０）の状態になりますので、該当する結果を全て抽出することが出来ます。
この方法の場合はソースデータ（名簿）は、そのまま使用で来ます。
- 【STEP１】一番上位の検索結果は、通常通りVLOOKUP関数を使用します。
H9セル：VLOOKUP(H$6,$C$4:$D$21,2,FALSE)
- 【STEP２】MATCH関数を利用して検索範囲を調整する。
同「学年クラス」の生徒名の行座標をI列に表示させます。
(※ここがこの方法の一番重要ポイントです。)
以下のようにMATCH関数を使用します。
Ｉ9セル；MATCH(H9,$D$1:$D$25,0)
Ｉ10セル：MATCH(H10,$D$1:$D$25,0)
Ｉ11セル：MATCH(H11,$D$1:$D$25,0)
Ｉ12セル：MATCH(H12,$D$1:$D$25,0)
Ｉ13セル：MATCH(H13,$D$1:$D$25,0)
このＩ列で算出された「行座標」の次の行以降を範囲とするように指定してVLOOKUP関数を使用します。
H10 セル：VLOOKUP(H$6,INDIRECT(“C”&I9+1):$D$24,2,FALSE)
H11 セル：VLOOKUP(H$6,INDIRECT(“C”&I10+1):$D$24,2,FALSE)
H12 セル：VLOOKUP(H$6,INDIRECT(“C”&I11+1):$D$24,2,FALSE)
※赤字の「+1」が「次の行から」という意味を表しています。
INDIRECT関数は、文字列で指定したセル番地の値を表示する関数であり、今回の場合で言えば、I列に表示された行座標を用いてVLOOKUP関数の範囲指定をしています。
これで、複数該当するすべての結果が抽出することが出来ました。
この2つ目の方法の場合も1つ目の方法同様に「該当する結果」がなくなった行以下は、エラー「＃N/A」が表示されてしまいます。
よってIFERROR関数を使ってエラー表示しないようにしましょう。
【まとめ】エクセルのVLOOKUP関数にて複数結果が該当する際に全て抽出する方法
今回の記事をまとめます。
VLOOKUP関数にて該当する複数結果を抽出する方法は以下２つあります。
- 「VLOOKUP関数」＋「COUNTIF関数」
クラス名に枝番をつけた新たな項目を作成します。 - 「VLOOKUP関数」＋「MATCH関数」
既に検索抽出されたレコードを含まない範囲でVLOOKUP関数を使用する。
個人的私見ですが初心者の方にわかりやすい方法は、構造的にシンプルな「１：枝番号をつける方法」であると考えます。
ぜひ皆さんもご自身の業務で複数該当項目を抽出する場面に遭遇した際に試していただければ幸いです。
ここまでお読みいただきありがとうございました。
