【Excel】XLOOKUPに乗り換えて便利だったこと｜IFERROR不要・列がずれない・左の列も取れる

私は仕事でExcelのデータ集計をすることが多く、以前はVLOOKUPを使っていました。

会社のPCはExcel 2016でしたが、PCの入れ替えでOffice 365になり、XLOOKUPが使えるようになりました。VLOOKUPより便利なので、今では職場の人が使う資料にもXLOOKUPを使っています。

XLOOKUPの基本的な使い方は、他のサイトで詳しく紹介されています。このブログでは、私が実務で使っている便利な使い方を紹介します。

なお、XLOOKUPはMicrosoft 365とExcel 2021以降で使えます。Excel 2019以前では使えません。

見つからないときの処理にIFERRORが要らない

VLOOKUPでは、検索値が見つからないと「#N/A」が表示されます。これを空白にするために、IFERRORで囲んでいました。

=IFERROR(VLOOKUP(A2,商品マスタ!A:C,3,FALSE),"")

XLOOKUPには、見つからないときに表示する値を指定する引数があります。IFERRORで囲む必要がありません。

=XLOOKUP(A2,商品マスタ!A:A,商品マスタ!C:C,"")

4つ目の引数「””」が、見つからないときに表示する値です。「該当なし」のように文字を入れることもできます。

また、VLOOKUPは最後の引数を省略すると近似一致になるため、毎回「FALSE」を入れる必要がありました。XLOOKUPは省略すると完全一致になるので、入れ忘れによる間違いが起きにくくなります。

VLOOKUPは、取り出す列を「3」のような列番号で指定します。

=VLOOKUP(A2,商品マスタ!A:D,3,FALSE)

元の表に列を追加したり削除したりしても、式の列番号は3のままです。そのため、別の列の値を取り出してしまいます。

私はこれで困っていたので、MATCHで列番号を求めるようにして対応していました。見出しの「単価」が何列目にあるかをMATCHで探し、その番号をVLOOKUPに渡す方法です。

=VLOOKUP(A2,商品マスタ!$A:$D,MATCH("単価",商品マスタ!$A$1:$D$1,0),FALSE)

XLOOKUPは、取り出す列を範囲で指定します。

=XLOOKUP(A2,商品マスタ!A:A,商品マスタ!C:C,"")

列を追加したり削除したりすると、範囲も自動で動くので（C:CがD:Dに変わるなど）、同じ列を取り出し続けます。MATCHを組み合わせる必要もありません。

VLOOKUPは、検索する列より右にある列しか取り出せません。そのため、取り出したい列より左に、検索値の列を用意する必要がありました。「=B2」のように、検索値のセルを参照するだけの列です。

XLOOKUPは「検索する範囲」と「取り出す範囲」を別々に指定するので、左の列も取り出せます。

例えば、A列に商品コード、B列に商品名がある表で、商品名から商品コードを取り出す場合です。

=XLOOKUP(E2,B:B,A:A,"")

取り出す範囲に複数の列を指定すると、1つの式で複数の列をまとめて取り出せます。

=XLOOKUP(E2,A:A,B:D,"")

この式を入れたセルから右へ3列分、結果が自動で表示されます（スピル）。VLOOKUPのように、列ごとに列番号を変えた式を入れる必要がありません。

右のセルに値が入っていると「#SPILL!」というエラーになるので、結果を表示する分のセルは空けておいてください。

VLOOKUPは縦方向、HLOOKUPは横方向と、表の向きによって関数を使い分ける必要がありました。

XLOOKUPは、検索する範囲に行を指定すれば、横方向にも検索できます。例えば、1行目に月、2行目に売上が並んでいる表で、4月の売上を取り出す場合です。

=XLOOKUP("4月",B1:M1,B2:M2,"")

表の向きを気にせず、同じ関数で書けます。

XLOOKUPに乗り換えて便利だったことは、次の4つです。

一方で、VLOOKUPのように範囲を指定して、取り出す列を番号で指定することはできません。私はXLOOKUPを2回使うことで解決しています。この方法は、クロス表の検索の記事で紹介します。

XLOOKUPと一緒に使っているテーブルとパワークエリについては、こちらの記事で紹介しています。