マスタ内容を表示する関数XLOOKUP、VLOOKUP【Excel】

ExcelのXLOOKUP関数（VLOOKUP・HLOOKUP）はマスタ内を検索し該当したデータの行や列から内容を取り出して表示させる関数です。関数の使い方やマスタについて解説していきます。VLOOKUP関数ではIFERROR関数との組み合わせでエラーコードにも対応していきます。

XLOOKUP関数（VLOOKUP関数）でできること

例を見てみます。
青枠のセルはXLOOKUP関数（VLOOKUP関数）が設定されています。
これにより、赤枠の【B2】のコースNo.に数字を入力すると、青枠の「コース名」「開催曜日」「開催時間」が自動で表示されます。

※XLOOKUP関数はExcel2021のバージョン以降とMicrosoft365で使えます。
使用できない場合はVLOOKUP関数やHLOOKUP関数で行います。

また、これらの関数を使うには「マスタ」が必要になります。

マスタ

XLOOKUP関数やVLOOKUP関数、HLOOKUP関数を使うには「マスタ」が必要になります。

マスタ（master data）
基本情報の入ったデータリスト（データベース）
※マスターというと、主人・親方という意味のほうが強い印象になります。コンピュータ用語ではマスタという言い方をするのが一般的です。

ここでは、コースナンバーに対応する内容（タイトル・曜日・時間）をまとめたマスタを「Sheet2」に用意しました。

XLOOKUP関数

XLOOKUP関数は、マスタ内を検索し該当したデータに対応する内容を取り出して表示させる関数です。見つからない場合は指定した内容を表示させます。

※XLOOKUP関数はExcel2021のバージョン以降とMicrosoft365で使えます。
使用できない場合はVLOOKUP関数やHLOOKUP関数で行います。

XLOOKUP関数の作り方をみていきます。

XLOOKUP関数
=XLOOKUP（検索値,検索範囲,戻り範囲,［見つからない場合］,［一致モード］,［検索モード］）
検索値：検索の元となる内容（コードや番号）
検索範囲：マスタの検索値が含まれる列（行）を範囲選択
戻り範囲：マスタの抜き出して表示させたい列（行）を範囲選択
［見つからない場合］：見つからない場合に表示させたい文字列や計算式
［一致モード］：検索値の近似値でOKか、完全に一致したものだけを表示するのかの選択
　・0：完全一致。 見つからない場合は#N/A が返される（省略するとこれになる）
　・-1：見つからない場合、その数を超えない近似値で返す（VLOOKUP関数の近似値と同様）
　・1：見つからない場合、その数を超えた近似値で返す
　・2：*、?、および 〜 が特別な意味を持つワイルドカードの一致
［検索モード］：
　・1：先頭の項目から検索を実行（省略するとこれになる）
　・-1：末尾の項目から逆方向に検索を実行
　・2：昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行※並べ替え必須
　・-2：降順で並べ替えられた検索範囲を使用してバイナリ検索を実行※並べ替え必須
　※バイナリサーチについては下で解説します
　※関数を直接入力していくと選択肢が表示されるのでTabキーで指定できます

VLOOKUP関数（HLOOKUP関数）と大きく変わった点
1．マスタ全体を範囲選択せず必要な行（列）だけを選択するようになったこと
2．見つからない場合の指定ができること（以前はIFERROR関数などと組み合わせていた）
3．デフォルトが完全一致に変わったこと
4．近似値の場合、超えた数の指定もできるようになったこと
5．マスタの並びが降順でも可能になったり検索が末尾からできたり自由度が広がったこと

例を見てみます。
【A3】にコードを入力すると【B3】に種類、【C3】に単価が表示されるようにします。
ただし、見つからない場合は非表示とします。
※【E3】は =C3*D3 の計算式が入っています。

【B3】=XLOOKUP(A3,$G$3:$G$7,$H$3:$H$7,””)
【A3】のコード（102）をマスタ【G3:G7】から検索し【H3:H7】の対応する内容を表示します。
見つからない場合は非表示 “”（半角ダブルクォーテーション2つ入力）にします。
結果は「りんご」と表示されました。

【C3】=XLOOKUP(A3,$G$3:$G$7,$J$3:$J$7,””)
【A3】のコード（102）をマスタ【G3:G7】から検索し【J3:J7】の対応する内容を表示します。
見つからない場合は非表示 “”（半角ダブルクォーテーション2つ入力）にします。
結果は「120」と表示されました。

