【Excel】INDEXとMATCHで複数該当する値を取得【作業列を使う】

目次から見たい項目へ移動すると便利ですよ。

Excelで、INDEX関数とMATCH関数を使って、複数該当する場合の検索方法について、ご紹介します。

検索する値が1つの場合は、簡単で、次のようにINDEX関数とMATCH関数を使うとできます。

商品が「A」である行を検索できています。

検索する値によっては、検索結果が複数該当する場合があります。

次のような場合です。

商品の「A」が、複数ある場合に、INDEX関数とMATCH関数で検索しても、1つしか検索できません。

商品「A」が複数該当しますけども、1つしか検索できません。

この記事でやりたいのは、次のように複数該当する場合は、複数の検索結果を出力するということです。

商品「A」が3つあるので、検索結果が3つとなっています。

では、INDEX関数とMATCH関数で、検索する場合に、複数該当する場合の検索方法について、解説していきます。

INDEX関数とMATCH関数で、複数該当する場合に複数の結果を出力するには、作業列を使うのが簡単です。

では、やってみます。

次のような表を使います。

商品「A」を複数入力しました。

作業列を追加します。

この作業列に「COUNTIF関数」を入力していきます。

やりたいのは、作業列に、商品「A」の連番を入力する、ということです。

最終的にこんな出力結果にしたいです。

では、「COUNTIF関数」を使って、商品「A」の連番を入力していきます。

商品「A」に一致する連番を入力したいので、「=COUNTIF(C1:C2,G1)」と入力します。

あとで、下の方にコピーしたいので、COUNTIFの範囲の一部を絶対参照にします。

「=COUNTIF($C$1:C2,G1)」と入力しています。

検索条件の検索条件も絶対参照にします。

「=COUNTIF($C$1:C2,$G$1)」と入力しています。

では、下の方にコピーします。

数式をコピーした結果です。

商品「A」以外の値がいらないですね。

商品「A」以外の値を空白「""」にするために、「IF関数」を使います。

「=IF(C2=G1,COUNTIF(C1:C2,$G$1),"")」と入力しています。

下の方にコピーするので、検索値を絶対参照にします。

「=IF(C2=$G$1,COUNTIF(C1:C2,$G$1),"")」と入力しています。

では、下の方にコピーしてみます。

これで、商品「A」の連番が入力できます。

商品「A」の連番が完成しました。

この連番を使って、INDEX関数とMATCH関数で、複数該当する値を検索していきます。

先ほど作成した作業列の連番を使って、検索値を検索してみます。

INDEX関数とMATCH関数を使って、作業列が「1」である行の「A列」の値を取得します。

「=INDEX(A:A,MATCH(1,D:D,0))」と入力しています。

この数式を右の方にコピーしますので、作業列「D:D」を絶対参照にします。

「=INDEX(A:A,MATCH(1,$D:$D,0))」と入力しています。

結果をみてみます。

作業列が「1」である行の「A列」を取得できました。

この数式を右の方にコピーします。

コピーした結果です。

これで、作業列が「1」である行の値を取得できました。

同じように次は、作業列が「2」である行の「A列」の値を検索します。

セルには「=INDEX(A:A,MATCH(2,$D:$D,0))」と入力しています。

これで、作業列が「2」である行の「A列」が取得できます。

ただ、これでは面倒くさいので、「ROW関数」を使います。

これで、「1、2、3、」と連番を自動で取得することができます。

セルには、「=INDEX(A:A,MATCH(ROW(A1),$D:$D,0))」と入力しています。

結果は、同じように作業列が「1」である行の値が取得できます。

ROW関数を使って、作業列が「1」である行の値を取得できました。

これを表全体にコピーします。

作業列の「1、2、3」の値を取得できました。

これで、INDEX関数とMATCH関数を使って、複数該当する値を取得することができました。

ただ、取得できる値がないので、4行目以降がエラーとなっています。

このエラーを「IFERROR関数」を使って、空白「""」にしていきます。

エラーとなっていたセルを「IFERROR関数」を使って空白「""」にしていきます。

次のように「IFERROR関数」を入力していきます。

セルには、「=IFERROR(INDEX(A:A,MATCH(ROW(A1),$D:$D,0)),"")」と入力してます。

エラーがない場合は、同じ結果を取得できます。

では、この数式を表全体にコピーします。

結果をみてみます。

エラーがなくなりました。

こんな感じで、INDEX関数とMATCH関数を使うと、複数該当する値を取得することができます

先ほど作った数式で、検索値を「D」にしてみます。

商品が「D」である行の値を取得できました。

検索結果が1つの場合も、正しく検索できています。

この記事では、INDEX関数とMATCH関数を使って、複数該当する値を取得する方法について、ご紹介しました。

複数該当する値を取得する場合は、「作業列」を使う方法が簡単です。

INDEX関数とMATCH関数を使えば、検索できるスキルの幅が広がります。

うまくINDEX関数とMATCH関数を使いこなしていきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。