IndexとMatch関数で条件に合うデータを抽出する

vlookupは、Excelで最も常用な検索関数ですが、逆方向や双方向などの複雑なテーブルを検索する場合、下記のindex+Match関数の組み合わせを使用する必要があります。

逆方向に検索する

例1

次の図のように、品名から型番を抽出します。

分析

まず、Match関数で品名に従って列Cの位置を見つけます。

=MATCH(B13,C5:C10,0)

また、Index関数で見つかった位置に従って列Bから値を抽出します。完全な式は次のとおりです。

=INDEX(B5:B10,MATCH(B13,C5:C10,0))

双方向に検索する

例2

次の図のように、月と費用の項目から金額を求めます。

分析

最初にMATCH関数を使用して、最初の行の3月の位置を見つけます。

=MATCH(B10,$A$2:$A$6,0)

次、MATCH関数を使用して、列Aの費用の位置を見つけます。

= MATCH(A10,$B$1:$G$1,0)

最後に、INDEX関数を使用して、見つけた行と列の位置に基づいて金額を抽出します。

INDEX(配列,行番号,列番号）

=INDEX(B2:G6,MATCH(B10,$A$2:$A$6,0),MATCH(A10,$B$1:$G$1,0))

複数の条件で検索する

例3

次の図のように、日付と品名から販売数量を求めます。

分析

match関数の2番目の引数はマージされた配列をサポートできるため、マージして直接検索できます。

=MATCH(C11&C12,B4:B9&C4:C9,0)

値を見つけた後、INDEX関数を使用して、値を抽出します。

=INDEX(D4:D9,MATCH(C11&C12,B4:B9&C4:C9,0))

数式には配列の計算が含まれているため（数値のグループと別の数値のグループが同時に計算される）、ctrl+shift+enterキーを押す必要があります。

indexとmatch関数を使用して、ワークシート全体のデータを取得できます。

下記の図はワークシート1の内容です。

どうやってデータを日付と項目に従ってワークシート2にコピーしますか？そして項目の順序は並び替えました。

ここでは、MATCHとINDEXの2つの関数を組み合わせて使用できます。

セルB2をクリックし、数式「=INDEX(sheet1!$B2:$K2,,MATCH(B$1,sheet1!$B$1:$K$1,0))」を入力し、Enterキーを押すると、数式はセルB2:K15にコピーします。

[数式の説明]

1 MATCH(B$1,sheet1!$B$1:$K$1,0)はワークシート2のセルB1はワークシート1の列Bから列Kまでを返します。

2 INDEX(sheet1!$B2:$K2,,MATCH(B$1,sheet1!$B$1:$K$1,0)) はワークシート1のセル範囲B2からK2までのデータを返します。