※ここでは計算式をコピーした際にマスタ範囲がずれないように絶対参照しています。

VLOOKUP関数

VLOOKUP関数は、マスタ内を検索し該当したデータのレコード（行）から内容を取り出して表示させる関数です。

VLOOKUP関数の頭文字「Ｖ」はvertical（垂直）を表し「LOOK UP」は検索するという意味でマスタを垂直方向に検索するということになります。

VLOOKUP関数の作り方をみていきます。

VLOOKUP関数
=VLOOKUP（検索値,範囲,列番号,［検索方法］）
検索値：検索の元となる内容（コードや番号）
範囲：マスタ全体（見出しの行は外してOK）※検索値の含まれる列から範囲選択
列番号：上記で選択した「範囲」の左から何列目を表示するのか数字で指定
［検索方法］：検索値の近似値でOKか、完全に一致したものだけを表示するのかの選択
　・近似値：省略、または「1」または「TRUE」と入力
　・完全一致：「0」または「FALSE」と入力
　※関数を直接入力していくと選択肢が表示されるのでTabキーで指定できます
　※検索方法について後ほど詳しく解説します

例を見てみます。
【A3】にコードを入力すると【B3】に種類、【C3】に単価が表示されるようにします。
※ 品番を検索するため完全一致したものを表示させます。
※【E3】は =C3*D3 の計算式が入っています。

【B3】=VLOOKUP(A3,$G$3:$J$7,2,0)
【A3】のコード（102）をマスタ【G3:J7】から検索し、見つけたらその２列目の内容を表示します。完全一致したものを表示させるので 0 と入力します。
結果は「りんご」と表示されました。

【C3】=VLOOKUP(A3,$G$3:$J$7,4,0)
【A3】のコード（102）をマスタ【G3:J7】から検索し、見つけたらその 4 列目の内容を表示します。完全一致したものを表示させるので 0 と入力します。
結果は「120」と表示されました。

※ここでは計算式をコピーした際にマスタ範囲がずれないように絶対参照しています。

HLOOKUP関数

マスタを横方向に検索したい場合はHLOOKUP関数を使います。
HLOOKUP関数の頭文字「H」はhorizontal（水平）を表し「LOOK UP」は検索するという意味でマスタを水平方向に検索するということになります。

HLOOKUP関数は、マスタ内を検索し該当したデータの列から内容を取り出して表示させる関数です。

HLOOKUP関数の作り方をみていきます。

HLOOKUP関数
=HLOOKUP（検索値,範囲,行番号,［検索方法］）
検索値：検索の元となる内容（コードや番号）
範囲：マスタ全体（見出しの行は外してOK）※検索値の含まれる行から範囲選択
行番号：上記で選択した「範囲」の上から何行目を表示するのか数字で指定
［検索方法］：検索値の近似値でOKか、完全に一致したものだけを表示するのかの選択
　・近似値：省略、または「1」または「TRUE」と入力
　・完全一致：「0」または「FALSE」と入力
　※関数を直接入力していくと選択肢が表示されるのでTabキーで指定できます
　※検索方法について後ほど詳しく解説します

例を見てみます。
【A3】にコードを入力すると【B3】に種類、【C3】に単価が表示されるようにします。
※ 品番を検索するため完全一致したものを表示させます。
※【E5】は =C3*D3 の計算式が入っています。

【B3】=HLOOKUP(A3,$H$2:$L$5,2,0)
【A3】のコード（102）をマスタ【H2:L5】から検索し、見つけたらその２行目の内容を表示します。完全一致したものを表示させるので 0 と入力します。
結果は「りんご」と表示されました。

【C3】=HLOOKUP(A3,$H$2:$L$5,4,0)
【A3】のコード（102）をマスタ【H2:L5】から検索し、見つけたらその 4 行目の内容を表示します。完全一致したものを表示させるので 0 と入力します。
結果は「120」と表示されました。

※ここでは計算式をコピーした際にマスタ範囲がずれないように絶対参照しています。

VLOOKUP関数（HLOOKUP関数）の注意点

VLOOKUP関数を使うのか、HLOOKUP関数を使うのかはマスタの構造により決まるということがわかりました。

1．VLOOKUP関数（HLOOKUP関数）はマスタの全てを検索しない

