「VLOOKUP関数」と「INDEX関数」は同じ目的で使われる関数です
２つの関数は似たような使われ方をしますが、それぞれに特徴があるので使い分ける必要があります。
- 「VLOOKUP関数」と「INDEX関数」は同じ目的で使われる関数です
VLOOKUP関数とINDEX関数の違い
VLOOKUP関数とINDEX関数は「検索／行列」に属する関数ですが、基本的には違う性質の関数です。
- VLOOKUP関数には検索機能がありますが、INDEX関数には検索機能はありません。
- VLOOKUP関数は行方向（HLOOKUP関数は列方向）にしか検索機能が無いので、列方向の検索（クロス集計）をするためには、MACH関数を組み合わせる必要があります。
- INDEX関数はMATCH関数と組み合わせることで行方向・列方向検索（クロス集計）の機能を持たせることができます。
VLOOKUP関数とINDEX関数の相違点をまとめると
- VLOOKUP関数は指定された範囲の一番左の列を検索し、一致する行のセルの値を返します。
- INDEX関数は指定した範囲で指定した行、列の交点のセルの値を返すだけで検索の機能は無い。
- INDEX関数はMATCH関数と組み合わせることによって、検索の機能を持たせることができます。
- VLOOKUP関数の場合は検索対象が一番左の列に固定されるので、それ以外の列を検索したい場合は使うことができません。
- INDEX関数とMATCH関数を組み合わせれば、「行」と「列」を同時に検索対象とするクロス集計が簡単にできます。（※ VLOOKUP関数でも可能)。
クロス集計で「会員のランク」に応じた「時間帯別の利用料金」を調べる
フィットネスクラブで「会員のランク」に応じて、時間帯別に異なる利用料金を調べる表を作成します。
このように、２つの条件に合致するものをもとめるクロス集計をする場合、VLOOKUP関数では数式が複雑になってしまいます。
「利用時間」と「会員種別」の２つの条件に合致する利用料金を求める
横方向と縦方向の２つの条件で検索して、両方の条件に一致する部分が求める「値」になるように数式を作成する。
「ゴールド会員」が「早朝時間帯」に利用すると５００円になる
数式の作成方法は複数あります
- VLOOKUP関数とMACH関数を組み合わせてクロス集計する
- INDEX関数でクロス集計する
１．VLOOKUP関数とMACH関数を組み合わせてクロス集計する
VLOOKUP関数で行方向を検索し、MATCH関数を使って列方向を検索することで２つの条件（クロス集計）します。
- MATCH関数で「会員種類が何列目」かを求める
- VLOOKUP関数の「列番号」にMATCH関数で求めた「何列目になるかの値」を入れる
- 検索方法を「FALSE」として近似値を求める
- 注意点は元になる表の「利用時間」のフィールド（項目）が左端（１列目）にする必要があることです。（※ VLOOKUP関数は範囲の左端が「１」になる）
「料金表」に名前を設定します
検索範囲にする表の範囲に「名前」を付けることで、数式が分かり易くなります。
- 検索対象にする、表の料金表の範囲を選択します
- 名前ボックスに「料金表」と入力します
検索条件の各部に「名前」を付けます
範囲に一つ一つ名前を付けることもできますが、「選択範囲から作成」の機能を使えば、表の各部に一度で名前を付けることができます。
- 検索用の範囲を選択します
- 「数式タブ」を選択します
- 「選択範囲から作成」をクリック
- 選択範囲から名前...ダイアログで「上端行」にチェックを入れる
- 「OK」をクリック
各部に「名前」が付きました
検索部分に「リスト入力」を設定します
「検索値」を入力する部分に「リスト入力」を設定することで、検索値を入力する手間を省けると同時に、入力ミスにより正しく検索されないことを防ぐことができます。
- リスト入力を設定する部分を選択します
- 「データタブ」を選択
- 入力の種類を「リスト入力」にする
- 元の値に「利用時間」の範囲を設定
「リスト入力」が設定されました
「利用時間」の部分に、リスト入力が設定されました、「会員種類」の部分も同様の方法で設定します。
VLOOKUP関数とMACH関数を使って数式を入力します
VLOOKUP関数で行方向の「利用時間」を検索し、MATCH関数で列方向の「会員種類」を検索します。
関数入力のダイアログで関数を入力します
- 数式を入力するセルを選択します
- 数式バーの「 fx 」をクリックして、「関数の挿入」ダイアログを開きます
- 関数の分類で「検索／行列」を選択します
- 関数名のボックスで「VLOOKUP関数」を選択します
関数入力のダイアログで数式を設定します
「検索値」のボックスを設定します
関数の「引数」に範囲のつけた名前を使う場合、直接「名前」を入力しても構いませんが、「F3キー」を入力すると表示される「名前の貼り付けダイアログ」から選択することで簡単に間違いなく入力することができます。
- 「検索値」のボックスで「 F３キー」を押します
- 名前の貼り付けダイアログで、名前を付けた範囲「利用時間」を選択します
※ ボックスで「 F３キー」を押せば、設定した「名前」の一覧から選択できます
「範囲」のボックスを設定します
- 範囲のボックスで「 F３キー 」を押します
- 名前の貼り付けボックスで「料金表」を選択します
VLOOKUP関数の「列番号」のボックスを設定します
VLOOKUP関数の「列番号」はMATCH関数で検索して求めます。
数式はVLOOKUP関数の「列番号」にMATCH関数をネスト（入れ子）した構造になります。
入力の難しいネスト（入れ子）ですが、関数の挿入ダイアログを使うことで分かり易くなります。
- 列番号のボックスにカーソルを置きます
- 名前ボックスで「MATCH関数」を選択します（※ 名前ボックスを使うことで、ネスト（入れ子）ができます）
- 「MATCH関数」のダイアログが開くので、「検索値」のボックスで「 F３キー 」を押します
- 名前の貼り付けのボックスで「会員種別」を選択します
※ 「名前ボックス」にMATCH関数がない場合は「その他の関数」で探します
MATCH関数を設定します
- 「検査範囲」のボックスを選択します
- 「会員種類」の範囲を選択します
- 照合の種類を「０」にします
※ 会員種類の「範囲」に注意してください
VLOOKUP関数に戻って設定します
ネスト（入れ子）になっている関数から、もとの関数に戻るには「数式バー」の「関数の文字列」のどこか1カ所をクリックすると、その関数の「関数の挿入」ダイアログに切り替わります。
- 「数式バー」の「VLOOKUP関数」の部分をクリックします
- 「VLOOKUP関数」のダイアログが開きます
- 検索方法を「FALSE」にします
※ 数式バーの「開きたい関数」の部分をクリックすると、そのダイアログが開きます
「利用時間」と「会員種類」を選択すると「金額」が表示されます
INDEX関数で「クロス集計」をする方法
- 「配列」として料金表全体を指定
- 「行番号」をMATCH関数で求める
- 「列番号」をMATCH関数で求める
- 「行」と「列」はMATCH関数で直接指定するので、並びを気にする必要がない
INDEX関数とMACH関数を使って数式を入力します
- 数式を入力するセルを選択します
- 関数の分類で「検索／行列」を選択します
- 関数名から「INDEX」を選択します
INDEX関数の設定
- 「配列」のボックスで「 F３キー 」を押します
- 名前の貼り付けボックスで「料金表」を選択します
MATCH関数を使って「行番号」と「列番号」を求めます
「行番号」を設定します
MACH関数のダイアログで、行番号を設定します
- 検索値のボックスに、「 F３キー」を使って「利用時間」を入力
- 検査範囲に「利用時間の範囲」を選択します（※ ２行目から選択）
「列番号」を設定
INDEX関数の「関数の挿入」ダイアログに戻って「列番号」の設定をします。
- 数式バーの「INDEX関数」の部分をクリックします
- 「INDEX関数」のダイアログが開くので「列番号」の部分を選択し、名前ボックスで「MACH関数」をクリックし、MATCH関数のダイアログを開きます
「列番号」を入力
- 検査値のボックスに「 F３キー」を使って「会員種類」を入力します
- 検査範囲に「会員種類」の範囲を選択します（※ 列Bから選択する）
- 照合の種類を「０」に設定
「時間」に対応して、A～Fの「時間帯」を表示させる場合
VLOOKUP関数を使う方法
- 検索対象の「時間」を左端に配置します（※ 必須）
- 「 =VLOOKUP([@時間],テーブル1,2,TRUE) 」
INDEX関数を使う方法
- 検索対象の「時間」が左端に無い場合でも対応できる
- MATCH関数で「時間」を検索して、INDEX関数が必要とする「行」の値を求める
- 「 =INDEX(テーブル2,MATCH([@時間],テーブル2[時間],1),1) 」
※「クロス集計(抽出)」にはINDEX関数が適している
VLOOKUP関数の場合
- 数式は「＝VLOOKUP(検索値 , 範囲 , 列番号 , 検索の型）」となる
- 「列番号」をMATCH関数で求める
- 数式は「 MATCH（検索値、検査範囲、照合の種類）」となる
INDEX関数の場合
- 数式は「 ＝INDEX(検索範囲 , MATCH(行) , MATCH(列) , 照合の種類 )) 」となる
- ２つのMATCH関数で直接「行」と「列」を指定するので検索範囲の並びを気にしなくてもよい
INDEX関数とVLOOKUP関数は状況によって使い分けます
- 「クロス集計」をする場合にはINDEX関数が適している。
- 「リスト入力」など、単純な参照をする場合にはVLOOKUP関数が使いやすい。
通常、表引き（検索）というとVLOOKUP関数を想像しますが、縦・横の条件で表引きするクロス集計をする場合はINDEX関数が適しています。
VLOOKUP関数は「行」の検索対象にするフィールド（項目）を、表の左端に置くことで「行の指定」を省略することができますが、表がそのような構造（並び）になっていない場合は使用できません。
INDEX関数は「行」と「列」の検索対象を直接指定するので、フィールド（項目）の並びを気にする必要がないので、縦・横の表引き（クロス集計）をする場合はINDEX関数を使うことをお勧めします。
- 「VLOOKUP関数」と「INDEX関数」は同じ目的で使われる関数です
