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

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

2013技BEST 56回 OFFSET関数,MATCH関数

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

別シートに検索値に該当するデータが複数ある場合に、すべてを抽出したい時はどうすればいいでしょう。

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

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

別シートにコーヒーギフトのタイプ別一覧があります。
データを扱いやすくするために、ギフトタイプの範囲に名前をつけて「タイプ」としてあります。 あ
こちらは、検索用のシートで、ギフトタイプは、ドロップダウンリストから選択できるようになっています。
あ
タイプを選択すると、隣の列に対応するギフトの一覧が表示されるようにしたいのですが、検索タイプに該当するデータが複数あるので、VLOOKUP関数では対応できません。

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

1.もし該当商品がなければ、空白にする。IF関数
2.該当のギフトは何個あるのか?COUNTIF
3.検索値と同じギフトは何行目にある?MATCH
4.商品名を取り出すには OFFSET関数

OFFSET関数とMATCH関数どう使う

OFFSET関数とMATCH関数を使うのですが、その前に書式を復習しましょう。
OFFSET関数は、指定された行数と列数だけシフトした位置にあるセルの参照を返します。(またはセル範囲への参照を返します。)
OFFSET関数
MATCH関数は、照合の型に従って参照または配列に含まれる値を検索し、検査値と一致する要素の相対的な位置を数値で返します。
a
MATCH関数が、案内役で検索値の場所を教えてくれますから、OFFSET関数で取り出すという形になります。

それでは次のページで具体的にそれぞれの関数の式を見て行きましょう。