Xlookupが使えない！(見つからない)Xlookupの代わりに使える関数はある？複数条件検索は？降順検索は？Xlookupなくても、既存の関数で対応できるって知っていますか？
XlookupはVlookupとは違い、簡単かつ高機能になっています。
例えば、
- 検索値よりも左の列を抽出することができる
- 検索値に該当する値がなかった時にエラー表示させない
- 降順検索することができる
- 複数条件で検索できる
などなど、Vlookupではできなかったことができるようになりました。（Microsoftサポート参照）
ですが、Xlookupがなくても関数の組み合わせでできます。
Xlookupの欠点はOfficeの古いバージョンを使っている方ですと、このXlookupが使えません。
今回は、Xlookup使えなくても、古いバージョンのExcelでも使える関数を使って、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では昇順に加えて降順でも検索することができます。
降順検索の使いどころは、今回のように新しい登録者を検索するといった時などに使うことができます。今回名前検索なのでデータが重複しますが、最新の降順で抽出が可能です。
ここでは、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)),”該当なし”)
名前を取得する式ですが、これをオートフィルで性別、出身国、趣味と続けて取得できるようになっています。
まとめ
いかがでしたでしょうか。
式が少し長くなりますが、Xlookupがなくても何とかなります。
Excelでは色々な関数がありますが、バージョンによって使えない関数も存在しますが、関数を組み合わせてできるものもあります。
今回の記事がお役に立ちましたら幸いです。
他にも関数を組み合わせて作る式をご紹介しておりますので、よろしければご覧ください。
コメント