VLOOKUP関数で複数該当・重複する２番目以降を抽出する

ここでは、VLOOKUP関数で「検索値に複数該当・重複する場合の２番目以降を抽出する方法」を解説します。

VLOOKUP関数でもできなくはないのですが、別の関数を使えばもっと簡単です。

そのため、このページではVLOOKUP関数ではない簡単な方法を、次のページではVLOOKUP関数を使った方法を説明します。

ほとんどの人が知っていると思いますが、念のため確認します。

VLOOKUP関数は、基本的に「該当した１番上の行」だけしか抽出できません。

一つ例を見てみます。

上の表は、社員の基本情報が載った一覧表です。

この表から、「正社員の人（A列が〇の人）」をVLOOKUP関数で抽出してみます。

条件に該当する人は全部で6人いますが、VLOOKUP関数では上のように「該当する中で一番上の値（例だと№1の人）」しか抽出できません。

例え2番目、3番目を抽出したくても、そのままの数式では「何番目」を選ぶことができないのです。

一応引数「範囲」を手でいじることでも抜き出せますが、いちいち数式を直すというのは現実的ではありません。

VLOOKUP関数で検索値（条件）に該当するデータが複数ある場合、「番目」を指定して抽出する方法はあります。

しかし、数式は長く複雑になるためおすすめはできません。

そして、別の関数でやりたいことがもっと簡単に実現できるので、そちらを使った方が便利です。

ですからこのページではまず、「VLOOKUP関数ではない別の関数を使って条件に複数該当するデータを抜き出す方法」を見ていきます。

なお、この次のページではVLOOKUP関数を用いた方法も紹介します。

では、数式の形と使い方のポイント、数式作成の手順を見てみましょう。

３－１．数式の形

「条件に複数該当・重複する２番目以降を抽出」する数式の基本形は、次の通りです。

※ 条件が一つの場合

引数は、「元データの表示列」、「条件式」、「条件範囲」、「番目」の４つです。

３－２．引数の指定の仕方

続いて、各引数の指定の仕方を見てみます。

３－２－１．抽出列

「抽出列」には、「元データのうちで抽出したい列（「列」とは横のA,B,C…のこと）全体」を１列指定します。

例えば、元データのうち抽出したい列がA列の場合には「A:A」、D列の場合には「D:D」となります。

３－２－２．条件式

「条件式」は、

条件範囲 → 比較演算子 → 条件値

の順で指定します。

注意点は次の通りです。

 　※文字列や日付、時刻を条件値に入れる場合には「"」で囲むこと

例えば、条件範囲を「A5からA10」、条件を「〇か？」としたい場合、それぞれ次のようになります。

つなげると条件式は、

$A$5:$A$10="〇"

となります。

また、条件値をセル番号で指定する場合には、絶対参照にします。

例えば、先ほどの例で条件値「〇」がセルB1に入っている場合、条件式は次のようになります。

$A$5:$A$10=$B$1

このように、条件値にも「$」を2つ付けます。

適切に絶対参照にしないと、数式を他のセルにコピーした時に正しい結果が返ってこないので、注意しましょう。

３－２－３．条件範囲

条件式で指定した「条件範囲」を、ここにも入れます。

同じく「$」を4つ付けて絶対参照にします。

厳密に言うと、列番号は条件範囲と違っても問題ありません。ただ、全く同じものを入れると覚えたほうが分かりやすいと思います。

３－２－４．番目

「番目」とは、1番目、2番目などの順番のことです。抽出したい「番目」を、「数値」または「数値の入ったセル番号」で指定します。

数値の場合には、「1」、「10」などそのまま入れます。

セル番号を入れる場合には、やはり「$」を2つ付けて絶対参照にします。

例を用いて数式を作ってみます。

先ほども見た社員一覧表です。この表から、「A列が〇の2番目の人」の氏名や年齢、性別を抽出してみます。

（１）以下の数式をコピーし、結果を表示したい範囲の左上のセルに貼り付ける

【コピーする数式】

※ 複数条件の場合には、この後紹介する数式をコピーし貼り付けます

（２）「抽出列」の文字を消し、元データのうち抽出したい列をクリックする

※ 列番号（赤矢印部分）を押すと簡単です

（３）「条件式」の文字を消し、まず「条件範囲」を指定する

