ExcelのXLOOKUP関数の使い方｜VLOOKUPとの違いとエラー対策

ExcelのXLOOKUP関数の使い方について、原因を切り分けながら直す方法を解説します。

画面やメニューの名称は、Windows版のExcel for Microsoft 365・Excel 2024を基準にしています。

 

最初に確認したい結論

XLOOKUPは検索範囲と戻り範囲を別々に指定でき、左方向の検索や見つからない場合の表示も数式内で設定できます。

大切なファイルを操作するときは、先にコピーを作成してから対処してください。

 

主な原因

XLOOKUPはMicrosoft 365や新しい永続版Excelで利用でき、古いバージョンでは関数名エラーになる場合があります。

検索範囲と戻り範囲の行数または列数が違うと正しく計算できません。

完全一致が既定ですが、検索値の型や余分な空白が違うと見つかりません。

 

対処方法

 

基本のXLOOKUP数式を作る

1. 数式の第1引数へ検索したい値を指定します。
2. 第2引数へ検索範囲、第3引数へ返したい値の範囲を指定します。
3. 検索範囲と戻り範囲が同じ行数になっていることを確認します。

 

見つからない場合の表示を指定する

1. 第4引数へ「該当なし」などの文字を指定します。
2. 検索値がない場合に#N/Aではなく指定文字が表示されます。
3. 本来存在すべきデータまで隠していないか、検索漏れを別途確認します。

 

左方向や複数列を返す

1. 検索範囲より左側にある列も戻り範囲として指定できます。
2. 対応版では複数列の戻り範囲を指定すると、結果が隣のセルへ展開されます。
3. 展開先にデータがある場合は#SPILL!になるため、空きセルを確保します。

 

作業するときの注意点

XLOOKUPを含むブックを古いExcel利用者と共有する場合は互換性を確認してください。

古い環境も含む場合は、VLOOKUPやINDEXとMATCHを使った版を用意する方法があります。

 

まとめ

XLOOKUPは検索範囲と戻り範囲を別々に指定でき、左方向の検索や見つからない場合の表示も数式内で設定できます。

一度に複数の設定を変えず、原因になりそうな項目を一つずつ確認すると元に戻しやすくなります。