Excel
GoogleSpreadSheet

検索値が複数該当する時に2つ目以降の数値をVLOOKUP関数で抽出する

VLOOKUP2つめ.png

利用シチュエーション

  • VLOOKUP関数を用いて検索値の二つ目以降のデータを抽出したい時

直面した問題

 × VLOOKUP関数では検索値に引っかかる値が複数ある時に、最初に引っかかった値しか抽出しない

記述方法

=VLOOKUP(検索値,INDEX(元データの検索値,MATCH(検索値,元値の検索値,0)+1):元の値の2列目最末行,2,)

<記述例>
=VLOOKUP(A2,INDEX(E2:E13,MATCH(A2,E2:E13,0)+1):F13,2,)

<補足>
※3個目、4個目を抽出したい場合は+1の値を1ずつプラスする

前提条件
A列に購入日(下に行くごとに1日ずれる)
E列・F列に「購入した日」と「購入した商品」を上から記載
抽出前.png


抽出結果
抽出後.png


解説

範囲の抽出

  • INDEX関数で指定した範囲内で検索値に引っかかる行数をMATCH関数で指定。
  • 抽出した行数より1つ下(+1)から商品名の一番下までをVLOOKUPの検索範囲に指定。
  • 指定した範囲の2行目から該当のデータを抽出。

個々の関数の説明

INDEX関数は指定された行と列が交差する位置にあるセルの値を返す

書き方 利用例
書式 INDEX(範囲, 行番号, 列番号, 領域番号) INDEX(E2:E13,E3:F13,2)
引数1 1つまたは複数のセルのを範囲を指定 E3:F13
引数2 指定した範囲の上からの行数を指定 2
引数3 指定した範囲の左からの行列数を指定 (省略)
引数4 複数の領域を選択した場合、どちらの範囲を使用するか指定

MATCH関数は、指定したセルの範囲から検索値が上から何番目にあるかを返す

書き方 利用例
書式 MATCH(検査値, 検査範囲, 照合の型) MATCH(A2,E2:E13,0)
引数1 検索する値またはセル参照を指定 A2
引数2 検索するセルの範囲を指定 E2:E13
引数3 1、0、-1の数値で指定(0は完全一致) 0

VLOOKUP関数は、指定された範囲の1列目で値を検索し、その指定範囲内の別の列の同じ行にある値を返す

書き方 利用例
書式 VLOOKUP(検索値, 範囲, 列番号, 検索の型) VLOOKUP(A2,E3:F13,2)
引数1 検索する値またはセル参照を指定 A2
引数2 2列以上のセル範囲を指定 E3:F13
引数3 目的データが入力されている列番号を指定 2

サンプル

補足

サンプルシートでは、2つめの商品がある場合のみVLOOKUPを実行するようにIF関数で制御
=IF(COUNTIF(E2:E13,A2)<2,"",VLOOKUPで検索)