エクセル XLOOKUP 関数：検索して一致した値に対応する行や列のセルを取得する

はじめに

エクセルの XLOOKUP 関数の使い方を紹介します。

XLOOKUP 関数は検索して一致した値に対応する行や列のセルを取得します。「VLOOKUP 関数」や「HLOOKUP 関数」を好きな行や列から検索できるようにした上位互換の関数です。

=XLOOKUP(100,A1:A3,B1:B3) のようにして、「VLOOKUP 関数」のように列「A」から 100 を検索して一致する行の列「B」の値を取得できます。

=XLOOKUP(100,A1:C1,A2:C2) のようにして、「HLOOKUP 関数」のように行「1」から 100 を検索して一致する列の行「2」の値を取得できます。

=XLOOKUP(A1&","&B1,A2:A4&","&B2:B4,C2:C4) のようにして、複数条件で列「A」と「B」の両方に一致する列「C」の値を取得できます。

見つからないときに 0 にしたり、複数該当するときにすべての値を取得できます。

XLOOKUP 関数の引数

XLOOKUP(検索値, 検索範囲, 結果範囲)
検索値を検索範囲のデータと比較し、一致した行または列に対応するセルを結果範囲から取得します。

XLOOKUP(検索値, 検索範囲, 結果範囲, 見つからない場合, 一致モード, 検索モード)
検索値が見つからないときは見つからない場合の値を返します。一致モードで検索値と近いデータを取得するように指定できます。検索モードで検索範囲を逆順に検索するように指定できます。

スピル化：セルの範囲や配列を指定すると結果が「スピル」します。

結果がセルの範囲になるときは、セルの範囲や配列を指定してもその通りにはスピルされません。

引数「検索値」

引数「一致モード」が 2 のときに使用できるワイルドカードには次のものがあります。

使い方をわかりやすく

XLOOKUP 関数の引数を「VLOOKUP 関数」のように検索するのに、何を指定するのかわかりやすく解説します。「HLOOKUP 関数」のように検索するには「行」と「列」を入れ替えて読んでください。

引数「検索値」、何を検索しますか？

次のようなデータがあります。この中から何を検索したいですか？

No. を検索して名前を取得したい。名前を検索してバージョンを取得したい。その検索する値を引数「検索値」に指定します。検索する値がセル「B8」に入っているのなら次のように入力します。

引数「検索範囲」、どこから検索しますか？

No. を検索するなら、その範囲の列を引数「検索範囲」に指定します。次のように入力します。

名前を検索するなら次のように入力します。

引数「結果範囲」、どの値を取得しますか？

No. から名前を取得するなら、その名前の範囲を引数「結果範囲」に指定します。次のように入力します。

バージョンを取得するなら次のように入力します。

これで結果を取得できます。以降の引数はすべて省略できます。

引数「検索モード」

逆から検索しますか。

省略するか 1 を指定すると、引数「検索範囲」の上または左から検索します。

-1 を指定すると引数「検索範囲」の下または右から検索します。

該当する値が複数あるときに、最後に一致する値を取得するのに使用します。通常は上または左から検索したいので省略します。

使い方

XLOOKUP 関数の使い方を紹介します。

No. を検索して名前を取得する

No. が 1 の行の名前を取得します。引数「検索範囲」を 1 列にすると「VLOOKUP 関数」のように検索できます。

HLOOKUP 関数のように検索する

No. が 1 の列の名前を取得します。引数「検索範囲」を 1 行にすると「HLOOKUP 関数」のように検索できます。

特定の文字が入っている名前を検索する

ワイルドカードを使用して、名前に ワード が入っている行の個数を取得します。引数「一致モード」に 2 を指定して、ワイルドカードを使用できます。

名前が ワード で始まる値は "ワード*" のように入力します。

名前が ワード で終わる値は "*ワード" のように入力します。

見つからないときに 0 にする

検索結果が見つからないときに 0 を表示します。

引数「見つからない場合」を省略したときはエラーになります。

セルの範囲を取得する

No. が 1 の行のバージョンと個数を取得します。引数「結果範囲」を複数列にしてセルの範囲を取得できます。

結果がセルの範囲になるので「SUM 関数」などの引数に指定できます。

別シートを検索する

シート「Sheet2」に検索と取得したいデータがあります。

シート「Sheet2」の No. が 2 の行の名前を取得します。

別シートを参照するには シート名!セル名 のように入力します。セル名の前に シート名! を付けると、そのシートのセルを参照できます。

セル参照を入力するのと同じように別シートのセルをクリックして簡単に Sheet2!B3:B5 のように入力できます。

高度な使い方

XLOOKUP 関数と他の関数を組み合わせた使い方などを紹介します。

複数条件で検索する

名前とバージョンを検索して両方に一致する個数を取得します。

複数の列を条件にするには、引数「検索値」に名前とバージョンを区切り文字を付けて結合した値を入力します。引数「検索範囲」に名前とバージョンの列を区切り文字を付けて結合した範囲を指定して複数条件に対応できます。

見出しで検索して交差する値を取得する

行見出しと列見出しを検索条件にして交差するセルを取得します。

数式の解説をします。見出しで検索するには XLOOKUP 関数を入れ子にします。

2 つ目の XLOOKUP 関数で列見出しの検索をします。引数「結果範囲」に取得したいデータの範囲を入力します。

結果は、一致した列の範囲 C3:C5 のようになります。

1 つ目の XLOOKUP 関数で行見出しを検索します。引数「結果範囲」に列見出しを検索した結果を入力します。

これで行見出しと列見出しの交差する値を取得できます。

複数該当するときすべての値を取得する

一致する値が複数あるとき、すべての該当する値を取得するには「FILTER 関数」を使用します。

解説

引数「検索値」の大文字と小文字は区別しません。"ABC" と "abc" は等しいです。

引数「検索範囲」に 1 列だけ指定します。データが並べ替えられている必要はありません。

引数「検索範囲」が複数列のときはエラー #VALUE! になります。

引数「結果範囲」は引数「検索範囲」の行数に合わせる必要があります。行数が合っているなら 1 列でも複数列の範囲でも指定できます。

引数「結果範囲」が 1 列なら、対応する行のセルの値を取得します。複数列なら、対応する行のセルの範囲を取得します。

引数「結果範囲」と引数「検索範囲」の行数が合っていないときはエラー #VALUE! になります。

検索結果が見つからないときは、引数「見つからない場合」を返します。

検索結果が見つからないときに引数「見つからない場合」を省略しているときは、エラー #N/A になります。

引数「一致モード」が 1 なら 10, 20, 30 の中から 15 を検索すると、15 以上の最小値である 20 と一致します。

引数「一致モード」が -1 なら 10, 20, 30 の中から 15 を検索すると、15 以下の最大値である 10 と一致します。

引数「一致モード」が 2 なら、引数「検索値」にワイルドカードを使用できます。