Microsoft 365 の Excel に追加された XLOOKUP 関数について、今回は指定した検索値が見つからなかった場合の処理についてご紹介します。
(XLOOKUP 関数は Excel 2016 や Excel 2019 では利用できないのでご注意を)
XLOOKUP 関数については こちら もご覧ください。
数式に必要な材料 (引数) の指定について
XLOOKUP 関数の構文はこちら↓
=XLOOKUP(検索値 , 検索範囲 , 戻り範囲 , [見つからない場合] , [一致モード] , [検索モード])
検索値は VLOOKUP 関数と同じ、キーワードとしたい値 (が格納されているセル) を指定します。
検索範囲は検索値に指定した値を探す範囲です。
戻り範囲は結果として返したい値の含まれる範囲です。
下図のセル F2 には XLOOKUP 関数を使った数式が作成されていますが、検索値 (セル E2) に指定されている値が、検索範囲 (A2:A11) で見つからないので #N/A エラーが返っています。
■ セル F2 の数式
=XLOOKUP(E2 , A2:A11 , B2:B11)
検索値が検索範囲で見つからなかった場合に、計算結果として何を返すのかを指定するのが第 4 引数の [見つからない場合] です。
下図では、検索値が見つからない (存在しない) ときには、「IDを確認」という文字列を返す数式で対応しています。
■ セル F2 の数式
=XLOOKUP(E2 , A2:A11 , B2:B11 , "IDを確認")
VLOOKUP 関数で、IFERROR 関数などと組み合わせて行っていたエラー処理が 1 つの関数の数式のなかで指定できるため、すっきりシンプルでよいですね。
おまけ
やらないと思うし、そもそもデータをきれいにしようよ・・・という話だけれど。
検索値が見つからなかったら、ほかの検索値 (ほかの列) で探したらどう？という感じで、見つからない場合 に XLOOKUP の数式をネストしてみました。
下図は、名前 (上杉) で探したけどいないから、種別 (EE) で探してみた、という数式の結果です。前半の XLOOKUP の処理の 見つからない場合 が、後半の XLOOKUP 関数の処理です。
ヘルプに見つからない場合に指定したテキストが返る、と書いてあったけれど、もちろん別の処理を入れてもいいよね、と思い試しにやってみただけです。なんで上杉さんだけカタカナ？という、こんな揺れのあるデータベースは使いたくないけどね。。。
XLOOKUP 関数だけでエラー処理も指定できるので今までよりもすっきりですね。
おまけを書いておいてなんだけど、そもそもテーブル側の検索範囲の文字列の後ろとかに余計なスペースがあって見つからない！とかは VLOOKUP だろうが、XLOOKUP だろうが同じ話なので、データベースはキレイに整えましょう。
石田 かのこ
