1. Home » 
  2. Excelの基本操作の目次 » 
  3. 関数・数式を入力する方法 » 
  4. VLOOKUP関数やINDEX関数(+MATCH関数)でテーブルを参照する数式を作成する

VLOOKUP関数やINDEX関数(+MATCH関数)でテーブルを参照する数式を作成する：Excelの基本操作



VLOOKUP関数でテーブルを利用する INDEX関数でテーブルを利用する
構造化参照を使った数式の作成方法

はじめに　　　　　　　Topへ

VLOOKUP関数でテーブルを利用する　　　　　　　Topへ

  1. VLOOKUP関数を使って、品番を入力すると商品リストから商品名や単価を取り出す、といった使い方です。
    通常の数式では　=VLOOKUP($F4,$B$4:$D$8,MATCH(G$3,$B$3:$D$3,0),FALSE)　となります。
    構文は　=VLOOKUP(検索値,範囲,列番号,[検索方法])　ですので、列番号を上の数式では　MATCH(G$3,$B$3:$D$3,0)　としていますが、　2　としてもOKです。
  2. この数式だと、商品コードの9行目にデータが追加されたら、=VLOOKUP($F4,$B$4:$D$8,MATCH(G$3,$B$3:$D$3,0),FALSE) のままではエラー #N/A となり、検索ができません。
  3. 数式を　=VLOOKUP($F4,$B$4:$D$9,MATCH(G$3,$B$3:$D$3,0),FALSE) と修正する必要があります。
    $B$4:$D$8　→　$B$4:$D$9　と変更しています。

テーブルを使う方法

  1. 商品コードのB3:D8セルを選択して、[ホーム]タブの[テーブルとして書式設定]→「オレンジン、テーブルスタイル」を選択して、テーブルに変換します。
  2. テーブル内のセルを1つ選択して、テーブルツールを表示します。
    テーブルツールの[デザイン]タブのテーブル名で「商品コード」とテーブルの名前を変更しました。
  3. G4セルの数式は　=VLOOKUP($F4,商品コード,MATCH(G$3,商品コード[#見出し],0),FALSE)　となります。
  4. 商品コードにデータが追加されました。すると、テーブルが自動で拡張されます。
    G4セルの数式は　何も修正することなく、正しい答えを返しています。

INDEX関数でテーブルを利用する　　　　　　　Topへ

  1. VLOOKUP関数を使わずに、INDEX関数とMATCH関数に置き換えただけです。
    G3セルの数式は　=INDEX(商品コード2,MATCH($F3,商品コード2[品番],0),MATCH(G$2,商品コード2[#見出し],0))　となります。
    INDEX関数の構文は　=INDEX(配列,行番号,列番号)　となります。
    配列は　テーブル名を指定します。　ここの例では　テーブル名は　商品コード2　としています。
    行番号は　MATCH関数で　「商品コード2」テーブルの「品番」の列で検索しますので、MATCH($F3,商品コード2[品番],0)　となります。
    列番号は　VLOOKUP関数の時と同じで　MATCH(G$2,商品コード2[#見出し],0)　とします。
  2. ところが、G3セルのフィルハンドルをドラッグして、H3セルにコピーすると・・・・エラーになってしまいました。
    原因は　MATCH($F3,商品コード2[品番],0)　のままでないといけないのに、MATCH($F3,商品コード2[商品名],0)　に変化しているためです。
    数式でいうところの絶対参照でないといけないところです。
  3. テーブルを参照するときには絶対参照をどう指定するのか・・・列を範囲で指定すればよいようでした。
    つまり、商品コード2[品番]　を　商品コード2[[品番]:[品番]]　とすることで、コピーしても相対参照みたいに変化せず、絶対参照のような使い方ができました。

構造化参照を使った数式の作成方法　　　　　　　Topへ

  1. 数式バーに数式を入力していく方法で説明します。
    =VLOOKUP($F4,　と入力したら、マウスでデータ範囲 B4:D9　をドラッグして選択します。数式バーには　テーブル名　商品コード　が入力されます。
  2. =VLOOKUP($F4,商品コード,MATCH(G$3,　続きを入力します。
    MATCH関数の検査範囲　B3:D3 をドラッグして選択します。数式に　商品コード[#見出し]　と入力されます。
  3. =VLOOKUP($F4,商品コード,MATCH(G$3,商品コード[#見出し],0),FALSE)　と、残りの部分を入力して、数式は完成です。
Home｜Excelの基本操作の目次｜VLOOKUP関数やINDEX関数(+MATCH関数)でテーブルを参照する数式を作成する