XLOOKUP関数の使い方！VLOOKUPとの違いと#N/Aの対処法

別の表からデータを自動で引っ張ってきたいときはないでしょうか。

けど、そんな中で悩むことは、

・VLOOKUPの列番号を数えるのが面倒
・左側の列を検索したいのにVLOOKUPではできない
・#N/Aエラーが出てしまって困る

ですよね。

今回はそんなお悩みを解決する

・XLOOKUP関数の使い方と基本の数式
・VLOOKUPとの違い
・#N/Aエラーの対処法

についてまとめます！

完成イメージ

商品コードを入力するだけで、商品名と単価が自動で表示される表を作ります。

今回のゴールは
・商品マスタからXLOOKUPでデータを引く
として、VLOOKUPよりさらに柔軟な操作ができることをめざします。

例えば、戻り範囲を自由に指定できるので、検索列より左側の値も引っ張れます。
見つからない場合のメッセージも設定できるため、#N/Aをスマートに防げます。

なお、VLOOKUPとの違いの比較表と、うまくいかないときのトラブルシュートもあわせてご紹介しますね。

早速実装して試してみましょう！

STEP1. 商品マスタを用意する

まずは元データとなる商品マスタを用意します。

1行目が見出しで、A列に商品コード、B列に商品名、C列に単価を入力します。

商品コードは MA-001 のような形式にしています。
これをもとに、別の場所からデータを引っ張っていきます。

STEP2. XLOOKUPの数式を入力する

商品マスタの少し右側に、検索用のテーブルを作ります。

E1セルに「商品コード（検索）」、F1セルに「商品名」、G1セルに「単価」と入力します。

次に、F2セルに次の数式を入力しましょう。

=XLOOKUP(E2, $A$2:$A$11, $B$2:$B$11, "見つかりません")

G2セルにも同様に入力します。

=XLOOKUP(E2, $A$2:$A$11, $C$2:$C$11, "見つかりません")

POINT

検索範囲と戻り範囲の行数を揃えるのがポイントです。
ここではどちらも $A$2:$A$11（検索）と $B$2:$B$11（戻り）で、同じ10行になっています。

行数を固定するために $（絶対参照） を使っています。
これにより、あとでオートフィルしても範囲がずれません。

数式の入力画面を確認する

実際に数式バーで =XLOOKUP( と入力すると、引数のヒントが表示されます。

第1引数が 検索値（今回はE2）、第2引数が 検索範囲（$A$2:$A$11）、
第3引数が 戻り範囲（$B$2:$B$11）です。

第4引数には 見つからない場合の表示 を指定できます。
ここでは “見つかりません” と入力しているので、
該当する商品コードがない場合は「見つかりません」と表示されます。

#N/Aエラーが出なくなります。

STEP3. 商品コードを入力して結果を確認する

E2セルに商品コードを入力してみましょう。

ここでは MA-005（プリンタ）を入力します。

すると、F2セルに「プリンタ」、G2セルに「29,800」が自動で表示されます。

商品コードを変えるだけで、商品名と単価が一瞬で切り替わります。
カンタンですね。

同じ数式を下方向にもコピーしたいときは、オートフィル（セルの右下をドラッグ）すればOKです。
そのときに $（絶対参照） が効いていないと範囲がズレてしまうので注意しましょう。

VLOOKUPとの違いを比較する

XLOOKUPはExcel 2021以降で使える新しい関数です。

従来のVLOOKUPと比べて、いくつかの大きな違いがあります。

項目	VLOOKUP	XLOOKUP
戻り範囲の指定	列番号（何列目か数える）	戻り範囲を直接指定（自由）
左方向の検索	不可（検索列が左端固定）	可（戻り範囲が検索範囲の左でもOK）
見つからない場合	#N/Aしか返せない	「見つかりません」など任意の文字を指定できる
検索方向	上から下のみ	上から下／下から上を選べる
近似一致	TRUE/FALSEのみ	-1（完全一致なしの次の値）/ 1（完全一致なしの前の値）/ 0（完全一致）/ 2（ワイルドカード）
配列対応	単一値のみ	配列を返せる（複数の値を一度に取得）

POINT

VLOOKUPは「検索列が左端」「戻り列を番号で指定」という制約があります。
XLOOKUPなら検索範囲と戻り範囲を独立して指定できるので、左方向の検索もカンタンです。
また、#N/Aを「見つかりません」などに書き換えられるのも便利ですね。

#N/Aエラーのトラブルシュート

XLOOKUPを使っていて、うまく結果が表示されないことがあります。

よくある原因と対処法をまとめました。

症状	原因	対処法
#N/A が表示される	検索値が検索範囲に見つからない	第4引数に「”見つかりません”」を指定して任意のメッセージに置き換えるそれでも#N/Aのときは検索範囲と検索値のデータ型（数値か文字列か）を確認する
結果が正しくない（違う値が返る）	検索範囲と戻り範囲の行数が揃っていない	両方の範囲の行数を揃える（例：$A$2:$A$10 と $B$2:$B$10）
オートフィルで範囲がズレる	絶対参照（$）が使われていない	検索範囲と戻り範囲に $（絶対参照）を付ける（例：$A$2:$A$11）
完全一致しないのに値が返る	第5引数（一致モード）の指定が不足	第5引数に 0 を指定すると完全一致になる =XLOOKUP(E2,$A$2:$A$11,$B$2:$B$11,”見つかりません”,0)
数式を入れても空白のまま	ブックの計算が「手動」になっている	数式タブ → 計算オプション → 自動に変更するまたは F9キーで再計算する

VBAでXLOOKUPの数式を一括入力する

何度も同じXLOOKUPの数式を入力するのが面倒なときは、VBAで一括セットするのもおすすめです。

Sub SetXlookup()
    Dim rng As Range
    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For Each rng In Selection
        If rng.Column = 6 Then 'F列
            rng.Formula = "=XLOOKUP(E" & rng.Row & ",$A$2:$A$" & lr & ",$B$2:$B$" & lr & ",""見つかりません"")"
        End If
    Next rng
End Sub

タカヒロ

VBAが初めての方でも、この機会に試してみてください。
上のコードをコピーして、Alt+F11でVBAエディタを開き、標準モジュールに貼り付けるだけです。
あとは数式を入れたいセルを選択してマクロを実行すればOK！
$（絶対参照）が自動で付くので、オートフィルしても範囲がずれません。

関数の説明

今回使うXLOOKUP関数と、比較対象のVLOOKUP関数の概要です。

XLOOKUP関数（エックスルックアップ）

書式	=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
できること	指定した範囲から値を検索し、対応する値を別の範囲から返します。 VLOOKUPと違い、戻り範囲を自由に指定できるので、検索列より左側も取得できます。
戻り値	見つかった場合は対応するセルの値。見つからない場合は第4引数で指定した値（省略時は#N/A）。
こんなときに	商品コードから商品名を引く／社員番号から氏名を取得する／対応表から単価を探す
注意	Excel 2021以降またはMicrosoft 365で使用可能。古いExcelでは使えないので、他の人と共有するときは互換性に注意しましょう。

VLOOKUP関数（ブイルックアップ）

書式	=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
できること	範囲の左端の列を検索し、同じ行の指定した列番号の値を返します。長年使われてきた標準的な検索関数です。
戻り値	見つかった場合は対応するセルの値。見つからない場合は#N/A。
こんなときに	XLOOKUPが使えない環境での検索。シンプルな縦方向の検索。
注意	検索列は範囲の左端に固定されます。戻り値は列番号（何列目か）で指定するため、列を挿入すると結果がズレます。左方向の検索はできません。