利用シチュエーション
- 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列に「購入した日」と「購入した商品」を上から記載
抽出結果
解説
範囲の抽出
- 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で検索)