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

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

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

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

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

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

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

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

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

2.IF関数の論理式に使うCOUTIF関数。
該当のギフトは何個あるのか？
COUNTIF関数を使って、商品一覧にある該当商品をカウントする式に、COUNTIF(タイプ,A3)を入れますが、

論理式にするために、カウントされた数値が行数より小さければという式にします。 何行目というのをROW(A1)からROW(A6)で表します。
隣の列のデータを取り出すのは、OFFSET関数です。

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

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

4.商品名を取り出すには　OFFSET関数

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

OFFSET関数の式は、基準に商品一覧のA1、行数は、MATCH関数の式、列数は1列隣ということで、

=OFFSET(商品一覧!A1,MATCH($A$3,タイプ,0),1)

IF関数の前半の式と合わせて使うと、以下のようになります。

単価の列の式は、
基準のセルが相対参照で移動B1になっています。