更新日：、 作成日：

エクセル XMATCH 関数：行や列から検索して見つかった位置を取得する

はじめに

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

XMATCH 関数は行や列から検索して見つかったセルの位置を取得します。「MATCH 関数」の引数を省略したり並べ替えしなくても検索できるようにした上位互換の関数です。

=XMATCH(100,A1:A3) のようにして、100 を検索して列「A」の何番目の位置にあるか取得できます。

=XMATCH("エクセル",A1:C1) のようにして、エクセル を検索して行「1」の何番目の位置にあるか取得できます。

=IFERROR(XMATCH(100,A1:A3),0) のようにして、検索結果が見つからないときに 0 を表示できます。

「MATCH 関数」との違いは、引数「一致モード」を省略したときに同じ値を検索するかどうかです。

複数行や複数列から検索できます。

セルを検索する方法は「文字列やセルを検索する」をご覧ください。
この関数は 365 または 2021 から使用できます。

XMATCH 関数の引数

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

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

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

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

引数「検索値」

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

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

使い方

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

1 列から検索値と同じ値のセルの位置を取得する

値の列から検索値と同じ エクセル のセルの位置を取得します。引数「一致モード」に 0 や 2 を指定して、引数「検索値」と同じ値を検索できます。

=XMATCH(B8,$B$3:$B$5)
=XMATCH(B9,$B$3:$B$5,2)

取得される値は、一番上のセルを基準とした 1 からの連番です。

11

1 行から検索値と同じ値のセルの位置を取得する

値の行から検索値と同じ エクセル のセルの位置を取得します。引数「一致モード」に 0 や 2 を指定して、引数「検索値」と同じ値を検索できます。

=XMATCH(B5,$C$2:$E$2)
=XMATCH(B6,$C$2:$E$2,2)

取得される値は、一番左のセルを基準とした 1 からの連番です。

12

特定の文字が入っているセルの位置を取得する

ワイルドカードを使用して ワード が入っているか検索して、そのセルの位置を取得します。引数「一致モード」に 2 を指定して、ワイルドカードを使用できます。

=XMATCH("*ワード*",B3:B6,2)
=XMATCH("ワード*",B3:B6,2)
=XMATCH("*ワード",B3:B6,2)

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

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

13

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

検索結果が見つからないときに 0 を表示します。「IFERROR 関数」を使用して、エラーのときの値を表示できます。

=IFERROR(XMATCH(B8,B3:B5),0)
=XMATCH(B8,B3:B5)

MATCH 関数は検索結果が見つからないときにエラーになります。

14

値が基準値以下の最大値を検索する

基準値が 40 のとき、値が 40 以下の最大値のセルの位置を取得します。引数「一致モード」に -1 を指定して、引数「検索値」以下の最も近い値を検索できます。

=XMATCH(B8,$B$3:$B$5,-1)

日付ならその日以前の一番近い日付になります。

引数「検索範囲」が昇順に並べ替えられている必要があります。

15

値が基準値以上の最小値を検索する

基準値が 5 のとき、値が 5 以上の最小値のセルの位置を取得します。引数「一致モード」に 1 を指定して、引数「検索値」以上の最も近い値を検索できます。

=XMATCH(B8,$B$3:$B$5,1)

日付ならその日以降の一番近い日付になります。

引数「検索範囲」が降順に並べ替えられている必要があります。

16

高度な使い方

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

複数条件で検索する

名前とバージョンを検索して両方に一致するセルの位置を取得します。

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

=XMATCH(B7&","&C7,B3:B5&","&C3:C5)
17

複数列、複数行から検索する

複数行または複数列から検索するには「IFERROR 関数」の中に XMATCH 関数を入力します。見つからないでエラーになるときに次の行や列から検索できます。

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

複数列から値が 40 のセルの位置を取得します。/p>

=IFERROR(XMATCH(B8,$B$3:$B$5),XMATCH(B8,$C$3:$C$5))
18

解説

取得するセルの位置は引数「検索範囲」の一番左または一番上のセルを基準とした 1 からの連番です。10, 20, 30 の中から 20 を検索すると 2 が取得されます。

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

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

引数「検索値」が引数「検索範囲」から見つからないときはエラー #N/A になります。

文字列を検索するには、引数「一致モード」を 0 や 2 にします。一致する文字列を検索できます。

引数「一致モード」が 0 や 2 なら、文字列の大文字と小文字は区別しません。"ABC" と "abc" は等しいです。

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

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

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

XMATCH と MATCH の違い

XMATCH 関数と「MATCH 関数」の違いは引数「一致モード」の動作にあります。

  • XMATCH 関数：引数「一致モード」を省略すると検索する値と同じ値と一致します。1 または -1 のとき、引数「検索範囲」が並べ替えられている必要はありません。
  • MATCH 関数：引数「一致モード」を省略すると検索する値と同じまたは、それ以下の最大値と一致します。1 または -1 のとき、引数「検索範囲」が昇順や降順に並べられている必要があります。

同じ値を検索することが多いので使用できるバージョンなら XMATCH 関数を使用します。入力する引数が 1 つ少なくなります。

取得した位置の使い道

「INDEX 関数」や「VLOOKUP 関数」のように、引数に列番号や行番号を入力する関数と組み合わせます。

「XLOOKUP 関数」のように好きな列や行から検索するのを従来の関数で行うには「INDEX と MATCH 関数を組み合わせる」をご覧ください。

著者：Tipsfound