Excelの新しいXLOOKUPは、VLOOKUPに代わるもので、Excelの最も人気のある関数の1つに強力な代替手段を提供します。この新機能は、VLOOKUPのいくつかの制限に対応し、さらに機能を追加したものです。ここで知っておいていただきたいことがあります。

株式会社エクスルック(xlookup)は何ですか？

新機能XLOOKUPは、VLOOKUPの最大の欠点であったいくつかの点を解決しています。さらに、HLOOKUPに代わるものです。例えば、XLOOKUPは左側を見ることができ、デフォルトは完全一致で、列番号ではなくセル範囲を指定できます。vloopはそれほど使いやすく、汎用的ではありません。この仕組みをご紹介します。

現在、XLOOKUPはInsidersプログラムのユーザーのみが利用可能です。インサイダーズプログラムに参加すれば、誰でも最新のExcel機能をいち早く利用することができます。マイクロソフトは、近日中にすべてのoffice 365ユーザーへの展開を開始する予定です。

xlookup関数の使い方

次のデータを例にして、XLOOKUPの例で本題に入りましょう。A列の各IDの部署をF列から返したい。

これは完全一致検索の典型的な例です。XLOOKUP関数に必要な情報は3つだけです。

次の図は、6つのパラメータを持つXLOOKUPであるが、完全一致のために必要なのは最初の3つだけである。

• Find value：自分が求めているもの
• 配列の検索：場所を検索します。
• Returnu array: 返すべき値を含む範囲。

この例では、次の式が適用されます： =XLOOKUP(A2, $E$2:$E$8, $F$2:$F$8)

では、VLOOKUPに対するXLOOKUPの優位性をいくつか探ってみましょう。

カラムのインデックス番号が不要に

VLOOKUPの悪名高い第3パラメータは、テーブル配列から返すべき情報を指定する列番号である。XLOOKUPでは、返したい範囲（この例ではF列）を選択することができるので、このような問題はありません。

XLOOKUPは、以下で詳しく説明するVLOOKUPとは異なり、選択したセルの左側のデータを表示できることを忘れないでください。

また、**新しい列が追加されたときに、数式が壊れる問題もなくなりました。スプレッドシートでこれが発生した場合、戻り値の範囲が自動的に調整されます。

デフォルトは完全一致

VLOOKUPを学ぶとき、なぜ完全一致を指定しなければならないのか、常に混乱が生じます。

幸いなことに、XLOOKUPのデフォルトは完全一致です（ルックアップフォーミュラを使う理由としては、こちらの方が一般的です）。これにより、第5引数に答える必要がなくなり、数式を初めて使う人でも間違いが少なくなります。

つまり、XLOOKUPはVLOOKUPよりも質問数が少なく、親しみやすく、永続的に使えるということです。

xlookupは左を見ることができる

ルックアップ範囲を選択できるため、XLOOKUPはVLOOKUPよりも汎用性が高く、XLOOKUPではテーブルの列の順番は関係ありません。

VLOOKUPは、テーブルの一番左の列を検索し、指定された列数から右に戻ることで制約をかけます。

以下の例では、ID（E列）を見つけて、その人の名前（D列）を返す必要があります。

これは、次の式で実現されます：=XLOOKUP(A2, $E$2:$E$8, $D$2:$D$8)

見つからない場合の対処法

VLOOKUP関数やMatch関数で必要なものが見つからないときに表示される#N/Aエラーメッセージは、検索関数のユーザーならよくご存じでしょう。これには、たいてい論理的な理由がある。

そのため、ユーザーはこのエラーが正しくない、あるいは有用であるとして、すぐにこのエラーを隠す方法を調べます。もちろん、その方法はあります。

XLOOKUPには、このようなエラーを処理するための「if not found」パラメータが組み込まれています。先ほどの例を実際に見てみましょう。ただし、間違ったIDが入力されている状態です。

次の数式を実行すると、エラーメッセージではなく、「IDが正しくありません」というテキストが表示されます： =XLOOKUP(A2, $E$2:$E$8, $D$2:$D$8, "Incorrect ID")

