「StiLL」Excel情報495　関数--VLOOKUP関数で同じ検索値が複数ある場合の検索方法

【テーマ】
VLOOKUP関数は同じ検索値が複数ある場合、最初に一致した検索値を抽出しますので、正しい値が返されません。今回は同じ検索値が複数ある場合でも正しい値を抽出できる方法をご紹介します。

【方法】
１．COUNTIF関数で連番を作る。２．「１」で作成した値をVLOOKUP関数の検索値として使う。

【参考】
今回ご紹介した方法は、VLOOKUP関数の代わりにINDEX関数とMATCH関数の組み合わせでも実現可能です。

今回はVLOOKUP関数で同じ検索値が複数ある場合でも、正しい値を抽出できる方法をご紹介します。

下図のような「受注履歴」データから「受注日」が「2019/6/1」のデータを抽出しようとしても、

検索対象範囲の上から一番最初に見つかったデータしか表示しません。

これからご紹介する方法を使えば、下図のように正しくデータを抽出することができます。

例として下図を使用します。

データ：「受注履歴」シート

受注データは、受注日順に並べておきます。

データ抽出先：「受注検索画面」シート

例題は５行分ですが、データ件数により、適宜行数を加減して下さい。

■考え方

VLOOKUP関数で、完全一致を指定して検索する場合、

検索する元のデータも重複しない項目でなければなりません。

重複のある受注日では検索対象に出来ませんので、

別途、重複しない検索項目を作成して、検索対象とします。

■「COUNTIF関数」の設定

　　「受注履歴」シートの「受注日」の左に作業用の列(例では「重複番号」)を追加して、

　　そこにCOUNTIF関数の数式を入力します。

　　　　※COUNTIF関数については、以下のバックナンバーをご参照ください。

【バックナンバー276】セル--入力規則第ニ弾重複入力をチェックするプログラム

　　B4セルの数式は、下記のように設定します。

=COUNTIF($C$4:C4,受注検索画面!$C$3)

　　　　・範囲

「受注日」の最初の値(C4セル)を指定します。

　　　設定した数式は下の行にコピーしていくので、

　　　範囲の先頭は $C$4 のように絶対参照にして固定します。

　　　　・検索条件

別のシート「受注検索画面」シートのセルになります。

　　　受注データ抽出!$C$3 のように、絶対参照で固定します。

　　数式を設定したら、下の行にコピーします。

　　「重複番号」列に番号が反映されました。

■「VLOOKUP関数」の設定

「受注検索画面」シートの得意先名を表示する最初の行(下図では、C6セル)に

VLOOKUP関数を設定しますが、

該当のデータが存在しなかった場合の「#N/A」を表示させないように、

「IFERROR関数」も使用します。

　　　　※VLOOKUP関数については、以下のバックナンバーをご参照ください。

【バックナンバー242】関数--検索機能 VLOOKUP

　 ※IFERROR関数については、以下のバックナンバーをご参照ください。

【バックナンバー244】関数--エラーを非表示「IFERROR」

　　　C6セルには下記の数式を入力します。

=IFERROR(VLOOKUP(B6,受注履歴!$B$3:$E$100,3,FALSE),"")

　　　　・検索値

B列の「No.」項目のセルを指定します。

　　　　・範囲

検索対象である「受注履歴」シートの「重複番号」の列が先頭になるように範囲指定します。

数式設定後、下の行にコピーするので絶対参照で固定します。

　　　　・列番号

「3」で「得意先名」を指定します。

　　　　・検索方法

完全一致の FALSE を指定します。

　　　数式を設定したら、下の行にコピーします。

「得意先名」が表示されました。

上記と同じように、「商品名」にも数式を設定します。

D6セルに下記数式を設定後、下の行にコピーします。

'=IFERROR(VLOOKUP(B6,受注履歴!$B$3:$E$100,4,FALSE),"")

「受注日」が「2019/6/1」の「得意先名」と「商品名」が正しく表示されました。

「受注日」を「2019/6/2」に変えても正しい「得意先名」「商品名」が表示されます。

■ご参考までに

　　　上記の方法でVLOOKUP関数の代わりに、

　　　INDEX関数とMATCH関数を組み合わせて使うことで検索可能です。

　　　　※INDEX関数とMATCH関数の組み合わせについては、以下のバックナンバーをご参照ください。

【バックナンバー342】関数--MATCH関数

C6セル(得意先名)

=IFERROR(INDEX(受注履歴!$B:$E,MATCH(受注検索画面!$B6,受注履歴!$B:$B,0),3),"")

D6セル(商品名)

=IFERROR(INDEX(受注履歴!$B:$E,MATCH(受注検索画面!$B6,受注履歴!$B:$B,0),4),"")