【Excel新関数】VLOOKUP関数のもどかしさとサヨナラ！エクセルの新関数XLOOKUPの驚きの機能とは？

VLOOKUP関数のおさらい

　まず、VLOOKUP関数のおさらいをしましょう。VLOOKUP関数は、指定したデータのリストから検索条件を満たすデータを探し出して表示してくれる関数です。次のような「請求書」で、A列に入力された商品番号（①）をもとに、右側の「商品カタログ」（②）から商品名を取り出して、B列（③）に自動的に表示することができます。

　VLOOKUP関数は、次のような書式で記述します。

　実際にB列（「請求書」の「商品名」列）に入力する数式を見ながら解説していきます。ここでは、セルB3に次の数式を入力しています。これは、セルA3に入力された商品番号を商品カタログ（セル範囲G3:I10）から検索し、それに対応する商品名を検索して表示するという数式です。

　この数式により、商品カタログ（セル範囲G3:I10）の左端の列（④）からセルA3の値に合致するデータを検索して、2番目の列の値（商品名）（⑤）を取り出して表示することができます。

　このVLOOKUP関数の詳細については、以前の記事も参考にしてください。

　ところで、この関数の3つ目の引数「列番号」ですが、「範囲」の中から取り出したい列を数値で指定しなくてはならないことに不便を感じたことはありませんか。

　この例で、「商品カタログ」の2番目の列（H列）に新しい列が追加された場合（⑥）、数式で指定している「2」が指す列は変わってしまいます。「商品名」を取り出すには、この引数の値を書き換えなければなりません（⑦）。

　これでは、使い勝手があまりいいとはいえませんよね。

　また、この数式を別のセルにコピーしたい場合、うまくいかないことがあります。例えば、C列（「請求書」の「単価」列）にも同様にVLOOKUP関数を使った数式を入力して、「商品カタログ」から単価を取り出して表示したい場合、セルB3の数式をセルC3にコピー（⑧）すれば簡単にできそうに思いませんか？

　でも実際は、数式の3つ目の引数「2」が自動的に変更されないため、正しい情報を取得できません。やはり、この引数の値を書き換えなければならず（⑨）、面倒です。

　このようなもどかしさは、XLOOKUP関数で解決できます。次の項では、実際にその使い方を見ていきましょう。

XLOOKUP関数を使ってみよう

　前述の「請求書」の例を使って、今度は、XLOOKUP関数で同じことをやってみます。まず、書式を押さえておきましょう。

　たくさん引数があって難しそうに見えるかもしれませんが、最初の3つの引数以外は省略可能です。

　では、数式を入力していきましょう。まず、セルB3に「=XLOOKUP(」と入力したあとで、検索値となる「A3」を入力し、「,」（カンマ）を入力します（①）。

　続けて、検索値（セルA3の値）を検索するセル範囲（G3:G10）を指定します。ここでは、セル範囲は絶対参照で「$G$3:$G$10」と入力し、「,」（カンマ）を入れます（②）。

　最後に、表示したい商品名が入力された範囲（H3:H10）を指定します。ここでも、セル範囲は絶対参照で「$H$3:$H$10」と入力します。以降の引数は省略し、「)」（カッコ）を入力します（③）。

　この時、2つ目の引数（$G$3:$G$10）と3つ目の引数（$H$3:$H$10）は、同じ行番号（ここでは行番号3～10）にする必要があります。行番号が一致していないと、うまくいかないので注意してくださいね。数式を入力できたら、［Enter］キーを押して、数式を確定します。

　セルB3に商品名が表示されましたね（④）。これがXLOOKUP関数の基本の記述方法です。

　XLOOKUP関数では、「検索範囲」と「戻り範囲」をセル範囲で別々に指定できます。したがって、前項で解説したような、参照している表（ここでは「商品カタログ」）の列が追加されるたびに数式を修正しなければならないという問題は解決されます。

　また、前項で解説したもう1つの問題、コピペの時にうまくいかないというもどかしさも解決できます。ただし、これを解決するには、今書いた数式とは少し異なる書き方をします。

　今度は、セルB3には「=XLOOKUP(A3,$G$3:$G$10,$H$3:$I$10)」（⑤）と入力します。先ほどと異なる点は、3つ目の引数に「$H$3:$I$10」（⑥）と指定していることです。このようにXLOOKUP関数では、複数の列（ここでは「商品名」列と「単価」列）をまとめて指定することができます。

　数式を入力できたら、［Enter］キーを押して数式を確定しましょう。

　すると、式を入力したセルB3だけでなく、隣のセルCにも該当するデータが自動的に表示されましたね（⑦）！　これは、すごいと思いませんか？！

　この魔法みたいな機能は「スピル」といい、Microsoft 365に登場した新たな機能の1つです。今回は、「商品カタログ」から取り出して表示したい列は2列だけでした。でも、参照する表からいくつもの列の情報を取り出して表示したい場合、1カ所に数式を入力するだけで済むのでたいへん効率ですね。スピルの機能については、また別の回で詳しく紹介したいと思います。

VLOOKUP関数のヘビーユーザーにとって救世主のようなXLOOKUP関数！

　今回は、Microsoft 365（旧称Office 365）に新しく登場したXLOOKUP関数について解説しました。VLOOKUP関数に比べて、格段に便利なことがわかってもらえたのではないでしょうか。実は、このXLOOKUP関数、まだまだ便利な使い方ができます。次回も、皆さんのExcel業務に役立つ情報を紹介していきますので、楽しみにしていてくださいね！