【Excel】MATCH関数で値の位置を探す|範囲内の順番と行番号の違い

Excelで「この商品コードは一覧の何番目にあるか」を知りたいときは、MATCH関数を使います。完全一致で探す基本形は=MATCH(E2,B2:B6,0)です。E2の値がB2:B6の何番目にあるかを返します。

返る数値はシート全体の行番号ではありません。検索範囲がB2から始まるなら、B4に見つかった値は「3番目」です。位置を探す式と、その位置の別の列の値を取り出す式を、小さな表で確認します。

1. MATCHが返すのは範囲内の相対位置

【要点】MATCHの結果1は検索範囲の先頭です。構文は=MATCH(検査値,検査範囲,照合の種類)です。検査範囲には1列または1行の範囲を指定します。

B2:B6に上からP10、P20、P30、P20、P40が入っているとします。E2をP30にして=MATCH(E2,B2:B6,0)と入力すると、結果は3です。B2が1番目、B3が2番目、B4が3番目だからです。

シートの行番号が欲しい場合は、=MATCH(E2,B2:B6,0)+ROW(B2)-1とします。開始行が2なので、3+2-1で4です。検索範囲がB10からならROW(B10)を使います。「いつも1を足す」と覚えると、範囲が変わったときに間違えます。

横方向のB2:F2を探す場合も、返るのは左から何番目かです。行番号と列番号を混同しないよう、出力先に「範囲内の位置」など意味の分かる見出しを付けておくと便利です。

2. 完全一致を使うときは最後の0を省略しない

1. 検索する商品コードをE2へ入力します。
2. 結果を出すセルへ=MATCH(E2,$B$2:$B$6,0)を入力します。
3. E2がP30なら3になることを確認します。
4. E2を存在しないP99へ変え、#N/Aになることを確かめます。
5. 別の行にも式をコピーする場合は、検索範囲を$で固定します。

第3引数の0は、完全一致で探す指定です。0を省くと既定値の1になり、昇順に並べた範囲での近似一致になります。未登録のコードを見つけたい業務で、引数を省略するのは適切ではありません。

文字を式へ直接書くなら=MATCH("P30",B2:B6,0)のように引用符で囲みます。E2のようなセル参照は囲みません。"E2"と書くと、E2の中身ではなくE2という文字を探します。

同じコードが複数ある場合、0の検索で返るのは最初の一致です。この例のP20なら2で、次のP20の位置4は返りません。重複が存在するかを別途調べたい場合は、件数を確認する処理を併用します。

検索範囲の途中に空白セルがあっても、位置を数えるときはそのセルを飛ばしません。B2がP10、B3が空白、B4がP30でも結果は3です。また、フィルターで隠れた行も、指定範囲に含まれていれば通常のMATCHの検索対象になります。

3. 0・1・-1の照合は目的と並べ方が違う

例えばB2:B4が10、20、30なら、=MATCH(25,B2:B4,1)は20の位置2を返します。25以下の最大値が20だからです。B2:B4を30、20、10の降順にした場合、=MATCH(25,B2:B4,-1)は30の位置1になります。

近似一致は点数別ランクや数量別料金など、境界を調べる表に向いています。商品IDの存在確認とは目的が違います。元表を並べ替える必要があるときは、その列だけをソートして隣の品名と対応を壊さず、表全体の行の関係を保ちます。

並べ方の前提を満たさない近似一致は、期待する位置を返す保証がありません。エラーが出なかったというだけで正しいと判断せず、境界の前後の数値で確認します。

4. INDEXを組み合わせて同じ位置の値を取り出す

A2:A6に品名、B2:B6に商品コードがあるなら、E2の商品コードに対応する品名を=INDEX(A2:A6,MATCH(E2,B2:B6,0))で取り出せます。MATCHが3を返せば、INDEXはA2:A6の3番目にあるA4を読みます。

検索列より左の値も取得できますが、範囲の開始行はそろえます。検索がB2:B6なのに、取得範囲をA1:A5にすると、同じ3番目でもA3を読んでしまいます。結果が文字として表示されるので、エラーにならず誤った品名を返す点に注意してください。

結果が合わないときは、まずMATCHだけを別セルで計算して位置を確認し、その位置が取得範囲でどのセルになるか数えます。検索列と取得列の行数、見出しの含め方、末尾の明細を順に点検すると原因を追いやすくなります。

データを追加したときに固定範囲の外へ商品が増えたら、式の範囲も見直します。INDEXとMATCHを使えば、列や行の変更に無条件で壊れなくなるわけではありません。削除や並べ替えの後も、既知の商品で対応を確認します。

5. 見つからない値は型・空白・記号を確認する

#N/Aは、指定条件で一致が見つからないことを示します。数値の123と文字列の「123」は区別される場合があるので、検索値と一覧の両方をISNUMBERやISTEXTで調べます。

商品コード00123を数値化すると123になり、識別に必要な0を失います。金額なのかコードなのかで型を決め、むやみにVALUEで全列を変換しません。表示形式で00000と見せた数値と、文字列の00123も、見た目だけでは判別できないため確認が必要です。

前後の半角スペースはTRIMで整理できますが、全角スペースや改行まで一律に消えるわけではありません。補助列で変換結果を確認し、名称の途中に必要な空白を削らないようにします。

また、MATCHは英字の大文字と小文字を区別しません。照合の種類が0で文字列を探す場合、*は任意の文字列、?は任意の1文字を表すワイルドカードです。文字そのものの*を探すなら=MATCH("~*",B2:B6,0)のように~を付けます。「完全一致なら記号も含めてすべて厳密に区別する」とは考えないでください。

見つからない場合だけ「未登録」と表示したいなら、=IFNA(MATCH(E2,B2:B6,0),"未登録")とできます。先に範囲や型を確認し、#REF!など別の問題までIFERRORでまとめて隠さない方が原因に気付きやすくなります。

6. まとめ：既知の値・未登録・重複で試す

MATCHは、値を取り出す前の「どこにあるか」を調べる関数です。商品コードなどを探すときは最後に0を指定し、返った位置が検索範囲の先頭からの順番であることを確認します。

完成した式では、先頭、途中、末尾の既知の値と、存在しない値を一つずつ試します。重複があれば最初の行でよいかも確認してください。INDEXを重ねる場合は、取得範囲と検索範囲の開始行をそろえます。

照合の定義とワイルドカードの扱いはMicrosoftのMATCH関数の説明で確認できます。式を長くする前にMATCHだけの結果を確かめると、行のずれを見つけやすくなります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】