ExcelのINDEX関数は、表やセル範囲の中から「上から何行目、左から何列目」という位置を指定し、その値またはセル参照を返します。単独でも使えますが、MATCH関数で検索値の位置を求めてINDEXへ渡すと、検索列の左右に制約されない柔軟な検索式になります。うまくいかない原因の多くは、INDEXの対象範囲とMATCHの検索範囲の開始行がずれている、MATCHの照合方法を省略した、行番号と列番号を取り違えた、という構造の不一致です。まず小さな表で位置を数え、各関数を別セルで確認してから組み合わせます。
INDEX関数の役割を位置指定として理解する
Microsoftの公式説明では、INDEXは表または範囲から、数値のインデックスで選んだ要素の値または参照を返す関数です。配列形式の基本構文はINDEX(array, row_num, [column_num])です。arrayがB2:E10なら、INDEX(B2:E10,3,2)はその範囲内の3行目・2列目、つまりワークシート上のC4にある値を返します。
row_numとcolumn_numはシート全体の行番号・列番号ではなく、指定範囲の左上を1行目・1列目として数えます。B2:E10の「3行目」は4行目のことです。ここを混同するとエラーにならず別の値を返すため、気付きにくい誤りになります。数式を作る前に対象範囲へ色を付けるか、数式バーで範囲を選択し、左上から目的セルまでの相対位置を確認します。
一列・一行・二次元の指定を使い分ける
対象が一列だけなら列番号を省略でき、INDEX(D2:D100,5)はD2:D100の5番目を返します。一行だけなら行番号と列番号の扱いが分かりにくくなるため、引数ヒントを確認し、必要に応じて列番号まで明示します。二次元表では行番号と列番号の両方が必要です。表の見出し行をarrayへ含めるかどうかで、数える位置が一つ変わります。
行番号または列番号へ0を指定すると行全体・列全体を配列として返せる使い方がありますが、結果の展開方法はExcelの版や数式の入力方法によって見え方が異なります。まず単一セルを返す基本形を完成させます。Microsoft 365など動的配列対応版で複数値を返す場合は、展開先が空いているか、テーブル内で利用していないかを確認し、古いExcelとの互換性を検証します。
MATCHで検索値の位置を求める
MATCH関数は、検索値そのものではなく、検索範囲内での相対位置を返します。たとえばA2:A100の商品コードからG2のコードを完全一致で探す式はMATCH(G2,A2:A100,0)です。結果が7なら、A2:A100の7番目に一致したことを示します。この7を同じ開始行を持つD2:D100のINDEXへ渡すと、対応する価格などを取り出せます。
=MATCH(G2,$A$2:$A$100,0)
=INDEX($D$2:$D$100,MATCH(G2,$A$2:$A$100,0))MATCHの第3引数0は完全一致です。1または省略時は原則として昇順、-1は降順という並び順を前提とする近似一致なので、未整列のコード表で省略すると意図しない位置を返す可能性があります。商品コード、社員番号、注文番号のように同一値を探す用途では0を明示します。前後の空白、全角・半角、数値と文字列の違いは見た目が同じでも不一致になるため、元データも確認します。
INDEXとMATCHの範囲の高さをそろえる
INDEX/MATCHで最重要なのは、検索範囲と戻り範囲の行対応です。検索範囲がA2:A100なら戻り範囲もD2:D100のように同じ開始行・終了行へそろえます。検索範囲をA:A、戻り範囲をD2:D100にすると、MATCHが返す位置の基準が違い、範囲外エラーや一行ずれが起きます。見出しを片方だけ含めるのも同じ問題です。
一行ずれは正常な値に見えるため、先頭、中央、末尾の少なくとも三件で手作業の照合を行います。重複コードがある場合、MATCHは条件に合う最初の位置を返すため、「最新行」や「最後の行」を取る目的にはそのまま使えません。重複を禁止するデータなら条件付き書式やCOUNTIFで検査し、重複が正当なら日付など追加条件を設計します。式だけで曖昧な業務ルールを隠さないようにします。
縦横二つのMATCHで交点を返す
行見出しと列見出しがあるクロス表では、行位置と列位置を別々のMATCHで求めます。A2:A20に商品名、B1:F1に月、B2:F20に売上があり、H2の商品とH3の月の交点を返すなら、INDEXのarrayは値だけのB2:F20です。行MATCHはA2:A20、列MATCHはB1:F1を検索し、どちらも完全一致0を指定します。
=INDEX($B$2:$F$20,
MATCH($H$2,$A$2:$A$20,0),
MATCH($H$3,$B$1:$F$1,0))月見出しが日付の場合、H3も文字列の「1月」ではなく同じ日付シリアル値にし、表示形式だけを月表示へそろえます。結合セルの見出しや途中の空白列は位置対応を分かりにくくします。クロス表をデータ入力元にするなら、見出しを一行・一列に整理し、各交点へ一つの値を置きます。数式はまず二つのMATCHを別セルで評価し、正しい行番号・列番号が得られてからINDEXへ組み込みます。
参照形式と複数領域のarea_numを理解する
INDEXには配列形式のほか、複数の参照から指定したセル参照を返す参照形式があります。構文はINDEX(reference, row_num, [column_num], [area_num])です。referenceへ複数領域を与えた場合、area_numで何番目の領域を使うか指定します。通常の一つの表から値を取るだけなら配列形式で十分であり、複数領域を無理にまとめる必要はありません。
参照形式は、INDEXの結果をSUMなど参照を受け取る関数へ渡す高度な式で役立ちます。一方、離れた範囲の括弧や区切りを誤ると、期待しない領域を選びます。部門ごとに同じ形の表が分散しているなら、area_numを変える巨大式より、テーブルを縦に統合して部門列を持たせる方が監査しやすい場合があります。既存モデルを変更するときは依存数式と名前定義を確認します。
エラーを関数ごとに分解して調べる
#N/AはMATCHが一致を見つけられない場合によく出ます。検索値の前後空白、数値と文字列、全角・半角、見えない文字、検索範囲の誤りを確認します。#REF!はINDEXへ渡した行番号・列番号が対象範囲を超えた場合などに出ます。#VALUE!は引数の型や配列指定が不正な場合に起き得ます。IFERRORで隠す前に内側のMATCHだけを評価します。
「数式」「数式の検証」を使うと、入れ子の式を段階的に確認できます。F9による部分評価は数式バーで選択した部分を計算できますが、そのままEnterを押すと式の一部が値へ置換されることがあるため、確認後はEscで戻ります。IFNAで「未登録」と表示する場合も、検索範囲の誤りまで未登録として隠さない設計が必要です。異常件数を別セルで集計し、データ更新後に増えていないか確認します。
絶対参照とテーブル参照でコピーずれを防ぐ
数式を下方向へコピーするとき、検索表の範囲は$A$2:$A$100や$D$2:$D$100のように絶対参照で固定し、各行の検索値G2だけを相対参照にします。F4キーで参照形式を切り替えられます。列方向にもコピーする場合は、行だけ・列だけを固定する複合参照を使います。固定し過ぎると検索値まで同じセルを参照するため、コピー後の数式を先頭と末尾で確認します。
データをExcelテーブルにすると、構造化参照で列名を使え、行追加時に範囲が自動拡張します。ただし列名変更は式へ反映され、外部ブックや古いExcelとの互換性も考慮が必要です。全列参照は簡単ですが、大量の複雑式では再計算負荷が増えることがあります。必要範囲またはテーブルを使い、揮発性関数や重複計算を減らし、計算方法を手動へ変えて問題を隠さないようにします。
XLOOKUPやXMATCHとの使い分けを決める
現在のMicrosoft 365や新しいExcelでは、XLOOKUPやXMATCHも利用できます。XLOOKUPは検索範囲と戻り範囲を明示でき、完全一致が既定なので、多くの一方向検索を読みやすくできます。一方、INDEX/MATCHは古い対応版が広く、二次元の交点、参照を返す式、既存ブックとの互換性で引き続き有用です。利用者のExcel版と共有先を確認して選びます。
新しい関数へ一括置換する前に、重複時の結果、見つからない場合、近似一致、検索方向、空白と0の扱いをテストします。INDEX/MATCHが動いているなら、単に短い式へする目的で本番ブックを直接変更しません。代表データと境界値を含むコピーで新旧結果を比較し、差異が0件であることを確認します。関数名よりも、検索キーの一意性と範囲対応を文書化することが重要です。
確認チェックリスト
- INDEXの範囲内で目的セルが何行目・何列目かを数えたか
- 検索範囲と戻り範囲の開始行・終了行をそろえたか
- 完全一致が必要なMATCHで第3引数0を指定したか
- 見出しを範囲へ含めるか両側で統一したか
- 数値と文字列、前後空白、重複キーを確認したか
- MATCHを単独評価してからINDEXへ組み込んだか
- 先頭・中央・末尾と未登録データで結果を照合したか
コメント