「VLOOKUPで対応しようとしたら、エラーだらけになった」
Excelで「複数の条件を満たしながら、近似一致で値を取得したい」という場面に直面したとき、こんな経験はありませんか？
- VLOOKUPで数式を組んだら
#N/Aが返ってきて手詰まりになった - 仕方なく目視で表を探して手入力し、作業に30分以上かかった
- そもそもどの関数を組み合わせればいいかわからず、ネット検索を繰り返した
こうした「2次元の近似値検索」は、VLOOKUPやXLOOKUPだけでは構造的に対応が難しい領域です。解決策は INDEX・MATCH・IFの組み合わせ にあります。
本記事では、基礎の役割確認から実際の数式の組み立て、エラー対策、さらに3条件以上への応用まで、ステップバイステップで解説します。
基礎確認：3つの関数の役割を整理する
まず各関数の役割を表で整理しておきましょう。
| 関数 | 役割 |
|---|---|
INDEX(範囲, 行番号, 列番号) |
指定した位置のセル値を返す |
MATCH(検索値, 検索範囲, 照合型) |
検索値の位置（番号）を返す |
IF(条件, 真の値, 偽の値) |
条件に応じて返す値を切り替える |
特に重要なのが MATCH の第3引数「照合型」です：
| 照合型 | 動作 | ソート条件 |
|---|---|---|
0 |
完全一致 | 不要 |
1 |
検索値以下の最大値 | 昇順必須 |
-1 |
検索値以上の最小値 | 降順必須 |
近似一致検索では主に 1 を使います。そして 昇順ソートが必須である点は、後述するよくある失敗にも直結するため、しっかり覚えておいてください。
実践シナリオ：カテゴリー × 数量で単価を取得する
テーブルの構成（セル番地付き）
以下のようなレイアウトをExcelに用意してください。
A列 B列 C列 D列 1行目 （空白） 1 10 50 ← 数量の下限値 2行目 カテゴリーA 1000 900 800 3行目 カテゴリーB 1500 1300 1100 4行目 カテゴリーC 2000 1700 1400
A2:A4：カテゴリー名（完全一致で検索する）B1:D1：数量の下限値（昇順：1, 10, 50）B2:D4：取得したい単価データ
入力セル（検索条件）
G1：カテゴリー名（例：カテゴリーB）G2：数量（例：25）
期待する結果：カテゴリーBかつ数量25 → 1300（10〜49の列に該当）
数式の組み立て：3ステップで完成させる
Step 1：行番号をMATCHで取得（完全一致）
カテゴリー名は完全一致で検索するため、照合型は 0 を使います。
MATCH(G1, A2:A4, 0)
G1 が「カテゴリーB」なら → 2（A2:A4の中で2番目）
Step 2：列番号をMATCHで取得（近似一致）
数量は「下限値以下の最大値」で検索するため、照合型は 1 を使います。B1:D1は昇順（1, 10, 50）になっています。
MATCH(G2, B1:D1, 1)
G2 が「25」なら、25以下の最大値は「10」→ 2（B1:D1の中で2番目）
Step 3：INDEXで値を取り出す
Step 1とStep 2で求めた行番号・列番号をINDEXに渡します。
=INDEX(B2:D4, MATCH(G1, A2:A4, 0), MATCH(G2, B1:D1, 1))
結果：カテゴリーB（2行目）× 10〜の列（2列目）= 1300 ✅
ポイント：
INDEXの参照範囲B2:D4と、各MATCHの検索範囲（A2:A4とB1:D1）の起点を揃えることが重要です。ここがずれると엉뚱な値が返ります。
IFとIFERRORでエラー対策と入力ガードを追加する
実務では想定外の入力やデータ不備が発生します。IF と IFERROR を組み合わせて、堅牢な数式に仕上げましょう。
IFERROR：検索失敗時のメッセージ表示
=IFERROR( INDEX(B2:D4, MATCH(G1, A2:A4, 0), MATCH(G2, B1:D1, 1)), "該当なし" )
カテゴリー名のスペルミスや、範囲外の値が入力された際に #N/A の代わりに「該当なし」を表示します。
IF：無効な入力値をガードする
数量が0以下の場合は計算を行わないよう制御する例です：
=IF(G2 <= 0,
"数量を正しく入力してください",
IFERROR(
INDEX(B2:D4, MATCH(G1, A2:A4, 0), MATCH(G2, B1:D1, 1)),
"該当なし"
)
)
IF → IFERROR → INDEX/MATCH という外から内へのネスト構造で読むと整理しやすくなります。
応用：3条件以上には配列数式を使う
ここまでは「カテゴリー（完全一致）× 数量（近似一致）」の2条件でした。「地域」「ランク」「売上規模」のように3つ以上の条件が絡む場合は、配列数式のテクニックを使います。
シナリオ設定
A列 B列 C列 D列 E列 1行目 地域 ランク 〜100 〜500 〜1000 ← 売上上限 2行目 東日本 A 3% 2% 1% 3行目 東日本 B 5% 4% 3% 4行目 西日本 A 4% 3% 2% 5行目 西日本 B 6% 5% 4%
検索条件：G1=地域、G2=ランク、G3=売上規模
数式
=IFERROR(
INDEX(C2:E5,
MATCH(1, (A2:A5=G1)*(B2:B5=G2), 0),
MATCH(G3, C1:E1, 1)
),
"該当なし"
)
バージョン別の入力方法
- Excel 365 / Excel 2021：そのまま
Enterで確定（動的配列として自動処理）- Excel 2019以前：
Ctrl + Shift + Enterで確定（数式バーに{=...}と表示される）古いバージョンで通常の
Enterで入力すると正しく動作しないため注意してください。
(A2:A5=G1)*(B2:B5=G2) はAND条件を乗算で表現するテクニックです。両方が TRUE(1) のときだけ 1×1=1 となり、MATCHが該当行を特定します。
よくある失敗と対処法
❌ データが昇順ソートされていない
照合型 1 は昇順ソートが必須です。未ソートのまま使うと誤った値が返ります。
→ 対処：B1:D1 の数量下限値は必ず小さい順（1, 10, 50…）に並べてください。
❌ INDEXとMATCHの参照範囲の起点がずれている
INDEX(B2:D4,...) に対して、行の MATCH を A1:A4 で書いてしまうなど、起点のずれで엉뚱な行・列を参照するケースがよくあります。
→ 対処：INDEX の行範囲が B2:D4（2行目〜）なら、行の MATCH 検索範囲も A2:A4（2行目〜）と起点を揃えて考えましょう。
❌ 検索値がテーブルの最小値より小さい
照合型 1 では、検索値がテーブル内の最小値を下回ると #N/A エラーになります。
→ 対処：IFERROR でメッセージを表示するか、テーブルに「0〜」の列を追加してカバーします。
まとめ
本記事のポイントを整理します：
MATCHの照合型1（昇順ソート必須）で近似一致の位置を取得できるINDEX + MATCH × 2で2次元テーブルからの値取得が可能IFとIFERRORを外側に重ねてエラー対策と入力ガードを実装する- 3条件以上には
(条件1)*(条件2)の乗算テクニックと配列数式を使う - Excel 365以前では配列数式の入力方法（
Ctrl+Shift+Enter）が異なるため注意
VLOOKUPで行き詰まっていた検索処理も、INDEX・MATCH・IF の組み合わせを理解すれば、スマートかつ柔軟に解決できます。まずは本記事のシナリオをそのままExcelで再現し、自分のデータに置き換えてみてください。
本記事は「Lookupシリーズ」の一部です。他の記事も合わせてご覧いただくと、Excel検索関数の理解がより深まります。
