XLOOKUP関数の使い方|VLOOKUPとの違いと、エラーの直し方

結論: XLOOKUP関数は、検索値を、検索範囲から探し、対応する戻り範囲の値を返す関数です。VLOOKUPと違い、左側の列も検索でき、列番号を数える必要がありません。省略した時は完全一致で、見つからない時の表示も、第4引数で指定できます。使えるのは、Microsoft 365と、Excel 2021以降です。

XLOOKUPとは

表の中から、条件に合う行を探して、別の列の値を取り出す関数です。たとえば、「社員番号から、氏名を調べる」ような場面で使います。

書き方

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合])

例:社員番号から、氏名を探す

E1に「102」と入力して、E2に次の式を入れます。

=XLOOKUP(E1, A2:A4, B2:B4, "該当なし")

結果は「田中」です。E1に、表にない番号(たとえば「999」)を入れると、「該当なし」と表示されます。

VLOOKUPとの違い

VLOOKUPの使い方は、VLOOKUP関数の使い方で解説しています。

XLOOKUPが便利な点

• 列番号を数えなくてよい:VLOOKUPでは、「左から何列目か」を数えて、数字で指定します。列を挿入・削除すると、その数字がずれて、結果が変わる原因になります。XLOOKUPは、範囲で指定するので、ずれにくくなります
• 左側の列も検索できる:「氏名から、社員番号(左の列)を探す」といった、逆向きの検索ができます
• 完全一致が既定:省略した時に、意図しない近似一致になる、VLOOKUPでよくある間違いを防げます

オプションの引数

第5引数と、第6引数で、探し方を変えられます。

同じ値が複数ある時に、最後に出てくる行を取り出したい場合は、第6引数に -1 を指定します。

複数の列を、一度に取り出す

戻り範囲に、複数の列を指定すると、該当する行の値が、横に並んで表示されます。

=XLOOKUP(E1, A2:A4, B2:D4, "該当なし")

B列からD列までの値が、まとめて表示されます。

エラーが出た時の確認

数字が文字列になっている場合の直し方は、Excelの文字と数字の変換、先頭の0が消える場合は、先頭の0が消える時の対処をご覧ください。重複した値の確認は、Excelで重複を見つけて削除する方法が参考になります。

古いExcelを使う人と、共有する時

XLOOKUPは、Excel 2019以前では使えません。ファイルを共有する相手が古い版の場合、**#NAME?**と表示されることがあります。

• 相手のバージョンを確認する
• 古い版の人とやり取りするなら、VLOOKUP、またはINDEX関数とMATCH関数の組み合わせを使う
• 値だけを渡すなら、値として貼り付けて、渡す

まとめ

• XLOOKUPは、検索値を検索範囲から探し、対応する戻り範囲の値を返す関数
• VLOOKUPと違い、左側も検索でき、列番号を数えず、省略した時は完全一致
• 第4引数で、見つからない時の表示を指定できる
• 使えるのは、Microsoft 365とExcel 2021以降。古い版の人との共有には注意する

関連記事:VLOOKUP関数の使い方、IF関数の使い方、ピボットテーブルの使い方、Excelでプルダウンリストを作る方法、Excelで重複を見つけて削除する方法