エクセル INDEX関数とMATCH関数で縦横検索

INDEX関数とMATCH関数で縦横検索

VLOOKUP関数HLOOKUP関数を合わせたような関数はあるのでしょうか?

図のように、2次元の表から、縦横から検索して一致するデータを返してくれるような関数です。
図では、縦8行目、横7列目 に一致するセルの値(56)を取得しています。

INDEXとMATCHを合わせた便利なユーザ定義関数の作り方は以下の記事です。

エクセル マクロ/ユーザ定義関数を作る! INDEX&MATCH 超便利!
エクセルではさまざまな関数が用意されていますが、自分でも好きな関数を作ることができます。 それが マクロ画面で作る ユーザ定義関数 です。Function として関数を記述します。 今回はユーザ定義関数を一つ作ってみましょう。 ここで作る LOOKUPS関数は超便利です。 VLOOKUPやHLOOKUPはもう不要ともいえるかもしれません。

INDEX関数

こういった検索を実現するためには、INDEX関数を使うとよいでしょう。

書式

=INDEX(配列, 行番号, 列番号)

図の場合の式は

=INDEX(配列, 行番号, 列番号)=INDEX(C3:L14,C16,C17)

としています。

  • 配列    C3:L14 (図の青い範囲)
  • 行番号   C16 8  直接8と入力してもよい
  • 列番号   C17  7   直接7と入力してもよい

こうすることで、C16 セルにある値(8)と C17 セルにある値(7)を参照して、C3:L14 の範囲からクロスしているセルを探してきてくれます。

応用例

それでは、次の図のようなことは可能でしょうか?
図では、2018葉月 のクロスするところのセルの値(80)を取得しています。(注:この表はサンプルです。意味はありません)

これは INDEX関数 と MATCH 関数と組み合わせることで可能になります。

MATCH関数

MATCH 関数は、探したいデータが表の何行目、何列目にあるのかを調べることができます。

D6 セルに次のように入力します。

=MATCH(D4,B2:B9,0)

図では D4 セルの「Spain」が、範囲 B2:B9 で上から6番目であることを示しています。
(A列の数字は必要ありません)

MATCH関数は横の方向にも有効です。

B3 セルに次のように入力します。

=MATCH(B4,D3:K3,0)

図では、B4の「France」が D3:K3 の範囲で左から何番目にあるかを示しています。(5番目)

(国名の上にある数字は必要ありません)

書式

=MATCH(検索値, 検索範囲, [照合の種類])

  • 検索値
  • 検索範囲
  • 照合の種類 1・・以下,  0・・一致,  -1・・以上 省略で 1

MATCH関数とINDEX関数の組み合わせ

図では、D16 セルに「2018」、E16 セルに 「葉月」と入力することで、範囲の表で「80」という値を得ています。

このとき、E17 セルに次のように入力します。

=INDEX(配列, 行番号, 列番号)=INDEX(B2:L14,MATCH(E16,B2:B14,0),MATCH(D16,B2:L2,0))

  • B2:L14
    全体の検索範囲を指定します。
  • MATCH(E16,B2:B14,0)
    E16 に入力された内容を読み取り、B2:B14の範囲から値の一致するセルの位置を返します。この場合は、9 になります。(注:図の B2 セルを 1 と数えます)
  • MATCH(D16,B2:L2,0)
    D16 に入力された内容を読み取り、B2:L2 の範囲から値の一致するセルの位置を返します。この場合は、8 になります。(注:図の B2 セルを 1 と数えます)

こうすることで、検索範囲から「2018」「葉月」のクロスするセル内容(80)を取得できます。

コメント