範囲検索にxlookupを使用する

完全一致ほど一般的ではありませんが、ルックアップ式の非常に効果的な使い方は、範囲内の値を検索することです。次のような例を考えてみましょう。使った金額に応じて、割引で還元したい。

今回は特定の値を求めるのではなく、B列の値がE列の範囲に収まっていることを知る必要がある。これにより、得られる割引額が決まる。

XLOOKUPには、マッチモードと呼ばれるオプションの第5パラメータがあります（デフォルトは完全一致と覚えておいてください）。

XLOOKUPはVLOOKUPよりも近似マッチング力が高いことがわかります。

探している値より小さい（-1）か大きい（1）か、最も近い値を探すかどうかを選択することができます。また、ワイルドカード（2）文字を使用するオプションもあります（例：?または * 記号を使用します。デフォルトでは、VLOOKUPと異なる設定になっています。

この例の式は、完全に一致する値が見つからない場合、見つかった値よりも小さい最も近い値を返します： =XLOOKUP(B2, $E$3:$E$7, $F$3:$F$7,, -1)

しかし、セルC7には#N/aエラーが返される（'if not found'パラメータが使用されていない）エラーがある。これは、消費額64が割引の基準を満たさないため、0％の割引が適用されるはずです。

XLOOKUP関数のもう一つの利点は、VLOOKUPのように検索範囲を昇順にソートする必要がないことです。

ルックアップテーブルの下部に新しい行を入力し、数式を開きます。コーナーポイントをクリックしてドラッグし、使用する範囲を広げます。

この数式はすぐにエラーを修正します。「0」を範囲の一番下に配置することは問題ありません。

個人的には、今でもルックアップ・カラムでテーブルをソートしています。一番下に「0」があると、気が狂いそうです。でも、配合が崩れないというのは素晴らしいですね。

xlookup は、hlookup 関数の代わりにもなります。

前述したように、ここでもHLOOKUPの代わりにXLOOKUP関数が使われている。1つの関数が2つの関数を置き換える。

HLOOKUP関数は水平方向のルックアップで、行に沿った検索に使用されます。

兄弟分のVLOOKUPほど有名ではありませんが、次のような例で、ヘッダーがA列、データが4行と5行にある場合に便利です。

XLOOKUPは、列の下方向と行に沿った方向の2方向で見ることができるので、2種類の関数が不要になりました。

この例では、セル A2 の名前に関連する売上高を返す数式を使用しています。4行目に沿って名前を探し、5行目から値を返す： =XLOOKUP(A2, B4:E4, B5:E5)

xlookupは下から上に表示することができます。

通常、ある値に対して最初に（通常は一意に）一致するリストを見つける必要があります。xlookerupには検索モードと呼ばれる6番目のパラメータがあります。これにより、検索を下から始めるように切り替え、リストを検索して最後に発生した値を見つけることができます。

以下の例では、A列にある各製品の在庫レベルを求めたいと思います。

ルックアップテーブルは日付順に並べられ、商品ごとに複数の在庫確認ができるようになっています。最終チェック時（商品IDの最終出現時）の在庫レベルを返したい。

XLOOKUP関数の6番目のパラメータには4つのオプションがあり、ここでは「最後から一番目の検索」オプションを使用することに興味があります。

完全な式は以下の通りです： =XLOOKUP(A2, $E$2:$E$9, $F$2:$F$9，，，，-1)

この式では、4番目と5番目の引数は無視されます。これはオプションで、デフォルトは完全一致になることを期待しています。

アグリゲート

XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数の後継として待望されている関数です。

この記事では、さまざまな例を用いて、XLOOKUPの利点を紹介します。その一つは、XLOOKUPがワークシート、ワークブック、テーブルをまたいで使えることです。記事中の例は、私たちが理解しやすいようにシンプルなものにしています。

動的配列がExcelに導入される予定なので、値の範囲を返すことも可能です。

Flukupの時代は終わり、xLOOKUPが登場し、まもなくそれを見つけるためのデファクト式になるでしょう。