エクセル XMATCH 関数：行または列の中にある検索する値の位置を取得する

はじめに

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

XMATCH 関数は行または列の中にある検索する値の位置を取得します。

1 行か 1 列の範囲に指定した値が含まれているか検索したいときに使用します。複数条件や複数列から取得や、エラーを回避する方法も紹介します。

「MATCH 関数」の上位関数です。並び替えしていなくても検索できます。

この関数は 365 または 2021 から使用できます。

目次
- XMATCH 関数
- 使用例
- 解説

XMATCH 関数

XMATCH(検索値, 検索範囲)
検索範囲の中から検索値と同じ値が何番目のセルにあるのかを取得します。

XMATCH(検索値, 検索範囲, 一致モード, 検索モード)
一致モードで検索値と近いデータを取得するように指定できます。検索モードで検索範囲を逆順に検索するように指定できます。

引数「検索値」	検索する値を、数値、文字列、セル参照、関数などで指定します。引数「一致モード」が 2 のときだけ、ワイルドカードを使用できます。
引数「検索範囲」	検索対象となるデータの範囲を 1 行または 1 列で指定します。
引数「一致モード」	省略できます。 0 または省略：検索する値と同じ値と一致します。 1：検索する値と同じまたは、それ以上の最小値と一致します。 -1：検索する値と同じまたは、それ以下の最大値と一致します。 2：検索する値と同じ値と一致します。ワイルドカードを使用できます。
引数「検索モード」	省略できます。 1 または省略：引数「検索範囲」が 1 列なら上から下へ、1 行なら左から右へ検索します。 -1：引数「検索範囲」が 1 列なら下から上へ、1 行なら右から左へ検索します。

使用例

1 列から検索する値の位置を取得する。

=XMATCH(B7,$B$2:$B$4)

1 行から検索する値の位置を取得する。

=XMATCH(B7,$B$2:$D$2)

引数「一致モード」に「1」を指定して、検査値以上の最小値の位置を取得する。

=XMATCH(B7,$B$2:$B$4,1)

引数「一致モード」に「-1」を指定して、検査値以下の最大値の位置を取得する。

=XMATCH(B7,$B$2:$B$4,-1)

エラー処理

検索する値が見つからないときはエラーになります。見つからないときに好きな値を表示するには「IFERROR 関数」を使用します。

=IFERROR(XMATCH(B7,$B$2:$B$4),"エラー")

"エラー" の部分を "" にすると空にできます。 0 にすると 0 を表示します。

複数列、複数行から取得する

引数「検索範囲」に複数行または複数列の範囲を指定したいときは「IFERROR 関数」を使用します。「IFERROR 関数」の引数「エラーの処理」に 2 つ目の「XMATCH 関数」を入力して 2 列、3 列と増やしていけます。

=IFERROR(XMATCH(B7,$B$2:$B$4),XMATCH(B7,$C$2:$C$4))

2 列：
=IFERROR(XMATCH(1 列目),XMATCH(2 列目))
3 列：
=IFERROR(XMATCH(1 列目),IFERROR(XMATCH(2 列目),XMATCH(3 列目)))

複数条件を指定する

次のようなデータがあります。「名前」の位置を取得するとき、一致するデータが複数あるため一つに絞り込めません。

一つに絞り込むために検索する値を複数にし、「名前」と「バージョン」を結合した値で検索します。

B3&C3

引数「検索値」に名前とバージョンを結合した値を指定します。引数「検索範囲」に名前とバージョンの列を結合した範囲を指定して複数条件に対応できます。

=XMATCH(B9&C9,$B$3:$B$6&$C$3:$C$6)

セルの範囲を結合できるのはスピルという機能によるものです。

詳しくは「スピル、動的配列数式の使い方」をご覧ください。

解説

引数「検索値」が見つかったときに、引数「検索範囲」の何番目の位置にあるかを取得します。 10, 20, 30 の中から 20 を検索すると 2 が取得されます。

引数「検索値」の大文字と小文字は区別しません。 "ABC" と "abc" は同じものとして一致します。

引数「検索値」が存在しないときはエラー #N/A になります。

引数「検索範囲」に 1 行または 1 列だけ指定します。

引数「検索範囲」のデータは並べ替えられている必要はありません。

引数「検索範囲」に複数の行列を指定するとエラー #VALUE! になります。

引数「一致モード」に 1 を指定したときに 10, 20, 30 の中から 15 を検索すると、15 以上の最小値である 20 と一致し 2 が取得されます。

引数「一致モード」に -1 を指定したときに 10, 20, 30 の中から 15 を検索すると、15 以下の最大値である 10 と一致し 1 が取得されます。

引数「一致モード」に 2 を指定したとき、引数「検索値」にワイルドカードを使用できます。使用できるワイルドカードには次のものがあります。

パターン	説明	使用例	一致例
*	任意の長さの文字	"あ*"	あ, あい, あいう
?	任意の 1 文字	"あ?"	あい, あか, あき
~	ワイルドカードの文字 * ? を検索する	"あ~?~*"	あ?*

=XMATCH(B7,$B$2:$B$4,2)

引数「検索モード」は一致する値が複数あるときに使用します。通常は上または左から検索したいので省略します。

MATCH 関数との違い

「MATCH 関数」の引数「照合の型」に 1 または -1 を指定したときは、引数「検索範囲」が昇順や降順に並べ替えられている必要があります。

「XMATCH 関数」で同様に「一致モード」に 1 または -1 を指定したときは、並べ替えられている必要はありません。

取得した位置の使い道

取得した位置をどうするかは「VLOOKUP 関数より高度に検索する」のように「INDEX 関数」と組み合わせて検索するために使用できます。

ただし「XLOOKUP 関数」を使用してそれを実現できます。