=MATCH(E2,A2:A6,0)は、A2:A6の中でのE2の値の位置を返します。最初のセルなら1、2番目なら2という具合です。0は完全一致を求めるという意味です。MATCHが返すのは値そのものではなく数値なので、たいていはINDEXと組み合わせて使います。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Position | |
| 2 | Apple | Fruit | $1.20 | Bread | 4 | |
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
BreadはA2:A6の4番目のセルなので、F2には4と表示されます。数え始めるのはシートの1行目ではなく範囲の最初のセルです。範囲をA1:A6に変えると、答えは5になります。E2にKiwiと入力すると、MATCHは#N/Aを返します。
MATCH関数の構文と照合の種類
=MATCH(lookup_value, lookup_array, [match_type])
| match_type(照合の種類) | 探すもの | リストの並び |
|---|---|---|
0 | lookup_valueと完全に等しい最初の値。文字列ではワイルドカードを使えます。 | 順不同でよい |
1または省略 | lookup_value以下の最大の値。 | 昇順 |
-1 | lookup_value以上の最小の値。 | 降順 |
既定値は0ではなく1です。並べ替えていない名前のリストで3つ目の引数のないMATCHを使っても、エラーにはなりません。別の行のものかもしれない位置を返すだけです。文字列、コード、IDを検索するときは、毎回0を書いてください。
近似一致: 値がどの区分に入るか
照合の種類が1のとき、MATCHは「どの区分か」に答えます。値が到達した最後のしきい値の位置です。ここでは75点が0、60、70を超えているので、区分3、成績Cになります。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Min score | Grade | Score | Band | Grade | |
| 2 | 0 | F | 75 | 3 | C | |
| 3 | 60 | D | 59 | 1 | F | |
| 4 | 70 | C | 90 | 5 | A | |
| 5 | 80 | B | 100 | 5 | A | |
| 6 | 90 | A |
59はまだ60未満なので、区分1(F)です。90は最後のしきい値と完全に一致し、100はそれを超えているので、どちらも区分5(A)になります。しきい値は昇順のままにしておく必要があります。それによってMATCHが正しい場所で止まれるからです。
照合の種類-1は、大きい順に並べたリストに対する鏡写しの動きです。検索値以上で最小の値の位置を返します。列にサイズ{50;25;12;5}があるとき、=MATCH(18,D2:D5,-1)は2を返します。18が入る最小のサイズである25です。
ワイルドカードと大文字・小文字
MATCHは大文字と小文字を区別せず、照合の種類が0のときはワイルドカード*(任意の文字)と?(ちょうど1文字)を受け付けます。大文字と小文字を区別して一致させるには、リストをEXACTで比べて最初のTRUEを探します。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Category | Price | Starts with "C" | 3 |
| 2 | Apple | Fruit | $1.20 | Lower case "milk" | 5 |
| 3 | Pear | Fruit | $1.50 | Exactly "milk" | #N/A |
| 4 | Carrot | Vegetable | $0.80 | ||
| 5 | Bread | Bakery | $2.40 | ||
| 6 | Milk | Dairy | $1.10 |
"C*"はCarrotに一致し、位置は3です。大文字と小文字は区別されないので、"milk"は5番目のMilkを見つけます。EXACT版は各名前を大文字小文字まで含めて「milk」と比べ、TRUEが見つからないので#N/Aを返します。A6をmilkに変えると5を返します。Excel 2019以前では、EXACTの数式をCtrl+Shift+Enter(MacではCmd+Shift+Enter)で確定します。Excel 2021とMicrosoft 365ではEnterだけで十分です。チルダを付けるとワイルドカードが普通の文字になります。"~*"は本物のアスタリスクを探します。
値がリストにあるか調べる
MATCHは値が見つかれば数値を、見つからなければ#N/Aを返します。ISNUMBERでそれをTRUEかFALSEに変えれば、「リストにあるか」をすばやく判定できます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ordered | In stock? | In stock | |
| 2 | Pear | Yes | Apple | |
| 3 | Kiwi | No | Pear | |
| 4 | Milk | Yes | Bread | |
| 5 | Plum | No | Milk | |
| 6 | Bread | Yes |
Pear、Milk、BreadはD2:D5にあるので、Yesと表示されます。KiwiとPlumはNoです。COUNTIF($D$2:$D$5,A2)>0でも同じ答えになります。MATCHは最初に見つかったところで止まり、COUNTIFはすべてを数えます。
MATCH、XMATCH、INDEX
MATCHは、INDEXとMATCHのページで説明している定番の検索=INDEX(C2:C6,MATCH(E2,A2:A6,0))の半分を担っています。Excel 2021とMicrosoft 365にはXMATCHが加わりました。既定で完全一致になり、最後の項目から上へ検索でき、降順に並べなくても「次に大きい値」を扱えます。それでもMATCHはどこでも動くので、Excel 2019で開く必要があるファイルでも使えます。
練習: 社員の位置
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Start year | Name | Position | |
| 2 | Ana | Sales | 2019 | Dev | ||
| 3 | Ben | Support | 2021 | |||
| 4 | Cara | Sales | 2018 | |||
| 5 | Dev | Finance | 2022 | |||
| 6 | Eli | Support | 2020 | |||
| 7 | Fay | Finance | 2023 |
やってみよう: F2で、E2の名前がA列のリストの何番目にあるかを返してください。
よくある質問
ExcelのMATCHは何を返しますか？
値ではなく位置を返します。BreadがA2:A6の4番目のセルなら、=MATCH("Bread",A2:A6,0)は4を返します。INDEXと組み合わせると、別の列のその位置の値を取り出せます。
照合の種類0、1、-1の違いは何ですか？
0は順不同のリストで完全一致を探します。1(既定値)は検索値以下の最大の値を探し、リストが昇順に並んでいる必要があります。-1は検索値以上の最小の値を探し、リストが降順に並んでいる必要があります。
MATCHは大文字と小文字を区別しますか？
区別しません。=MATCH("milk",A2:A6,0)はMilkを見つけます。区別して一致させるには=MATCH(TRUE,EXACT(A2:A6,"milk"),0)を使います。Excel 2019以前では、Ctrl+Shift+Enter(MacではCmd+Shift+Enter)で確定する必要があります。
MATCHで値がリストにあるかどうかを調べるには？
値がないとMATCHは#N/Aを返すので、数値が返ったかどうかを判定します：=ISNUMBER(MATCH(A2,$D$2:$D$5,0))はTRUEかFALSEを返します。自分で決めた文字列にするならIFで囲みます。
