【Excel関数】XLOOKUP関数の使い方商品リストを参照して必要なデータを取り出す

ここでは、XLOOKUP関数の使い方について解説する。売上データを表にまとめるとき、いちいち商品コードや商品名、単価などを入力するのは手間がかかるもの。XLOOKUP関数を使えば、商品リストから商品コードで検索して、商品名などを取り出すことができるから大幅な省力化が可能となる。

エクセルを簡易データベースとして使う!

商品リストを参照して必要な情報を取り出す

売上データを表にまとめるとき、いちいち商品コードや商品名、単価などを入力するのは手間がかかります。そこで使いたいのが、XLOOKUP関数です。商品リストから商品コードで検索して、商品名などを取り出すことができ、大幅な省力化が可能です。

XLOOKUP	指定した値を検索範囲で検索し、対応する戻り範囲の行位置の値を返す
=XLOOKUP(検索値,検索範囲,戻り配列), [見つからない場合],[一致モード],[検索モード])

引数が多いので難しそうに見えますが、第4引数以降は省略可能です。第1引数の「検索値」は、検索に使う数値や文字列を指定します。第2引数の「検索範囲」は、検索値をどこから探すかを指定します。第3引数の「戻り配列」は、検索結果から取り出すデータを指定するものです。例えば商品名を取り出したい場合は、そのデータが入力されている範囲を指定しましょう。

セルB2に「=XLOOKUP(A2,A$7:A$11,B$7:B$11)」と入力すると((1))、セルA2に入力した商品コードがセルA7～A11から検索される((2))。一致する値(ここではセルA7)が見つかると、同じ行にある戻り配列の値(セルB7)が取り出され、セルB2に表示される((3))。なお、数列を下の行へコピーできるように、検索範囲と戻り配列は複合参照で記述した。

検索値が見つからない場合に備える

XLOOKUP関数では、検索値が見つからなかった場合や未入力の場合は、エラー値「#N/A」が返されます。エラーを表示したくない場合は、第4引数の「見つからなかった場合」で表示する文字列を指定しておきましょう。この文字列は「”」で囲んで記述します。「””」と指定すれば、空欄になります。

セルB3に「=XLOOKUP(A3,A$7:A$11,B$7:B$11,””)」と入力した。セルA3の値を検索しても見つからなかった場合、セルB3にエラー値は表示されず、空欄になる。

エクセル2019以前は非対応

XLOOKUP関数は非常に便利なのですが、対応するバージョンはエクセル2021とMicrosoft 365に限られます。それ以前のバージョンでは残念ながら使えないので、代わりにVLOOKUP関数を利用しましょう。

データの検索をさらに効率化!

一度の検索で商品名と単価をまとめて取り出す

XLOOKUP関数では、第3引数の「戻り配列」で複数の列にわたる範囲を指定することもできます。そのため、一度の検索で複数の値を取り出すことが可能です。下の例では、商品コードで検索した結果から、商品名と価格を同時に取り出しています。この仕組みを利用すれば、数式を書く手間を減らすことができ、データの検索と取り出しをさらに効率アップできます。

セルB2に「=XLOOKUP(A2,A$7:A$11,B$7:C$11)」と入力すると((1))、セルA2の値がセルA7〜セルA11から検索される((2))。検索結果のうち、列Bから商品名、列Cから価格が取り出され、それぞれがセルB2とC2に表示される((3))。