ExcelのVLOOKUP関数とHLOOKUP関数を組み合わせる

この記事では、ExcelのVLOOKUP関数とHLOOKUP関数を組み合わせる方法をご紹介します。

VLOOKUP関数とHLOOKUP関数は、どちらもデータ検索に便利な関数です。2つの数式を組み合わせると列番号を修正する手間を無くすことができます。

以下では、指定した行番号と列番号から交差する位置にあるセルの値を抽出する方法も合わせてご紹介します。

VLOOKUP関数とHLOOKUP関数とは？

VLOOKUP関数とHLOOKUP関数の違いは、検索方向の違いにあります。

VLOOKUP関数は、検索値を範囲の左端の列で縦方向に検索します。検索値に一致した値と同じ行から指定した列数分を右に移動した値を抽出します。

書式は「=VLOOKUP(検索値,範囲,列番号,[検索の型])」です。

VLOOKUP関数の引数や使い方の詳細は、以下の記事でご紹介しています。

HLOOKUP関数は、検索値を範囲の1行で横方向に検索します。検索値に一致した値と同じ列から指定した行数分を下に移動した値を抽出します。

書式は=HLOOKUP(検索値,範囲,行番号,[検索の型])」です。

HLOOKUP関数の引数や使い方の詳細は、以下の記事でご紹介しています。

VLOOKUP関数とHLOOKUP関数は単独で使用した場合でも、指定した検索値と列番号または行番号からデータを取得することができます。

今回は、VLOOKUP関数の引数にHLOOKUP関数を組み合わせて、条件に応じて抽出する列を可変させる方法をご紹介します。

上記画像のように、行と列に見出しがあるデータを用意します。

例ではB10セルに入力した「料金区分」と、C10セルに入力した「会員区分」に応じた「料金」をD10セルに表示していきます。

以下で、手順をご説明します。

B2セルに「2」を入力し、Ctrlを押しながらフィルハンドルをドラッグします。

2行目は数式に使う列番号として使用します。

範囲選択をする際、1列目は料金区分の見出しが入力されているので、列番号は「2」から数えます。

2行目に列番号に指定する数字が表示されました。

B10セルに料金区分（例：高校生）、C10セルに会員区分（例：ゴールド）を入力します。

「料金区分」や「会員区分」のように選択肢が複数ある場合は、選択肢をリスト化しておくと便利です。

D10セルに「=VLOOKUP(B10,A3:D7,HLOOKUP(C10,B1:D2,2,FALSE),FALSE)」を入力して、Enterを押します。

上記の数式は、HLOOKUP関数の検索値に紐づく数字をVLOOKUP関数の列番号に指定することで、会員区分に応じて抽出する列番号が可変します。

VLOOKUP関数で取得した「料金区分」高校生の行と、HLOOKUP関数で取得した「会員区分」ゴールドの列が交差したセルの値を返します。

D10セルに「料金区分」と「会員区分」に応じた「料金」￥1,400が表示されました。

例えば「2学期中間試験」の「英語」の「点数」を抽出したい場合、上記の画像のように行番号や列番号から、縦横が交わるセルの値を抽出する「クロス抽出」という方法があります。

以下では、INDEX関数とMATCH関数を組み合わせてクロス抽出する方法をご紹介します。

上記のような、行と列に見出しがある表を用意します。

A8セルとB8セルに抽出したい試験（例：2学期中間試験）と教科（例：英語）を入力します。

C8セルに「=INDEX(B2:F5,MATCH(A8,A2:A5,0),MATCH(B8,B1:F1,0))」を入力してEnterを押します。

上記の数式は、1つ目のMATCH関数で行番号を取得して、2つ目のMATCH関数で列番号を取得します。それぞれの行番号と列番号が交差したセルの値をINDEX関数で返します。

INDEX関数は、参照した範囲から指定された行番号と列番号が交差する位置にあるセルまたはセルの参照を返します。今回使用する書式は「=INDEX(参照,行番号,[列番号],[領域番号])」です。

INDEX関数の引数や使い方の詳細は、以下の記事でご紹介しています。

MATCH関数は、指定された照合の種類に当てはまる検索値と一致するセルが何番目にあるのかを相対的な位置に返します。書式は「=MATCH(検索値,検索範囲,[照合の種類])」です。

MATCH関数の引数や使い方の詳細は、以下の記事でご紹介しています。

C8セルに「試験」2学期中間試験と「教科」英語が交差した「点数」42を抽出することができました。