該当データが複数ありVLOOKUPで対応できないケースはExcel 2013

別シートから複数の検索データをすべて抽出 その2

重複データを抽出 OFFSET関数,MATCH関数

別シートから複数の検索データをすべて抽出する 上級技 その2

該当データが複数ありVLOOKUPで対応できないケースは
別シートに検索値に該当するデータが複数ある場合に、すべてを抽出したい時はどうすればいいでしょう。前頁へもどる

(サンプルファイルは、こちらから 2013基本技56回サンプルデータ

OFFSET関数とMATCH関数合わせ技 考え方

該当商品がなければ、空白

1.もし該当商品がなければ、空白にする。
IF関数を使って、該当するデータがない時は、空白という式を外枠に使います。
該当商品がなければ、空白

該当の商品は何個あるのか

2.IF関数の論理式に使うCOUTIF関数。
該当のギフトは何個あるのか?
COUNTIF関数を使って、商品一覧にある該当商品をカウントする式に、COUNTIF(タイプ,A3)を入れますが、
該当の商品は何個あるのかCOUNTIF
論理式にするために、カウントされた数値が行数より小さければという式にします。 何行目というのをROW(A1)からROW(A6)で表します。
隣の列のデータを取り出すのは、OFFSET関数です。 何行目か

検索値と同じ商品は何行目

3.検索値と同じギフトは何行目にある?MATCH関数の式
=MATCH(A3,タイプ,0)は、検索値がデータ範囲「タイプ」の7番目にあるということを返します。
MATCH関数
4.商品名を取り出すには OFFSET関数
OFFSET関数

OFFSET関数とMATCH関数を合わせてみる

OFFSET関数の式は、基準に商品一覧のA1、行数は、MATCH関数の式、列数は1列隣ということで、
=OFFSET(商品一覧!A1,MATCH($A$3,タイプ,0),1)
OFFSET関数で商品名を取り出す
IF関数の前半の式と合わせて使うと、以下のようになります。
該当データを複数抽出
単価の列の式は、
基準のセルが相対参照で移動B1になっています。
基準のセルは相対参照
今回は、関数をネストして合わせ技になっています。上級技ですが、引数ごとに分けて考えると、理解しやすいと思います。
今日の講義は以上です。お疲れ様でした。
もっと知りたい!MATCH関数