こんにちは！どこでもパソコン教室 四日市です。
今回の授業は、VLOOKUPよりも柔軟で実務向きの「INDEX関数」と「MATCH関数」の組み合わせを、初心者の方にもわかりやすく解説します！
大量データの検索がグッと楽になりますよ。
最近、VLOOKUPを使っているんですが、列が増えるたびに数式を直すのが大変で……。
もっと楽にできる方法ってないんでしょうか？
その悩み、よくわかります。
実務では列追加やデータ更新が頻繁に起こるので、VLOOKUPだけだと限界を感じますよね。
まさにそれです！ 毎回修正するのがストレスで……。
そこで提案したいのが INDEX関数 × MATCH関数 の組み合わせです。
実務講座でも「柔軟性が高い」「列追加に強い」と受講生から高評価のテクニックなんですよ。
そんなに便利なんですか？
はい。今回の授業をひとことでまとめると、
「INDEXとMATCHを組み合わせると、VLOOKUPより柔軟でメンテナンスしやすい検索ができる」
ということです。
なるほど…！ 受講するメリットは何ですか？
• 列が増えても数式を直さなくていい
• 左側の列も検索できる
• 大量データでも高速
• VLOOKUPの弱点をすべてカバーできる
という点ですね。
すごい…！
最終的にはどう使いこなせばいいんでしょう？
結論としては、
「VLOOKUPを理解している人こそ、INDEX＋MATCHを覚えると実務効率が一気に上がる」
ということです。
授業の手順通りに進めれば、誰でも使えるようになりますよ。
思い出し作業におススメの授業です！
INDEX関数とMATCH関数でVLOOKUPの弱点を解消しよう
VLOOKUPを使っていると、 列が増えるたびに数式を修正 したり、 左側の列を検索できなかったり と不便を感じることがあります。
そこで今回の授業では、INDEX関数とMATCH関数を組み合わせた柔軟な検索方法を、初心者の方にもわかりやすく解説します。
VLOOKUP関数のおさらい
VLOOKUP（ブイ・ルックアップ）関数について、実際の例を使いながらわかりやすく解説します。
下図は、 都道府県別・年ごとのコンビニ店舗数 をまとめた表です。
※統計ダッシュボード (https://dashboard.e-stat.go.jp/)のデータを加工して作成
47都道府県の中から、 お住まいの地域のコンビニ数を知りたい とき、
別表に都道府県名を入力するだけで、その行のデータを一瞬で取り出せるのがVLOOKUP関数です。
• VLOOKUPの「V」は “Vertical（バーティカル）＝垂直”の頭文字
• LOOKUPは「調べる」という意味
縦方向に同じ項目が並んでいる表から値を検索する関数です。
数式を見てみましょう。
| ＝ＶＬＯＯＫＵＰ（Ｇ４,Ａ３:Ｅ４９,２,０） | ||||
|---|---|---|---|---|
| VLOOKUP | G4 | A3:E49 | 2 | 0 |
| 関数名 | 検索値 | 範囲 | 列番号 | 検索方法 |
| 使用する関数の名前 | データ表の1列目から探したい値（今回は「都道府県名」）を指定するセル | データの範囲 | 抽出したい情報が何列目にあるか（今回は「表示したい年の列番号」） | 完全一致*は「0」or「FALSE」、近似値**は「1」or「TRUE」 |
（※今回の検索値は都道府県名なので、“約北海道”のような値は存在せず、完全一致を使います）
** 近似値とは…「検索値」に近い値も含めて検索します。
（※例：検索値が「100」で、データが「90」と「120」しかない場合、最も近い「90」を返します）
47都道府県を毎回手入力するのは大変です。
そこで、下図のように リスト化 しておくと、🖱️クリックだけで入力でき、誤入力も防げます。
INDEX関数の基礎
INDEX（インデックス）関数とは、 データ範囲の「行番号」と「列番号」を指定すると、その交差するセルの値を返す関数 です。
ちなみに「INDEX」を直訳すると “索引” という意味になります。
INDEX関数には、
● 複数のデータ表を使う「セル範囲形式」
の2種類があります。
配列形式：INDEX（配列, 行番号, 列番号）
INDEX関数の配列形式は、 使用するデータ表が1つの場合 に使います。
例えば、下図のような「かけ算の九九」の表では、👀 目視で見ると「3の段」の「5行目」は『15』ですが、INDEX関数を使うと、 この値を自動で取り出すことができます。
数式を見てみましょう
行番号と列番号は、今回のようにセルに入力しても良いですし、直接数字を入力しても構いません。
| ＝ＩＮＤＥＸ（Ｃ４:Ｆ１２,Ｃ１７,Ｄ１７） | ||||
|---|---|---|---|---|
| INDEX | C4:F12 | C17（※数字でもOK） | D17（※数字でもOK） | |
| 関数名 | 配列 | 行番号 | 列番号 | |
| 使用する関数の名前 | データの範囲 | 抽出したい情報がデータ表の何行目にあるか |
抽出したい情報がデータ表の何列目にあるか | |
セル範囲形式：INDEX（参照, 行番号, 列番号, 領域番号）
INDEX関数のセル範囲形式は、 使用するデータ表が複数ある場合 に使います。
例えば、下図のように九九の表が左右に2つある場合、同じ「5行目・2列目」でも、 どちらの表を使うか（領域番号）を選択できる のが特徴です。
数式を見てみましょう
行番号・列番号・領域番号は、セルに入力しても、直接数字を入力してもOKです。
| ＝ＩＮＤＥＸ（（Ｃ４:Ｆ１２,Ｊ４：Ｍ１２）,Ｅ１７,Ｆ１７,Ｇ１７） | ||||
|---|---|---|---|---|
| INDEX | (C4:F12,J4:M12) | E17 | F17 | G17 |
| 関数名 | 参照 | 行番号 | 列番号 | 領域番号 |
| 使用する関数の名前 | C4:F12 が領域番号1、J4:M12 が領域番号2 |
抽出したい情報がデータ表の何行目にあるか | 抽出したい情報がデータ表の何列目にあるか | どのデータ表を使用するか |
MATCH関数の基礎
MATCH（マッチ）関数とは、 検索したいデータが、指定した範囲の中で「何番目」にあるかを返す関数 です。
データ範囲を 1列だけ選択した場合は「行番号」、 1行だけ選択した場合は「列番号」 を返します。
ちなみに「MATCH」を直訳すると “一致” という意味です。
行番号を検索：検索範囲は「一列」を選択
データ範囲を一列選択した場合は行番号を返します。
複数列選択するとエラー（#N/A）になりますので注意しましょう。
データ範囲を 一列だけ 選択した場合、MATCH関数は 行番号 を返します。
複数列を選択すると エラー（#N/A）になるため注意しましょう。
数式を見てみましょう
検索値は、今回のようにセルに入力しても良いですし、直接数値や文字列を入力しても構いません。
| ＝ＭＡＴＣＨ（Ｄ１３,Ｅ４：Ｅ８,０） | ||||
|---|---|---|---|---|
| MATCH | D13 | E4:E8 | 0 | |
| 関数名 | 検査値 | 検査範囲 | 照合の種類 | |
| 使用する関数の名前 | データ表の何行目にあるか知りたい値を入力するセル | 位置を調べたいデータが並んでいる一列 | 完全一致は「0」 検査値以下の最大値は「1」 検査値以上の最小値は「-1」 |
|
列番号を検索：検索範囲は「一行」を選択
データ範囲を 一行だけ 選択した場合、MATCH関数は 列番号 を返します。
複数行を選択すると エラー（#N/A）になるため注意しましょう。
数式を見てみましょう
検索値は、今回のようにセルに入力しても良いですし、直接数値や文字列を入力してもOKです。
| ＝ＭＡＴＣＨ（Ｄ１３,Ｂ６：Ｈ６,０） | ||||
|---|---|---|---|---|
| MATCH | D13 | B6:H6 | 0 | |
| 関数名 | 検査値 | 検査範囲 | 照合の種類 | |
| 使用する関数の名前 | データ表の何列目にあるか知りたい値を入力するセル | 位置を調べたいデータが並んでいる一行 | 完全一致は「0」 検査値以下の最大値は「1」 検査値以上の最小値は「-1」 |
|
VLOOKUPとINDEX＋MATCHの比較
VLOOKUP関数の大きな特徴のひとつは、検索値（検査値）が 必ずデータ表の“一番左の列”にある必要がある という点です。
しかし、INDEX関数 × MATCH関数の組み合わせなら、検索値が左端になくても問題ありません。
さらにもう一つ注目ポイントがあります。
下図のようにデータ表に列項目を追加した場合……
● INDEX × MATCH の組み合わせなら、列番号の修正が不要です。
つまり、データ構造が変わっても数式が壊れにくいという、大きなメリットがあります。
比較表
| 項目 | VLOOKUP | INDEX＋MATCH |
|---|---|---|
| 左側の列を検索 | × | ○ |
| 列追加の影響 | 大きい | ほぼなし |
| 柔軟性 | 低い | 高い |
| 大量データ | 重くなりやすい | 比較的軽い |
実務ではINDEX＋MATCHの方が圧倒的に使いやすい理由がここにあります！
INDEX＋MATCHの数式を作る手順
INDEX関数とMATCH関数を組み合わせた数式の 作成手順 を解説します。
ちなみに、このように 関数の中に別の関数を入れることを「ネスティング（ネスト）」 といいます。
ここでは、上項目『VLOOKUP関数のおさらい』でも使用した 都道府県別・年ごとのコンビニ数の表 を使って説明します。
セル番地は、ご自身の表に合わせて読み替えてください。
※統計ダッシュボード (https://dashboard.e-stat.go.jp/)のデータを加工して作成
- セルH4を🖱️クリックしてアクティブセル*にします。
*アクティブセルとは…「セル」とはたくさんあるマス目のことで、「アクティブセル」とはセルの中でも入力できる状態のセルをいいます。
- 次の手順で INDEX関数を挿入 します。
1.［関数の挿入］ボタンを🖱️クリックします。
2.「関数の挿入」ダイアログボックスが表示されるので、 関数の検索 に「index」と入力します。
3.［検索開始］を🖱️クリックします。
4. 関数名 に表示された「INDEX」を🖱️クリックします。
5.［OK］を🖱️クリックします。
- INDEX関数の「関数の引数」ダイアログボックスが表示されます。
次の操作を行います。
1. 配列 に🖱️クリックして「|」カーソルを入れます。
2. データを🖱️ドラッグして範囲選択します。
（左上から右下にドラッグするのがコツです）
3. 後で数式をコピーしても範囲がズレないよう、F4キーで絶対参照にします。
（行・列の前に「$」が付いて固定されます）
- INDEX関数の「行番号」に MATCH関数を入れます。
1. 行番号 を🖱️クリックして「|」カーソルを入れます。
2.「名前ボックス」の［▼］➡［その他の関数］を🖱️クリックします。
- 「関数の挿入」ダイアログボックスが表示されます。
1. 関数の検索 に「ma」と入力します。
2.［検索開始］を🖱️クリックします。
3. 関数名 に表示された「MATCH」を🖱️クリックします。
4.［OK］を🖱️クリックします。
- MATCH関数の「関数の引数」ダイアログボックスが表示されます。
1. 検査値 を🖱️クリックします。
2. 検査値となるセル（今回は都道府県名を入力する A3）を🖱️クリックします。
3. コピー時にズレないよう F4キー で絶対参照にします。
4. 検査範囲 を🖱️クリックします。
5. 都道府県名を🖱️ドラッグして範囲選択します。
6. F4キー で絶対参照にします。
7. 照合の種類 に「0」を入力します。
- 列番号を求める MATCH関数を入れます。
1. 数式バーの「INDEX」を🖱️クリックします。
2. INDEX関数の「関数の引数」ダイアログボックスで、 列番号 を🖱️クリックします。
3. 名前ボックスで「MATCH」を選択します。
- MATCH関数の「関数の引数」ダイアログボックスが表示されます。
1. 検査値 を🖱️クリックします。
2. 検査値となるセル（今回は H4 の上にある「1982年」の H3）を🖱️クリックします。
3. 検査範囲 を🖱️クリックします。
4. 年を🖱️ドラッグして範囲選択します。
5. F4キー で絶対参照にします。
6. 照合の種類 に「0」を入力します。
7.［OK］を🖱️クリックします。
- 数式が完成しました！
＝INDEX($B$3:$E$49,MATCH($G$4,$A$3:$A$49,0),MATCH(H3,$B$2:$E$2,0)) INDEX $B$3:$E$49 MATCH($G$4,$A$3:$A$49,0) MATCH(H3,$B$2:$E$2,0) 関数名 配列 行番号 列番号 使用する関数の名前 データの範囲 抽出したい情報がデータ表の何行目にあるか
抽出したい情報がデータ表の何列目にあるか
数式をすべてのセルにコピー します。
1.「■」フィルハンドルを右方向へ🖱️ドラッグします。
2. 最初のセルの書式が引きずられます。
3. オートフィル オプションから「書式なしコピー（フィル）」を🖱️クリックします。
4. 書式を崩さず、きれいに数式をコピーできます。
まとめ
今回の授業は、VLOOKUPよりも柔軟で実務向きの「INDEX関数」と「MATCH関数」の組み合わせを、初心者の方にもわかりやすく解説しました！
INDEX関数とMATCH関数を組み合わせることで、
VLOOKUPより柔軟で、列追加にも強く、実務で使いやすい検索方法 が実現できます。
最初は少し難しく感じるかもしれませんが、
意味を理解しながら数式を作れば、必ず使いこなせるようになります。
文字列を取り出す関数もおすすめです。
😄ご受講お疲れさまでした！
図解が多く、INDEX・MATCH・VLOOKUPなどの“実務でよく使う関数”をやさしく丁寧に解説してくれる一冊です。
「関数が苦手…」という方でもスラスラ読める構成なので、今回の授業で学んだ内容をさらに深めたい方にぴったりです。
コメント