VLOOKUP関数の場合、実際に検索しているのは範囲選択したマスタの中の一番左の列だけ、
HLOOKUP関数の場合、実際に検索しているのは範囲選択したマスタの中の一番上の行だけです。

VLOOKUP関数の場合、コードが2列目にあるマスタなどは範囲選択する際に2列目から選択しなければなりません。
指定したマスタ範囲の一番左の列に一致するものがないと、検索した結果データなしということになりエラーコードが表示されてしまいます。

2．検索方法「近似値」と「完全一致」とは

※VLOOKUP関数、HLOOKUP関数共通なのでVLOOKUP関数として説明していきます。

検索方法とは、マスタ内を検索する際に近似値なのか、完全に一致したものだけを表示するのかの選択です。

検索方法
・近似値：省略、または「1」または「TRUE」と入力
　完全に一致するデータがない場合、その数を超えない近似値で返します
・完全一致：「0」または「FALSE」と入力
　完全に一致するデータがない場合、エラー（＃N/A）を返します
※関数を直接入力していくと選択肢が表示されるのでTabキーで指定できます

品番から探し出す場合は「近似値」にすると、ない品番を誤って入力しても近い品番の内容が表示されてしまいます。
つまり、間違った情報を表示することになってしまいます。
「完全一致」であれば、ない品番が入力されるとエラーコードが表示されるので間違った情報は表示されません。
品番などの場合は「完全一致」を選択しましょう。
※わからないからと省略してしまうと「近似値」で表示されてしまいます。

ではどんな時に「近似値」を指定するのでしょうか。

3．近似値を指定する例

近似値を使う例を見ていきます。

【B3】に点数を入力すると【C3】にランクが表示されるようにします。

【C3】=VLOOKUP(B3,$E$3:$F$7,2)　※近似値なので［検索方法］は省略しています。
【B3】の点数をマスタ【E3:F7】から検索します。
「76」はマスタにありませんが近似値の指定なので「70」のある２列目の「B」が表示されます。

これを「完全一致」で行うと「76」は見つからないのでエラーという結果になります。

VLOOKUP関数（HLOOKUP）では検索値を超えない範囲の近似値になります。
それについては次で説明します。

4．近似値のマスタ作成時の注意点（バイナリサーチ）

近似値にする場合、マスタを作る際は検索値のデータを昇順にしておく必要があります。

「近似値」を指定すると、バイナリサーチ（2分探索）という方法でマスタを検索します。
簡単に説明すると、上端と下端のデータを足して半分にした数値と検索値を比較して消去・・・
残り半分の上端と下端のデータを足して半分にした数値と検索値を比較して消去・・・
という作業の繰り返しです。
これにはデータを昇順（小さい順）にしておくことが必須となります（※VLOOKUP関数・HLOOKUP関数の場合です）。

自分で見る時は、上から「76」を探し、「80」は超えてしまっているので一つ戻って「70」の場所の「B」というように見ていけば結果としては同じ事になります。
つまり、検索値を超えない範囲の近似値です。

IFERROR関数でエラー表示回避

VLOOKUP関数などを設定すると、検索値が空の場合エラーコードが表示されます。

IFERROR関数はエラーが表示される計算結果となる場合、代わりの計算式または値を表示させる関数です。

IFERROR関数の作り方をみていきます。

IFERROR関数
=IFERROR(値,エラーの場合の値）
値：計算式（値とはその計算式の結果のこと）
エラーの場合の値：「値」の結果がエラーとなる場合、エラーコードの代わりに表示させたい値もしくは計算式
※単にエラーコードを表示させたくない場合は””（半角ダブルクォーテーション２つ入力）

例を見てみます。
【A3】にコードを入力すると【B3】に種類を表示、エラーになる場合は非表示になるようにします。

【B3】=IFERROR(VLOOKUP(A3,$G$3:$J$7,2,0),””)
VLOOKUP関数を使って種類を表示しますが、エラーコードが表示される結果になる場合は非表示にします。

今回は非表示となるように””（半角ダブルクォーテーション２つ連続入力）としました。
「該当なし」と表示させたい場合は ”該当なし” と半角ダブルコーテーションで括ります。
ハイフンを表示する場合は ”－” とします。
※ダブルクォーテーションは半角入力です。
※計算式や数値を入れる場合はダブルクォーテーションは不要です。

IFERROR関数を含めたものをコピーすれば、計算式が入っていてもエラーコードは表示されなくなります。

アイキャッチで使用したフリー素材はこちらです。