表から縦方向に検索し抽出するVLOOKUP関数と、横方向に検索するHLOOKUP関数はExcel(エクセル)の定番関数です。
表を縦方向(列)と横方向(行)に検索する機能をそれぞれ持っていますが、特定の行と列がクロスするセルの値を自動で取り出す場合は、ちょっとしたテクニックが必要です。
今回の記事では、下の表から「〇〇店の〇〇商品」を自動で抽出出来るような関数の使い方として、『特定の列と特定の行が交わる(クロスする)セルの値を抽出する方法』を紹介します。
クロス抽出する３つの方法
『特定の列と特定の行が交わるセルの値を抽出する』ことを『クロス抽出』と呼びます。
この『クロス抽出』のためには、以下の３つの方法があります。
- INDEX関数とMATCH関数を組み合わせる方法
- VLOOKUP関数とMATCH関数を組み合わせる方法
- HLOOKUP関数とMATCH関数を組み合わせる方法
それぞれMATCH関数ともう１つの関数を使ってクロス抽出しますが、私のお勧めの方法は①→➁→③の順番です。
上から順番に各見出しで解説しますので、関数の引数の設定がイメージしやすい方法を利用してください。
MATCH関数の使い方
『クロス抽出』するには、列の位置や行の位置を自動で判断するためにMATCH関数を利用します。
まずはMATCH関数の基本的な使い方を覚えましょう。すでに理解出来ている方は次の見出しまで読み飛ばしてください。
MATCH 関数(マッチ)は、検索範囲の中から検索値を検索し、その範囲内での検索値の相対的な位置を返します。
＝MATCH(検索値, 検索範囲, [照合の型])
検索値： 検索範囲の中で照合する値を指定します。
検索範囲： 検索するセルの範囲を指定します。
照合の型 ： ”-1”、”0”、”1 ”の数値のいずれかを指定しますが、”1 ”の場合は省略可能です。今回は、「検索値に一致する値のみが検索の対象」とする”0”を利用します。
実際に、下の図でMATCH関数の使用例を説明します。
下の図では、B5セルに「=MATCH($C$2,B2:E2,0)」とMATCH関数を挿入しています。
検索値のC2セル(商品カテゴリ)は、検索範囲のB2:E2の左から２番目にあるので、関数の結果は”２”と表示されます。
上の図ではMATCH関数を横方向の検索範囲で使用しましたが、縦方向でも利用出来ます。
下の図では、B5セルに「=MATCH($B$4,B2:B5,0)」とMATCH関数を挿入しています。
検索値のB4セル(商品名)は、検索範囲のB2:B5の上から3番目にあるので、関数の結果は”3”と表示されます。
クロス抽出する方法①(INDEX関数とMATCH関数)
１つ目のクロス抽出する方法は、INDEX関数の中にMATCH関数を利用する方法です。
INDEX関数は、行と列が交差する位置にあるセルの値を抽出することが出来る関数です。
INDEX関数の使い方
INDEX関数（インデックス）の使ったことが無い方は、この見出しで学習しましょう。
INDEX関数（インデックス）は、指定された行と列が交差する位置にあるセルの値を抽出する関数です。VLOOKUP関数やHLOOKUP関数と同じく、表から抽出することが出来る関数です。
関数の構文は下のとおりです。
＝INDEX(配列,行番号,[列番号])
配列は検索範囲のことで、行番号と列番号は数値の入力されたセルを指定するか、直接関数の中に数値を指定します。
実施に下の商品リスト表でINDEX関数を使ってみます。
G10セルに下のINDEX関数が挿入されています。検索範囲(C4:E10)の中から、５行目(G5セル)の３列目(H5セル)の値(2,000円）を抽出することが出来ます。
=INDEX(C4:E10,G5,H5)
INDEX＆MATCHを組み合わせる手順
それでは、以下の表から『〇〇店の〇〇商品』の値段を自動でクロス抽出する方法を解説します。
手順１．INDEX関数の第１引数「検索範囲」を指定
クロス抽出した結果を表示したいセルに、INDEX関数を挿入します。第１引数「検索範囲」として表全体を選択します。この際、列タイトルは検索範囲に含めません。
=INDEX(B4:E10,
手順２．INDEX関数を第２引数「行番号」を指定
INDEX関数の第２引数「行番号」としてMACH関数を挿入します。MACH関数の第１引数「検索値」は『抽出したい店舗』のセル(G4)を指定し、第2引数「検索条件」は表の「店舗列」のセル範囲(B4:B10)を指定します。
=INDEX(B4:E10,MATCH( G4 ,B4:B10 ,0) ,
手順３．INDEX関数の第３引数「列番号」を指定
続いて、INDEX関数の第３引数「列番号」としてMACH関数を挿入します。MACH関数の第１引数「検索値」は『抽出したい商品』のセル(H4)を指定し、第2引数「検索条件」は表の「商品列」のセル範囲(B3:E3)を指定します。INDEX関数の引数を全て指定し終えたので、”）”で閉じます。
=INDEX(B4:E10, MATCH(G4,B4:B10,0), MATCH(H4,B3:E3,0))
手順４．INDEX関数を確定
Enterキーで関数を確定します。そうすると、「B店のシャンプー」の値段720円が表からクロス抽出されました。
手順５．最終確認(検索値変更)
検索値を「E店の石鹸」に変更しても、値段340円が表から自動でクロス抽出されます。
クロス抽出する方法➁(VLOOKUP関数とMATCH関数)
２つ目のクロス抽出する方法は、VLOOKUP関数の第３引数「列番号」にMATCH関数を利用する方法です。INDEX関数を使った１つ目の方法と同様、『〇〇店の〇〇商品』の値段を自動でクロス抽出する方法を解説します。
VLOOKUP関数の使い方を復習したい方はこちらのリンク記事を参考にしてください。
手順１．VLOOKUP関数の第１引数「検索値」を指定
クロス抽出した結果を表示したいセルに、VLOOKUP関数を挿入します。第１引数「検索値」に探したい店舗の入力セル(G4)を指定します。
=VLOOKUP(G4,
手順２．VLOOKUP関数の第2引数「検索範囲」を指定
VLOOKUP関数の第２引数「検索範囲」として表全体の範囲(B3:B10)を選択します。
=VLOOKUP(G4, B3:E10,
手順３．VLOOKUP関数の第3引数「列番号」を指定
この手順が最も重要です。VLOOKUP関数の第3引数「列番号」にMATCH関数を利用します。
MATCH関数の第１引数「検索値」を探したい商品名の入力セル(H4)を指定し、検索範囲を表の列タイトルのセル範囲(B3:E3)を指定します。第３引数は完全一致の「0」を入力します。
=VLOOKUP(G4, B3:E10, MATCH(H4,B3:E3,0),
手順４．VLOOKUP関数の第４引数「検索方法」を指定
VLOOKUP関数の第4引数「検索方法」を「FALSE」を指定し、Enterキーで関数を確定させます。そうすると、「B店のシャンプー」の値段720円が表からクロス抽出されました。
=VLOOKUP(G4, B3:E10, MATCH(H4,B3:E3,0), FALSE)
手順５．最終確認(検索値変更)
検索値を「E店の石鹸」に変更しても、値段340円が表から自動でクロス抽出されます。
クロス抽出する方法③(HLOOKUP関数とMATCH関数)
3つ目のクロス抽出する方法は、HLOOKUP関数の第３引数「行番号」にMATCH関数を利用する方法です。上の見出しと同様、『〇〇店の〇〇商品』の値段を自動でクロス抽出する方法を解説します。
HLOOKUP関数の使い方を復習したい方はこちらのリンク記事を参考にしてください。
手順１．HLOOKUP関数の第１引数「検索値」を指定
クロス抽出した結果を表示したいセルに、HLOOKUP関数を挿入します。第１引数「検索値」に探したい商品名の入力セル(H4)を指定します。
=HLOOKUP(H4,
手順２．HLOOKUP関数の第2引数「検索範囲」を指定
HLOOKUP関数の第２引数「検索範囲」として表全体の範囲(B3:B10)を選択します。
=HLOOKUP(G4, B3:E10,
手順３．HLOOKUP関数の第3引数「行番号」を指定
この手順が最も重要です。HLOOKUP関数の第3引数「行番号」にMATCH関数を利用します。
MATCH関数の第１引数「検索値」を探したい店舗名の入力セル(G4)を指定し、検索範囲を表の行タイトルのセル範囲(B3:B10)を指定します。第３引数は完全一致の「0」を入力します。
=HLOOKUP(H4, B3:E10, MATCH(G4,B3:B10,0),
手順４．HLOOKUP関数の第４引数「検索方法」を指定
HLOOKUP関数の第4引数「検索方法」を「FALSE」を指定し、Enterキーで関数を確定させます。そうすると、「B店のシャンプー」の値段720円が表からクロス抽出されました。
=HLOOKUP(H4, B3:E10, MATCH(G4,B3:B10,0), FALSE)
手順５．最終確認(検索値変更)
検索値を「E店の石鹸」に変更しても、値段340円が表から自動でクロス抽出されます。
(おまけ)VLOOKUP関数で複数条件を指定する方法
今回紹介した『クロス抽出』では、縦方向と横方向の２つを検索条件に設定しました。
VLOOKUP関数でも複数の条件を指定して検索するテクニックがあります。
VLOOKUP関数の使い方の応用編として『VLOOKUP関数で複数条件で検索する方法』を以下のリンク記事で紹介しています。
(おまけ)HLOOKUP関数で複数条件を指定する方法
今回紹介した『クロス抽出』では、縦方向と横方向の２つを検索条件に設定しました。
HLOOKUP関数でも複数の条件を指定して検索するテクニックがあります。
HLOOKUP関数の使い方の応用編として『HLOOKUP関数で複数条件で検索する方法』を以下のリンク記事で紹介しています。
