「XLOOKUPが使えない…なんで？」「関数が使えないExcelでも同じことできる？」
そんな悩み、意外と多いんです。
このページでは、XLOOKUPが使えない原因と、代わりに使える関数やテクニックを初心者にもわかりやすく解説します！
XlookupとVlookupとの違いは
XlookupはVlookupの万能型の関数です。
例えば、以下のことがVlookupと違いできることになります。
- 検索値よりも左の列を抽出することができる（Vlookupは左の列がキーになるためできない）
- 検索値に該当する値がなかった時にエラー表示させない
- 降順検索することができる
- 複数条件の検索が可能
などなどです。（Microsoftサポート参照）
そもそもXLOOKUPが使えない理由は？
XlookupはExcel2019よりも新しいバージョンでないと使用できません。
ですが、Xlookupがなくても既存の関数を組み合わせることで、Xlookupの代用が可能です。
今回の記事は、Xlookup使えなくても、これまである関数を使って、Xlookupと同様のことをする方法を掲載します。
以下の表がテストデータです。
テストデータはこちらからダウンロードできます。
テストデータ
これらの問題は、Xlookupでできることですが、Xlookupの代わりに他の関数を使ってやっていきます。
検索値よりも左側の値を抽出する方法
Vlookupは検索値よりも左の列の値を抽出することができない縛りがあります。
これは、Index関数とMatch関数を組み合わせることで、解決することができます。
式は以下のような式になります。
=INDEX(A1:E16,MATCH(“ジョン”,B1:B16,0),1)
Vlookupは検索範囲で選択した１行目を検索します。
そのため、検索範囲で選択した場所よりも左の列を指定することができません。
しかし、Index関数・Xlookup関数は列数（Xlookupは戻り配列）を指定することができますので、検索する列よりも左側の値を引き出すことが可能です。
式の詳しい解説は、こちらの記事で方法を記載しておりますのでご参考ください。
検索値に該当する値がなかった時にエラー表示させない方法
Xlookupは値がない場合に指定をすることが可能です。
通常参照する値がないと＃N/Aという値が表示されます。
これを「非表示」や「該当なし」といったいことができます。
これはVlookupの時にはなかった機能ですが、IFERROR関数もしくはIFNA関数などエラー処理専用の関数をセットして、エラーケースの対処が可能となります。
式は以下のような式になります。
=IFNA(INDEX(A1:E16,MATCH(“ブラッドピット”,B1:B16,0),4),”該当なし”)
降順検索する方法
Vlookup関数、Index＆Match関数では昇順での検索になります。
Xlookupでは昇順に加えて降順でも検索することができます。
降順検索の使いどころは、今回のように新しい登録者を検索するといった時などに使うことができます。
今回名前検索なのでデータが重複するケースを想定しています。
最新のデータ（Noの大きいデータ）を抽出する方法です。
ここでは、Index・Max・Row関数を使うことで降順検索ができるようになります。
マリアはこの表上には３人います。
“降順”なので、一番新しい情報の③のマリアを検索するようにします。
このような式になります。
=IFNA(INDEX(A1:E16,IF(MAX((B1:B16=”マリア”)ROW(B1:B16))=0,NA(),MAX((B1:B16=”マリア”)ROW(B1:B16))),4),”該当なし”)
※エラー処理を行うため、IFNAの関数を使用しています。
配列なので、式を確定する際は「Ctrl＋Shift＋Enterキー」で決定することを忘れないでください。
これで降順検索も可能です。（式の構造上、厳密にいうと降順検索ではないのですが…）
下記のブログで式の解説をより詳しくしていますので、ご覧ください。
複数条件で検索する方法
Xlookupは複数検索ができるようになります。
これもIndexとMatch関数そして＆を使うことで、複数条件の検索が可能になります。
今回は「アメリカ人女性で読書をする人は？」ということなので、性別：女性、出身国：アメリカ、趣味：読書の人を検索します。
Xlookupがなくても、複数検索もこの通りです。
式は以下のようになります。
=IFNA(INDEX(A1:E16,MATCH(“アメリカ”&”女”&”読書”,D1:D16&C1:C16&E1:E16,0),2),”該当なし”)
※これもMATCH関数のところで配列を使いますので、式を確定する際、Ctrl＋Shift＋Enterをするのを忘れずに。
忘れると＃Valueエラーになります。
複数検索の計算式の仕組みを知りたい方は、こちらの記事に詳しく記載しております。
スピル機能の方法
Xlookupの良い点はスピル機能と言って、検索したい項目が複数あった場合に式を入力しなくても自動的に取得してくれる機能があります。
「式を入力しなくても」という部分はできませんが、隣接する式をオートフィルで獲得できる方法はあります。
列を取得する時にColumnという関数を使用します。
式はこのような式になります。
=IFNA(INDEX($A$1:$E$16,MATCH($A10,$A$1:$A$16,0),COLUMN(B$1)),”該当なし”)
名前を取得する式ですが、これをオートフィルで性別、出身国、趣味と続けて取得できるようになっています。
これは、COLUMN(B$1)で相対参照になっており、セルが一つ隣にずれるといったような形になっています。
まとめ
いかがでしたでしょうか。
式が少し長くなりますが、Xlookupがなくても何とかなります。
Excelでは色々な関数がありますが、バージョンによって使えない関数も存在しますが、関数を組み合わせてできるものもあります。
今回の記事がお役に立ちましたら幸いです。
他にも関数を組み合わせて作る式をご紹介しておりますので、よろしければご覧ください。
コメント