XLOOKUP関数の概要
XLOOKUP関数は、VLOOKUP関数の不便な点を改良した、現時点で最新のLOOKUP系の関数です。
この機会に、VLOOKUP関数からは卒業し、XLOOKUP関数を使えるようになりましょう。
参考情報 ExcelVBAで使うXLOOKUP関数はこちら→【VBA】15.2 XLOOKUP関数を使ってみよう！
動画を見て、XLOOKUP関数を視覚的にマスターしよう！
百聞は一見に如かず、習うより慣れよ、です。
まずは動画を見て、XLOOKUP関数の設定方法やどのような機能なのかを理解してみましょう。
この動画では、XLOOKUP関数を用いて、以下の処理を実施するものです。
| ウィンドウ名 | ウィンドウの役割 |
|---|---|
| 左のウィンドウ | A列のメールアドレスをキーにして、右のウィンドウよりXLOOKUP関数で氏名を取得し、B列に氏名をセットします。 |
| 右のウィンドウ | 氏名やメールアドレスなどの情報を持っている社員一覧シートです。 |
この動画では、以下の設定を実行しています。
- B2セルをクリックし、メニューの「数式」→リボンの「関数の挿入」をクリックし、関数を選択するダイアログ画面で関数名の領域から「XLOOKUP」を選択します。
- 検索値として、左のExcelシートのA2セル（メールアドレス）を設定します。
- 検索範囲として、右のExcelシートのD列（メールアドレス）を設定します。
- 戻り範囲として、右のExcelシートのB列（氏名）を設定します。
- 見つからない場合の戻り値として、「“”（半角ダブルクォーテーションを連続で2つ）」を設定します。
- 上記の1.～4.で、B2セルへのXLOOKUP関数の設定は完了しました。
- B2セルに設定したXLOOKUP関数の設定をB3セル～B5セルにコピーします。
XLOOKUP関数の構文
XLOOKUP関数の構文は、以下の通りです。
- =XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード)
| 引数名 | 必須/ 任意 | 解説 |
|---|---|---|
| 検索値 | 必須 | XLOOKUP関数で検索する値を指定します。 |
| 検索範囲 | 必須 | 検索値で検索する範囲を列の一部、または列全体で指定します。 |
| 戻り範囲 | 必須 | 検索範囲でヒットした際の戻り値を取得する範囲を列の一部、または列全体で指定します。 |
見つからない場合 | 任意 | 検索値を検索範囲で検索した場合に、見つからない場合の戻り値を設定できます。 任意項目のため、設定していない場合は「#N/A」エラーが返されますので、何らかの設定を行うことを推奨します。 戻り値を文字列で設定する際は、戻り値の前後を「”（半角ダブルクォーテーション）」で囲みます。（ex.”検出できませんでした”） 戻り値を空欄のままとする場合は、「“”（半角ダブルクォーテーションを連続で2つ）」をセットします。 |
| 一致モード | 任意 | 検索する際の検索条件が、完全一致か、近似値かを指定します。 設定していない場合は完全一致となります。 |
| 検索モード | 任意 | 検索をする方法を指定します。 設定していない場合は、先頭の項目から検索を実行します。 設定を最適化した場合、検索が高速化する場合があります。 |
上記の引数で、「2.検索範囲」と「3.戻り範囲」で列の一部を指定した場合、両方の行数が一致していないと、「#VALUE!」エラーとなります。
これを避けるため、上記の2つの引数は、列全体を指定することを推奨します。
XLOOKUP関数は、以下の値を返します。
- 検索値が検索範囲から見つかった場合：検索範囲で見つかった行の戻り範囲の値を返す。
- 検索値が検索範囲から見つからなかった場合：上記の「4.見つからない場合」の値を返します。
VLOOKUP関数からの改良点
XLOOKUP関数では、VLOOKUP関数の以下の点が改良されました。
この改良により、VLOOKUP関数での以下のような実装は不要となります。
- 検索値が検索範囲から見つからなかった場合に、戻り値が「#N/A」エラーと帰ってくる場合のエラー処理
- 「3.戻り範囲」が「2.検索範囲」の同一シートの右側に無かった場合の、このためだけの列のコピー処理
[emalabo式] ワークシート上の数式で使用した場合とExcelVBAで使用した場合の相違点
原因は不明なのですが（もしかしてバグ？）、XLOOKUP関数をワークシート上の数式で使用した場合とExcelVBAで使用した場合で、以下の違いあることが判明しました。
| XLOOKUP関数の使用条件 | XLOOKUP関数の戻り値 |
|---|---|
| ワークシート上の数式で使用した場合で、検索値が空欄だった場合 | 数式のあるセルに、数字の0が出力される |
| ExcelVBAで使用した場合、検索値が空欄だった場合 | 空欄が戻り値となる（想定通り） |
【ワークシート上の数式で使用した場合のイメージ】
【ExcelVBAで使用した場合のイメージ】
上記のイメージの、B3セルがワークシート上の数式でXLOOKUP関数を使用した場合の結果です。
実際のB3セルの数式は以下の通りです。
ワークシート上の数式で使用した場合で、見つからない場合の戻り値をどのように設定しても、上記の結果になるようです。
=XLOOKUP(A3,D:D,E:E,"")これを解決する（ExcelVBAの結果のように、何も出力しないようにする）ためにはいくつかの方法があるようですが、最もお手軽なのは、数式の最後に「&””」というおまじないを付けると解決します。
=XLOOKUP(A3,D:D,E:E,"")&""