※マウスの左ボタンを押しながら選択すると簡単です

（４）キーボードのF4キーを１回押し、「$」を4つ付ける

※ F4キーはキーの最上段にあります

（５）比較演算子を入力する

※ 条件に応じて「=」か「<>」、「>」などの記号を入れます

（６）条件値を入力する

※ 条件値の入れ方は、この後詳しく解説します

（７）「条件範囲」の文字を消し、「条件範囲」を指定する

（８）キーボードのF4キーを１回押し、「$」を4つ付ける

（９）「番目」の文字を消し、「番目」を指定する

（１０）セル番号の場合はF4キーを1回押し、$を2つ付ける

（１１）Enterキーを押す

（１２）セルの右下にカーソルを当てる

（１３）「+」マークになったら右方向に左ドラッグすれば完了

※ 左ドラッグとは、マウスで左クリックを押しながらカーソルを移動させることです

結果、セルH5に入れた数式は次のようになります。

=IFERROR(INDEX(B:B,1/LARGE(INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0),$G$5)),"")

慣れるととても簡単です。

条件値は「文字列」、「数値」、「セル番号」などの「値の型」によって指定の仕方が違います。

５－１．値を入力する場合

条件値に文字列などの「値」を入力する場合、型によって次のように指定します。

（１）文字列

条件値を「"」で囲みます。

　例）条件値が「〇」→”〇"

（２）数値

そのまま入力します。

　例）条件値が「100」→100

（３）日付

DATEVALUE関数を使い「"」で囲みます。

　例）条件値が「2022年1月1日」→DATEVALUE("2022/1/1”)

（４）条件値が時間

TIMEVALUE関数を使い「"」で囲みます。

　例）条件値が「18:00」→TIMEVALUE("18:00")

５－２．セル番号を入力する場合

セルに入力された値を条件値にする場合には、条件値の型に関わらず、セル番号をそのまま入力します。

ただし、「$」を2つ付けて絶対参照にします。

セル番号入力後にF4キーを１回押しましょう。

　例）セルA1に条件値が入っている→$A$1

VLOOKUP関数ではなかなか面倒くさい「複数条件」も、この数式だと簡単に指定できます。

「条件のどれも満たせば真」というAND条件の場合、数式は次のようになります。

【条件が二つの場合】

【条件が三つの場合】

【条件が四つの場合】

このように、「*（条件式）」を追加すれば、条件を増やせます。

このように、VLOOKUP関数を使わない形は比較的簡単に「番目」を指定してデータを抽出することができます。

また、複数条件の指定可能など、できることの幅が広いのも特徴です。

思ったよりもシンプルな数式で、任意の番目のデータが抽出できたと思います。

そこで次に思うのが、「なんでこの数式で求められるの？」だと思います。

この数式の解説は、次のページで行っています。「番目」は「ROW(A1)」となっていますが、基本的な形や動きは同じです。

よかったら参考にしてください。

　⇒ 条件に合うデータを関数で全て順に抽出できる数式の意味は

なお、この次のページではあえてVLOOKUP関数を使った「番目」を指定できる数式を説明します。

　⇒  あえてVLOOKUP関数で複数該当する２番目以降を抽出するには

VLOOKUP関数のその他の記事はこちらです。

• VLOOKUP関数その他の記事はこちら

• ◎基本的な使い方
• ◎「以上/未満」の条件指定の仕方
• ◎〇番台の条件にする方法
• ◎部分一致の条件指定の仕方
• ◎別シートから値を抜き出す方法
• ◎別ブックから値を抜き出す方法
• ◎数式コピーで横方向の値を簡単に取得する方法
• ◎２番目、３番目以降の値を抽出する方法
• ◎＃N/Aエラーになる３つの原因と対処方法
• ◎＃REF!エラーになる原因と対処方法
• ◎＃VALUE!エラーになる原因と対処方法
• ◎空白を０ではなく空白で表示する２つの方法
• ◎「IF(ISERROR(VLOOKUP」の意味と使い方
• ◎IFERROR関数とVLOOKUPの組み合わせ方
• ◎空白でもエラーでも空白で表示する方法
• ◎複数条件にする方法
• ◎セルや範囲を固定しコピーのズレを防ぐ方法
• ◎文字列を条件にする方法
• ◎列番号の意味と指定の仕方

関数の解説記事一覧へはこちら