他の人から受け取ったExcelブックを開いたところ、XLOOKUP関数が動かない場合は、まず数式を直すのではなく自分が使っているExcelの製品を確認してください。
XLOOKUPは、Excel for Microsoft 365、Excel 2021、Excel 2024などでは利用できますが、Excel 2016とExcel 2019では利用できません。MicrosoftのXLOOKUP解説ページでは適用製品一覧にExcel 2016・2019も表示されていますが、本文中で「Excel 2016とExcel 2019ではXLOOKUPを利用できない」と明記されています。したがって、適用製品一覧だけを見て旧バージョンでも使えると判断しないことが重要です。([マイクロソフトサポート][1])
この記事では、XLOOKUPが使えない原因をExcelのバージョンから切り分ける方法、対応版での基本的な使い方、#N/Aとの違い、検索モードを変更するときの注意点まで具体的に解説します。
XLOOKUPが使えないときは最初にExcelの製品名を確認する
「同じファイルなのに、作成した人のパソコンでは動くのに自分のパソコンでは動かない」という場合、最初に確認したいのがExcelの製品です。
XLOOKUPへの対応状況は、概ね次のように整理できます。
| Excelの製品 | XLOOKUP |
|---|---|
| Excel for Microsoft 365 | 対応 |
| Excel 2024 | 対応 |
| Excel 2021 | 対応 |
| Excel 2019 | 非対応 |
| Excel 2016 | 非対応 |
Microsoftは、Excel 2016またはExcel 2019で、より新しいExcelを使って作成されたXLOOKUP入りブックを扱うケースがあり得ると説明しています。ただし、ブックを開けることと、そのExcelでXLOOKUPを利用できることは別です。Excel 2016・2019ではXLOOKUP自体が利用できません。([マイクロソフトサポート][1])
そのため、受け取ったブックのXLOOKUPが動かない場合は、いきなり数式を書き換えるのではなく、まず自分のExcelが2016・2019なのか、それともMicrosoft 365・2021・2024などなのかを確認しましょう。
自分が使っているExcelのバージョンを確認する
Windows版Officeでは、Excelを開いて**「ファイル」→「アカウント」**と進み、「製品情報」を確認すると、使用中のOffice製品名を確認できます。
Microsoftの案内でも、Officeアプリの「アカウント」にある「製品情報」から製品名やバージョン番号を確認する方法が案内されています。([マイクロソフトサポート][2])
確認したら、少なくとも次の情報を控えておくと原因を切り分けやすくなります。
- Microsoft 365なのか買い切り版Officeなのか
- Excel 2024、2021、2019、2016のどれなのか
- 必要に応じてExcelの詳細なバージョン番号
たとえば「Office Home & Business 2019」など、製品名から2019であることが分かれば、XLOOKUPが動かない原因を数式の書き間違いだけに求める必要はありません。
Excel 2016・2019なら設定変更だけでは解決しない
ここは特に注意が必要です。
Excel 2016やExcel 2019でXLOOKUPが使えない場合、通常のオプション設定を一つ変更するだけでXLOOKUPが追加される、という問題ではありません。
共同作業を続けるなら、次のどちらかを検討します。
- XLOOKUPに対応したExcel for Microsoft 365、Excel 2021、Excel 2024などで扱う
- Excel 2016・2019の利用者もいる前提で、VLOOKUPやINDEXとMATCHなどを使った互換性の高い設計に変更する
「自分だけMicrosoft 365にすればよい」のか、「取引先や共同編集者にもExcel 2019利用者がいるため数式自体を変更すべきなのか」で適切な対応は変わります。
ファイルを複数人で使う場合は、作成者のExcelだけでなく、利用者全員のExcel環境を基準に関数を選ぶことが重要です。
対応版ならXLOOKUPの基本形を確認する
Excel for Microsoft 365、Excel 2021、Excel 2024などの対応環境であれば、次はXLOOKUPの数式そのものを確認します。
基本的な構文は次の形です。
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])Microsoftによると、XLOOKUPでは検索する範囲と、結果を返す範囲を別々に指定できます。また、一致モードを省略した場合は完全一致が既定です。([マイクロソフトサポート][1])
たとえば、次のような表を考えます。
| A列：商品コード | B列：商品名 | C列：価格 |
|---|---|---|
| A001 | ボールペン | 120 |
| A002 | ノート | 250 |
| A003 | ファイル | 380 |
E2セルに商品コードを入力し、その商品の価格を取得するなら、次のように書けます。
=XLOOKUP(E2,A2:A10,C2:C10,"該当なし")それぞれの意味は次のとおりです。
| 部分 | 意味 |
|---|---|
E2 | 探したい値 |
A2:A10 | 探す範囲 |
C2:C10 | 見つかった行から返す範囲 |
"該当なし" | 見つからなかった場合に表示する文字 |
E2にA002が入力されていれば、A列からA002を探し、同じ行にあるC列の値を返す、という考え方です。
XLOOKUPは検索列と返す列を別々に指定できる
XLOOKUPの重要な特徴の一つが、
検索範囲と
戻り範囲を別々に指定できる点です。
Microsoftの説明でも、XLOOKUPは検索用の配列と戻り値用の配列を使用するとされています。VLOOKUPのように一つの表範囲と列番号を指定する方式とは異なります。([マイクロソフトサポート][1])
そのため、数式を確認するときは、
「何列目を返しているのか」
ではなく、
「どの範囲から探し、どの範囲から値を返しているのか」
という見方をすると理解しやすくなります。
XLOOKUPが使えない問題と「#N/A」は別の問題
XLOOKUP入りのブックを受け取ったときに混同しやすいのが、
XLOOKUP関数そのものが使えない
場合と、
XLOOKUPは動いているが検索対象が見つからない
場合です。
この2つは分けて考える必要があります。
「該当なし」と表示される場合
先ほどの数式では、第4引数に次の文字を指定しています。
"該当なし"=XLOOKUP(E2,A2:A10,C2:C10,"該当なし")検索値がA2:A10の中に存在しなければ、「該当なし」と表示されます。
これはXLOOKUPが使えないという意味ではありません。
むしろ、XLOOKUPは正常に実行され、その結果として一致するデータが見つからなかったという状態です。
第4引数を省略すると#N/Aになる
次のように「見つからない場合」の指定を省略したとします。
=XLOOKUP(E2,A2:A10,C2:C10)このとき有効な一致が見つからなければ、XLOOKUPは#N/Aを返します。Microsoftも、第4引数のif_not_foundを省略し、有効な一致が存在しない場合は#N/Aになると説明しています。([マイクロソフトサポート][1])
つまり、#N/Aが表示されたからといって、
「このExcelはXLOOKUP非対応だ」
とは判断できません。
対応版でXLOOKUPが正常に計算された結果、検索値が見つからなかった可能性があります。
XLOOKUPで値が見つからないときに確認するポイント
対応版のExcelを使用しているにもかかわらず期待する結果にならない場合は、次の順序で確認すると切り分けやすくなります。
- 検索値が正しいか確認する
- 検索範囲が正しい列を参照しているか確認する
- 戻り範囲が取得したい列になっているか確認する
- 検索値が実際に検索範囲内に存在するか確認する
- 一致モードや検索モードを特殊な設定に変更していないか確認する
特に、他の人が作成したブックでは数式だけを見ても、どの列をキーとしているのか分かりにくいことがあります。
たとえば、
=XLOOKUP(E2,A2:A10,C2:C10,"該当なし")なら、次の3点を文章に置き換えて確認すると分かりやすくなります。
「E2の値を」
「A2:A10から探し」
「一致した行のC2:C10を返す」
この形まで分解すると、範囲指定の間違いを見つけやすくなります。
XLOOKUPは既定で完全一致する
XLOOKUPでは、一致モードを省略した場合、完全一致が既定です。
Microsoftが示している構文では第5引数が一致モードで、0が完全一致です。これは既定値なので、通常の完全一致検索であれば明示的に指定しなくても構いません。([マイクロソフトサポート][1])
そのため、
=XLOOKUP(E2,A2:A10,C2:C10,"該当なし")という数式でも、基本的にはE2と完全に一致する値をA2:A10から探します。
VLOOKUPを使った経験がある人は、一致方法を省略したときの挙動と混同しやすいため注意してください。
XLOOKUPでは、通常のコード検索、社員番号検索、商品番号検索など完全一致させたい用途なら、まず既定の検索方法を使う方が数式の意図を理解しやすくなります。
二分探索の検索モードは並び順に注意する
XLOOKUPには検索方向や検索方法を指定する第6引数の「検索モード」があります。
Microsoftが示している検索モードは次のとおりです。([マイクロソフトサポート][1])
| 検索モード | 動作 |
|---|---|
1 | 先頭から検索。既定 |
-1 | 最後から逆方向に検索 |
2 | 昇順に並んだ検索範囲を二分探索 |
-2 | 降順に並んだ検索範囲を二分探索 |
特に注意したいのが2と-2です。
検索モード2では検索範囲が昇順、-2では降順に並んでいることが前提です。
Microsoftは、必要な並び順になっていない状態で二分探索を使用すると、無効な結果が返されると説明しています。([マイクロソフトサポート][1])
たとえば、
=XLOOKUP(E2,A2:A10000,C2:C10000,"該当なし",0,2)と書かれているからといって、検索モード2を単純に「高速な検索」と考えてはいけません。
A2:A10000が昇順になっていることが前提です。
並べ替えが保証されていない一般的な一覧表であれば、無理に二分探索を指定せず、既定の検索モードを使う方が安全です。
他の人から受け取ったXLOOKUP入りブックの確認手順
実際にXLOOKUP入りブックが動かない場合は、次の順番で確認すると原因を整理できます。
| 順番 | 確認内容 | 判断 |
|---|---|---|
| 1 | Excelの製品名を確認 | 2016・2019か、対応版か |
| 2 | 2016・2019だった | XLOOKUP非対応として環境または数式設計を見直す |
| 3 | Microsoft 365・2021・2024などだった | XLOOKUPの引数を確認 |
| 4 | #N/Aや「該当なし」が出る | 検索値と検索範囲を確認 |
| 5 | 検索モードが2または-2 | 検索範囲の並び順を確認 |
ポイントは、最初から数式の修正に入らないことです。
Excel 2019でXLOOKUPを動かそうとしているのであれば、検索範囲を何度変更しても根本原因は解決しません。
反対に、Microsoft 365で#N/Aになっているだけなら、Excelを買い替える話ではなく、検索対象や数式を確認する問題です。
共同利用するExcelでは「作成者の環境」だけで決めない
XLOOKUPは便利ですが、複数人で共有するブックでは関数の機能だけでなく互換性も考える必要があります。
たとえば作成者がMicrosoft 365を使用していても、
- AさんはMicrosoft 365
- BさんはExcel 2021
- CさんはExcel 2019
という環境で同じブックを使用するなら、Cさんの環境ではXLOOKUPを利用できません。
この場合は、
「XLOOKUPを使える環境に統一する」
または、
「古いExcelでも扱える数式設計にする」
という判断が必要です。
社内だけで使うファイルならOffice環境をそろえられる場合がありますが、取引先や外部組織へ渡すファイルでは相手側のExcelバージョンまで統一できないこともあります。
そのため、長期間利用する帳票や複数組織で共有するブックでは、作成前に対応バージョンを決めておくとトラブルを減らせます。
XLOOKUPが動かないときの判断を整理
他の人から受け取ったXLOOKUP入りのブックが自分のExcelで動かないときは、次のように考えると原因を切り分けられます。
Excel 2016・2019を使っている場合は、XLOOKUP自体が非対応です。 設定だけを探すのではなく、対応するExcelで扱うか、利用者の環境に合わせて数式を見直します。([マイクロソフトサポート][1])
Excel for Microsoft 365、Excel 2021、Excel 2024などの対応版を使っている場合は、検索値・検索範囲・戻り範囲を確認します。
=XLOOKUP(E2,A2:A10,C2:C10,"該当なし")という基本形なら、E2の値をA2:A10から探し、一致した行のC列を返します。XLOOKUPの既定は完全一致です。一致する値がなく、第4引数も指定していなければ#N/Aになります。([マイクロソフトサポート][1])
さらに検索モード2または-2を使っている場合は、指定された昇順・降順が守られているかも確認してください。
まず**「自分のExcelでXLOOKUPが使えるか」を確認し、その後で「数式の検索条件が正しいか」**を調べる。この順番で確認すれば、バージョン非対応とデータ検索の失敗を混同せず、効率よく原因を特定できます。
[1]: https://support.microsoft.com/en-us/excel/functions/xlookup-function “XLOOKUP function | Microsoft Support”
[2]: https://support.microsoft.com/ja-jp/office/lifecycle/lc-account/about-office-what-version-of-office-am-i-using?utm_source=chatgpt.com “Office について:使用している Office のバージョンを確認する方法 | Microsoft Support”
コメント