関数辞典

タグ: MATCH

  • INDEX関数の使い方｜行と列の位置から値を取り出す

    INDEXは、範囲の中から「何行目・何列目」を指定して値を取り出す関数です。

    単体では地味ですが、MATCH と組み合わせることで、VLOOKUP の弱点をすべて回避した検索が組めます。この INDEX+MATCH は長年スプレッドシートの定番テクニックとして使われてきました。

    書式

    =INDEX(範囲, [行番号], [列番号])
    引数 内容
    範囲 取り出す対象の範囲
    行番号 範囲の上から何番目か
    列番号 範囲の左から何番目か。範囲が1列なら省略可

    行番号・列番号はシート全体ではなく、指定した範囲の中での位置です。ここを間違えやすいので注意してください。

    基本の使い方

    A B C
    1 商品コード 商品名 価格
    2 A-101 ボールペン 150
    3 A-102 ノート 320
    4 B-201 ハサミ 480 
    =INDEX(A1:C4, 3, 2)   → ノート

    範囲 A1:C4 の中で、上から3番目・左から2番目 ＝ B3セルの「ノート」が返ります。

    範囲を1列だけにすれば、列番号は省略できます。

    =INDEX(B1:B4, 3)   → ノート

    行番号を0にすると列全体が返る

    行番号に 0 を指定すると、その列の全体が配列として返ります。

    =INDEX(A1:C4, 0, 2)   → 商品名 / ボールペン / ノート / ハサミ

    列番号を 0 にすれば行全体です。この挙動は他の関数に範囲を渡すときに使います。

    INDEX + MATCH ── 定番の検索パターン

    INDEXの本領はここです。行番号を MATCH で求めると、検索関数になります。

    「B-201」の商品名を取り出す例：

    =INDEX(B2:B4, MATCH("B-201", A2:A4, 0))

    分解するとこうなります。

    1. 1. MATCH("B-201", A2:A4, 0) → A列の上から3番目にある、なので 3
    2. 2. INDEX(B2:B4, 3) → B列の3番目 ＝ ハサミ

    MATCHで位置を求め、INDEXでその位置の値を取る。 これだけです。

    VLOOKUPより優れている点

    左方向にも検索できます。 VLOOKUPは範囲の左端列しか検索できませんが、INDEX+MATCHは検索する列と取り出す列が独立しているため、位置関係を問いません。

    =INDEX(A2:A4, MATCH("ハサミ", B2:B4, 0))   → B-201

    列を挿入しても壊れません。 列番号を数字で書いていないためです。

    処理が軽いという利点もあります。VLOOKUPは指定した範囲全体を読みますが、INDEX+MATCHは検索列と結果列の2列しか見ません。行数が数万規模になると体感で差が出ます。

    XLOOKUPがあるなら

    XLOOKUP は INDEX+MATCH と同じことを、1つの関数で書けます。

    =XLOOKUP("B-201", A2:A4, B2:B4)

    読みやすさでは明らかにXLOOKUPが上です。新しく書くならXLOOKUPで構いません。

    INDEX+MATCHを覚えておく価値があるのは、次の場合です。

    • – 古いExcelとファイルを共有する（XLOOKUPが動かない）
    • – 既存ファイルの数式を読む・直す必要がある
    • – 行と列の両方をMATCHで求める二次元検索をしたい

    二次元の検索

    行も列も動的に決めたい場合、MATCHを2つ使います。

    =INDEX($B$2:$D$10, MATCH($F$1, $A$2:$A$10, 0), MATCH($F$2, $B$1:$D$1, 0))

    F1に行の項目、F2に列の項目を入れると、その交差点の値が返ります。クロス集計表からのピンポイント抽出はこの形が定番です。

    エラーの対処

    #REF! が出る

    指定した行番号・列番号が範囲の外を指しています。範囲が3行なのに行番号4を指定した、というケースです。MATCHの結果と範囲の開始行がずれていることも多いので、MATCH 単体の結果を別セルで確かめると原因が早く分かります。

    #N/A が出る

    INDEXではなく、中の MATCH が失敗しています。検索キーが見つかっていません。

    結果が1つずれる

    範囲の開始行のずれが原因です。MATCH を A1:A4（見出し行を含む）で取り、INDEX を B2:B4（見出しなし）で指定すると1行ずれます。両方を同じ行から始めるのが鉄則です。

    まとめ

    • – 書式は =INDEX(範囲, 行番号, 列番号)
    • – 位置はシートではなく指定範囲の中での番号
    • – INDEX(結果列, MATCH(キー, 検索列, 0)) が検索の定番形
    • – 左方向の検索・列挿入への耐性・処理の軽さが利点
    • – 新規に書くなら XLOOKUP のほうが読みやすい

    関連する関数

    • – MATCH — 位置を求める。INDEXとセットで使う
    • – VLOOKUP — 従来の検索関数
    • – XLOOKUP — INDEX+MATCHを1関数で書ける後継
    • – OFFSET — 基準セルからずらして参照する
    • – 関数一覧に戻る

  • MATCH関数の使い方｜値が上から何番目にあるかを調べる

    MATCHは、指定した値が範囲の何番目にあるかを返す関数です。値そのものではなく「位置」を返すのが特徴です。

    単体で使うことは少なく、INDEX や VLOOKUP と組み合わせて力を発揮します。

    書式

    =MATCH(検索キー, 検索範囲, [検索の型])
    引数 内容
    検索キー 探したい値
    検索範囲 探しに行く範囲。1列または1行だけ
    検索の型 0＝完全一致 / 1＝以下の最大値 / -1＝以上の最小値

    第3引数には必ず 0 を書いてください。 省略すると 1（近似一致）になり、事故のもとになります。理由は後述します。

    基本の使い方

    A
    1 東京
    2 大阪
    3 名古屋
    4 福岡 
    =MATCH("名古屋", A1:A4, 0)   → 3

    「名古屋」は範囲の上から3番目にあるので 3 が返ります。A3セルという意味ではなく、範囲の中での順番である点に注意してください。範囲を A2:A4 にすれば、同じ「名古屋」でも結果は 2 になります。

    横方向の範囲でも同じように動きます。

    =MATCH("5月", A1:M1, 0)   → 6

    INDEXと組み合わせる

    MATCHの最も一般的な使い道です。

    =INDEX(B2:B10, MATCH("B-201", A2:A10, 0))

    MATCHで「A列の何番目か」を求め、INDEXでB列の同じ位置を取り出します。これで VLOOKUP と同じ検索ができ、しかも左方向にも検索できます。詳しくは INDEX のページで解説しています。

    VLOOKUPの列番号を動的にする

    VLOOKUPの列番号を数字で直接書くと、列を挿入したときに壊れます。MATCHで求めれば壊れません。

    =VLOOKUP($E2, $A:$D, MATCH(F$1, $A$1:$D$1, 0), FALSE)

    見出し行から「取り出したい列が何番目か」を計算しているため、列の順番が変わっても追従します。横方向にコピーして複数項目を一度に引くときに特に有効です。

    第3引数を省略してはいけない理由

    省略すると 1（検索キー以下の最大値を探す）になります。このモードは範囲が昇順に並んでいる前提で動きます。

    先ほどの都市名リストで =MATCH("札幌", A1:A4) とすると、「札幌」は存在しないのにエラーにならず、何らかの数字が返ります。並び順が昇順でないため結果は予測できません。間違いに気づけないのが最大の問題です。

    近似一致には「点数から評価ランクを求める」といった正当な用途もありますが、意図して使う場合以外は 0 を明示してください。

    重複チェックに使う

    MATCHは最初に見つかった1件の位置を返します。この性質を使うと、重複の検出ができます。

    =IF(MATCH(A2, A:A, 0) = ROW(A2), "初出", "重複")

    「自分の行番号」と「最初に見つかった位置」が一致していれば初出、ずれていれば2件目以降＝重複、という判定です。列全体を範囲にしているので、位置と行番号が一致する点を利用しています。

    単に重複の有無を数えるだけなら COUNTIF のほうが簡単です。

    =IF(COUNTIF(A:A, A2) > 1, "重複あり", "")

    エラーの対処

    #N/A が出る

    見つからなかった、という意味です。原因はほぼ次のどれかです。

    表記ゆれ・余分なスペース

    「東京」と「東京 」は別物です。疑わしいときは TRIM で囲みます。

    =MATCH(TRIM(E2), A:A, 0)

    数値と文字列の食い違い

    101（数値）と "101"（文字列）は一致しません。片方が左寄せ、片方が右寄せになっていたらこれです。

    第3引数を省略している

    昇順でない範囲で近似一致モードが働き、期待した結果になっていません。

    見つからないケースを許容するなら IFERROR で包みます。

    =IFERROR(MATCH(E2, A:A, 0), "")

    結果がINDEXと1つずれる

    MATCHの範囲とINDEXの範囲で、開始行が揃っていないのが原因です。MATCHを A1:A10、INDEXを B2:B10 にすると必ず1つずれます。両方を同じ行から始めてください。

    まとめ

    • – 書式は =MATCH(検索キー, 検索範囲, 0)
    • – 返るのは値ではなく範囲の中での位置
    • – 第3引数の 0 は必ず書く
    • – INDEX + MATCH で柔軟な検索が組める
    • – VLOOKUPの列番号を動的にするのにも使える

    関連する関数

    • – INDEX — MATCHで求めた位置の値を取り出す
    • – VLOOKUP — 列番号をMATCHで動的にできる
    • – XLOOKUP — INDEX+MATCHを1関数で置き換えられる
    • – COUNTIF — 重複の判定はこちらが簡単
    • – 関数一覧に戻る