VLOOKUP関数は大変便利なExcelの関数なのですが、使い方が複雑で分からないという人が多い関数でもあります。また、Office365からXLOOKUP関数という、VLOOKUP関数よりも便利な関数が使えるようになりました。ここでは、VLOOKUP関数とXLOOKUP関数の比較と活用法を紹介します。
VLOOKUP関数とは
VLOOKUP関数とはテーブルから行ごとに値を取得する時に使用する関数です。例えば、商品コードを元に商品名や単価を取得する際に活用できます。別のシートの表も参照可能なので、見積書と商品マスタを別のシートで管理して、見積書に入力した商品コードを元に商品名や価格を表示するということも可能です。
VLOOKUP関数の使い方
=VLOOKUP(検索値,範囲,列番号,検索方法）
検索値：検索対象のセルを指定
例は商品コードを元に商品情報を取得したいので,「A20」を指定しています。
範囲：検索する範囲を指定
検索したい値が最初の列になるよう検索範囲を指定します。例では、商品コードを元に検索するので、最初の列はC列を指定しています。
ポイント！
数式を他のセルにコピーした際に参照するセルを変化させないようにするため、絶対参照するようにしましょう。例では、「$C$3:$E$6」としています。
※F4キーで切り替えることができます（「$」と入力したのでも問題ありません）
列番号：戻り値の列番号を指定
列番号は検索範囲の最初の列を1と数えます。例では、検索値のC列が1番になるので、商品名はD列の2番目になるため、「2」と指定しています。
検索方法：近似一致「TRUE」、完全一致「FALSE」を指定
例では商品コードに対して商品名を表示したいので、完全一致の「FALSE」を指定しています。商品マスタや、価格などコードで固定される値を検索する場合は「FALSE」を指定するようにしましょう。
VLOOKUP関数の不便なところ
検索値がない場合は「#N/A」と表示される
例では、商品コード「9999」に対して一致する商品コードがないためエラー「#N/A」と表示されています。エラーの場合の返り値を指定する場合は、IFERROR関数と組みああせて下記のように設定すれば”該当なし”などの表示ができます。
「IFERROR（VLOOKUP(B21,$C$3:$E$6,2,FALSE),”該当なし”)」
検索値を左端に設定する必要がある
検索値を左端に設定する必要がるため、検索値よりも左にある列の値を表示することができません。
例では、商品コードの左に製造工場があるのですが、検索値である商品コードよりも左にあるため参照できません。製造工場を表示する場合は、元の検索値を加工し、商品コードの右側に位置するようにしなくてはいけません。
XLOOKUP関数とは
XLOOKUP関数はVLOOKUP関数と同様で、行ごとに情報を検索することができます。VLOOKUP関数ではできなかったこともできるようようになっており、VLOOKUP関数に代わる便利な関数です。
XLOOKUP関数の使い方
＝XLOOKUP(検索値,範囲,返す範囲,見つからない場合の返り値,一致モード,検索モード)
検索値：検索対象のセルを指定
例は商品コードを元に商品情報を取得したいので,「J20」を指定しています。
範囲：検索する範囲を指定
検索したい値の列を指定します。VLOOKUP関数では戻り範囲を含めた範囲指定しなくてはいけないのですが、XLOOKUP関数は検索する範囲のみを指定します。例では、商品コードを元に検索するので、商品コードの列を範囲指定しており、商品名や単価は範囲指定していません。
ポイント！
数式を他のセルにコピーした際に参照するセルを変化させないようにするため、絶対参照するようにしましょう。例では、「$J$3:$J$6」としています。
※F4キーで切り替えることができます（「$」と入力したのでも問題ありません）
返す範囲：戻り値の範囲を指定
戻り値となる範囲を指定します。VLOOKUP関数では検索列を基準に数えた、戻り値の列番号を指定していたのですが、XLOOKUP関数では範囲を指定します。例では商品名を表示したいので、「$K$3:$L$6」を指定しています。
見つからない場合の戻り値（省略可）
該当する値がない場合どう表示するかを指定できます。例では、商品コードに該当するコードがなければ”該当なし”と表示するように指定しています。
一致モード：一致する種類を指定（省略可）
VLOOKUP関数では「TRUE」や「FALSE」で指定していたのですが、XLOOKUP関数ではもっと細かく指定することが可能になりました。完全一致にする場合は「0」を指定してください。省略した場合は「0」が規定の値になります。
検索モード：検索モードを指定（省略可）
先頭から検索するか、末尾から検索するかなどの検索方法を指定できます。省略した場合は「1」の先頭項目から検索されます。
XLOOKUP関数の便利なところ
検索列の左にある列の値も取得可能
例では、商品コードの左にある製造工場の値は、VLOOKUP関数では取得できなかったのですが、XLOOKUP関数なら戻り値として指定できます。
隣接するセルに値を配置することが可能
数式を入力したセルだけでなく、複数の値が戻り値にある場合隣接するセルに値を返すことができます。
例では、商品名と商品単価を表示しているのですが、VLOOKUP関数では商品名は商品名、単価は単価で個別に数式を設定する必要がありました。XLOOKUP関数では戻り値として商品名と単価の列を指定しておけば、隣のセルにある単価にも値を返すことができます。
見つからない場合の値が指定可能
VLOOKUP関数ではエラー表示をしないようにするにはIFERORR関数という別の関数を使用しなくてはいけなかったのですが、XLOOKUP関数ではその必要はありません。
Excelダウンロードはこちら
今回例で使用したExcelを添付します。
実際に動かし設定内容を確認してみて下さい。
