「この表、商品名から商品コードを引きたいだけなんですけど」
そう言いながらVLOOKUPを書いて、何度も#N/Aを出してしまう。よくある話です。原因は単純で、探したい商品名がB列、取り出したい商品コードがA列。つまり、欲しい値が検索する列より左側にあったからです。
VLOOKUP関数は、検索する列が範囲の一番左になければ動きません。だったら列を入れ替えればいい。そう思って作業を始めたところで、「そのファイル、他の人も参照しているので並びは変えないでください」と止められる。説明用に用意した例ですが、職場ではよくある話です。八方ふさがりです。
そんなときに使えるのが、この式です。
=INDEX(A2:A200, MATCH(D2, B2:B200, 0))初めて見ると、呪文にしか見えないかもしれません。関数が2つ入れ子になっていて、どこから読めばいいのかも分からない。ただ、一つずつ分解してみると、やっていることは驚くほど単純です。
この記事では、INDEX関数とMATCH関数を「いきなり合体させない」やり方で説明していきます。先に1つずつ単独で動かして、感触をつかんでから組み合わせる。この順番で追いかけると、途中で迷子になりません。
まずはMATCH関数だけを動かしてみる
MATCH関数は、探している値が「上から数えて何番目にあるか」を教えてくれる関数です。値そのものは返しません。返すのは番号だけです。
=MATCH(検索値, 検索範囲, 照合の種類)- 検索値：探したい値を指定します。
- 検索範囲：探す先の範囲を指定します。1列（または1行）だけを指定します。
- 照合の種類：完全一致で探すなら0を指定します。
たとえばB2からB200に商品名が並んでいて、D2に「アイスコーヒー」と入力してあるとします。
=MATCH(D2, B2:B200, 0)この式の結果が「45」だったなら、「アイスコーヒーはB2から数えて45番目にある」という意味になります。B列の45行目、ではありません。指定した範囲の中で45番目、です。ここを勘違いすると後で必ず数式がズレるので、最初に押さえておきたいところです。
第3引数の0は、省略せずに必ず書く
MATCH関数の3つ目の引数を省略すると、Excelは1（検索値以下の最大値を探す）として処理します。この設定は、範囲が昇順に並び替えられていることが前提になっています。並び替えられていない普通の表で使うと、まったく関係のない行の番号を平気で返してきます。
しかもエラーは出ません。数字が返ってくるので、一見すると成功しているように見えます。これがやっかいなところです。完全一致で探したいなら、面倒でも0を書く。この習慣だけで防げる事故はかなり多いです。
検索範囲に複数列を指定するとエラーになる
MATCH関数の検索範囲は、1列か1行のどちらかである必要があります。B2:C200のように2列を指定すると#N/Aになります。VLOOKUPの「範囲」の感覚で複数列を選んでしまう人が多いのですが、MATCHは番号を数えるだけの関数なので、探す列は1本に絞ってあげる必要があります。
次にINDEX関数だけを動かしてみる
INDEX関数は、指定した範囲の中から「何番目の値か」を取り出す関数です。
=INDEX(範囲, 行番号, 列番号)A2からA200に商品コードが並んでいるとして、次のように書きます。
=INDEX(A2:A200, 45)これで「A2から数えて45番目の商品コード」が返ってきます。範囲が1列だけの場合、列番号は省略できます。
ここで気づいた方もいると思います。INDEX関数を単独で使うと、「45」という番号を自分で数えて入力しなければいけません。200行の表を目視で数えるのは現実的ではありませんし、行が増減したら書き直しです。だからこそ、この「45」を自動で出してくれる相棒が必要になります。それがMATCH関数です。
2つを組み合わせる手順
ここまで来れば、合体させる作業はほとんど作業になります。おすすめの順番は次の3ステップです。
ステップ1：取り出したい列を決めて、INDEXの第1引数に入れる
今回は商品コードが欲しいので、A2:A200を指定します。この時点では行番号の場所は空けておきます。
ステップ2：別のセルでMATCHだけを書いて、番号が正しく出るか確認する
いきなり組み込まないのが大事です。適当な空きセル、たとえばF2に次の式を入れて、番号が返ってくるか確かめます。
=MATCH(D2, B2:B200, 0)ここで#N/Aが出るなら、原因はMATCH側にあると確定します。組み合わせた後にエラーが出ると、INDEXが悪いのかMATCHが悪いのか切り分けられなくなるので、この検算をはさむだけで作業がぐっと楽になります。
ステップ3：確認できたMATCHの式を、INDEXの行番号の位置に貼り付ける
=INDEX(A2:A200, MATCH(D2, B2:B200, 0))これで完成です。「A列の中から、B列でD2を探して見つかった番号のところの値を取り出す」という式になりました。読み方としては、内側のMATCHから先に読むと意味がつかみやすいです。
なお、検索する列が範囲の左端にあって、取り出したい値がその右側にある、という素直な形の表であれば、無理にINDEX+MATCHにする必要はありません。その場合はVLOOKUPの方が式が短く済みます。VLOOKUP関数の使い方については別記事で基本から整理していますので、そもそもの検索の考え方から確認したい方はあわせてご覧ください。
実務でINDEX+MATCHが効いてくる4つの場面
1. 検索する列より左側の値を取り出したいとき
冒頭の例がまさにこれです。MATCHで探す列とINDEXで取り出す列は完全に独立しているので、位置関係を一切気にしなくてよくなります。左でも右でも、離れていても構いません。
取引先から受け取ったマスタファイルのように、自分では列の並びを変えられないデータを扱うときに、この自由度がそのまま効いてきます。
2. 列を挿入しても数式が壊れないようにしたいとき
VLOOKUP関数は「範囲の中で何列目か」を数字で指定します。そのため、参照先の表の途中に列を1本追加しただけで、3列目だったものが4列目になり、数式が静かに違う値を返し始めます。エラーにならないので気づきにくく、月次の資料でこれをやると数字が丸ごと入れ替わります。
INDEX+MATCHは列を数字で指定しません。取り出す列そのものを範囲として指定するため、列が挿入されてもExcel側が参照を自動で追従してくれます。長く使い回す集計表ほど、この差が効いてきます。
3. 縦と横の両方から絞り込んで1つの値を取り出したいとき
月別・商品別のクロス集計表から、「6月のアイスコーヒーの数量」だけを取り出したい。こういう場面では、MATCHを2回使います。
=INDEX(B2:M50, MATCH(P2, A2:A50, 0), MATCH(Q2, B1:M1, 0))1つ目のMATCHで「アイスコーヒーが縦に何番目か」、2つ目のMATCHで「6月が横に何番目か」を求め、その交差点の値をINDEXが取り出します。VLOOKUPでは列番号を手で数えるしかなかった部分が、こちらは月の名前を入力するだけで切り替わります。
月次の実績表から特定のセルだけを別シートに抜き出したい、という要望は実務でよく出てきます。そのたびに列番号を数え直していた作業が、この形にしておくと一度書けば終わりになります。
4. 条件を2つ以上組み合わせて探したいとき
「東京支店の佐藤さん」のように、条件が2つ必要な場面もあります。この場合、いちばん確実なのは補助列を作る方法です。
作業列にたとえば =A2&B2 という式を入れて「東京佐藤」という連結キーを作り、その列をMATCHの検索範囲にします。検索値の側も同じように =P2&Q2 で連結しておけば、あとは通常のINDEX+MATCHと同じ書き方で動きます。
補助列を作らずに書く方法もありますが、数式が長くなるうえに配列の考え方が必要になるので、慣れないうちは補助列方式のほうが安全です。後から見直したときに何をしているのか分かる、というのも実務では大きな利点になります。
エラーが出たときの読み分け
INDEX+MATCHでつまずくポイントは、だいたい決まっています。表示された内容から原因を逆算できるように整理しておきます。
| 表示される内容 | 主な原因 | 最初に確認する場所 |
|---|---|---|
| #N/A | MATCHが検索値を見つけられていない | 検索値と検索範囲の表記ゆれ、余分な空白、全角と半角の違い |
| #REF! | INDEXに範囲外の行番号が渡っている | INDEXの範囲の行数と、MATCHが返した番号のズレ |
| #VALUE! | MATCHの引数の指定に無理がある | 検索範囲に2列以上を指定していないか |
| 0が表示される | 取り出した先のセルが空欄 | 元データの該当セルが空になっていないか |
| エラーは出ないが値が違う | 照合の種類を省略している | MATCHの第3引数に0を書いているか |
このうち圧倒的に多いのが#N/Aで、その原因の多くは「見た目は同じなのにデータとしては別物」というパターンです。特に多いのが、セルの末尾に空白が1つだけ入っているケース。画面上ではまったく判別できません。セルの中の見えない空白については集計が合わなくなる原因としても解説していますので、検索値と検索範囲を何度見比べても違いが見つからないときは、こちらの確認方法を試してみてください。
もう一つ、意外と気づきにくいのが参照先の表の構造そのものに問題があるケースです。同じ意味の列が複数ある表を参照していると、MATCHはたまたま先に見つけた側の位置を返すため、更新されていない古い列の値を延々と拾い続けることになります。数式は正しいのに結果が古い、というときは表の側を疑ってみてください。
VLOOKUP・XLOOKUP・INDEX+MATCHの使い分け
3つとも「別の表から値を持ってくる」という目的は同じです。どれを選ぶかは、表の構造と、そのファイルを誰と共有するかで決まります。
| VLOOKUP | XLOOKUP | INDEX+MATCH | |
|---|---|---|---|
| 検索列の位置 | 左端に固定 | どこでもよい | どこでもよい |
| 列の挿入 | 数式がズレる | ズレない | ズレない |
| 縦横の同時検索 | 苦手 | 工夫が必要 | 得意 |
| 数式の短さ | 短い | 短い | やや長い |
| 使えるバージョン | ほぼすべて | Microsoft 365、Excel 2021以降 | ほぼすべて |
表を見ると、機能面ではXLOOKUPが有利に見えます。実際、使える環境ならXLOOKUPで書いたほうが式は短くなります。XLOOKUP関数の使い方ではVLOOKUPとの違いと乗り換えの判断基準をまとめていますので、自分の環境で使えるか確認したい方はそちらもどうぞ。
ただし、社内に古いバージョンのExcelを使っている人が一人でもいて、その人とファイルを共有する可能性があるなら、話は変わってきます。XLOOKUPで作った数式は、対応していないExcelで開くとエラーになります。INDEX+MATCHは古いバージョンでも問題なく動くため、共有相手が読めない可能性を考えなくて済みます。
私自身は、自分だけで使うファイルはXLOOKUP、部署内で回覧するファイルはINDEX+MATCH、というように分けています。数式の見た目より、相手の環境で確実に動くことを優先した結果です。
INDEX+MATCHで聞かれること
Q. XLOOKUPがあるなら、INDEX+MATCHは覚えなくてもいいのでは？
自分の環境だけで完結するなら、それでも困りません。ただ、他社から送られてきたファイルや、社内の共有フォルダに何年も置かれている古いファイルを開く機会があるなら、読める状態にしておく価値はあります。INDEX+MATCHで書かれた数式は今でも大量に現役で動いていて、それを引き継ぐ場面は普通にやってきます。書けなくても読めるだけで、引き継ぎのときの負担がかなり違います。
Q. 数式が長くて、後から見ると何をしているのか分かりません
数式バーの中でAlt+Enterを押すと、数式の途中で改行できます。INDEXの引数ごとに改行しておくと、構造が目で追えるようになります。計算結果には影響しません。
もう一つの方法は、MATCHの部分を別のセルに切り出しておくことです。作業列が1本増えますが、番号が正しく出ているかを常に目視できるので、トラブル時の切り分けが速くなります。人に渡すファイルなら、この形にしておくほうが親切だと思います。
Q. 範囲はA:Aのように列全体を指定してもいいですか？
動きますが、行数が多いファイルでは動作が重くなります。特にINDEX+MATCHを何百行にもコピーしている場合、列全体指定だと再計算のたびに時間がかかります。データの範囲を具体的な行数で指定するか、表をテーブルに変換して構造化参照を使うほうが安定します。
Q. MATCHの検索範囲とINDEXの範囲は、行数を揃える必要がありますか？
揃えてください。MATCHの検索範囲をB2:B200、INDEXの範囲をA1:A200のように開始行がズレていると、返ってくる値が1行分ずれます。エラーが出ないぶん、こちらのほうが厄介です。どちらも2行目から始めるなら、両方とも2行目から。この一致だけは、数式を書いたあとに必ず見返す習慣にしておくと安心です。
Q. そもそも、どの関数を使えばいいのか分からなくなります
やりたいことから逆算して探すのがいちばん近道です。目的別のExcel関数の選び方で、集計・条件分岐・検索・文字列処理といった用途ごとに関数を整理していますので、迷ったときの地図として使ってみてください。
回りくどく見える書き方に慣れるコツ
INDEX+MATCHを初めて見ると、「なんでこんな回りくどい書き方をするんだろう」と感じる人が多いはずです。VLOOKUPなら1つで済むことを、わざわざ2つに分けているように見えるからです。
例えば、こんなケースを考えてみてください。前任者が作った集計ファイルを引き継いだら、VLOOKUPで組まれていた。参照先のマスタに列が1本追加された瞬間、全部の数式が1列分ズレる。エラーは1つも出ません。ただ、単価の欄に原価が入っている。月次報告の数字が前月比で妙に良すぎることに誰かが違和感を持つまで、気づかれないかもしれません。
もしINDEX+MATCHで組まれていれば、列が増えても何も起きません。式が長いことは、そのまま欠点ではないのです。少し面倒な書き方をしておくことで、将来の誰かが数字を間違えずに済む。関数を選ぶ基準には「今の自分が書きやすいか」だけでなく、「半年後の誰かが壊しにくいか」も入れておきたいところです。
INDEX+MATCHの要点
INDEX関数とMATCH関数は、分けて考えるとどちらも単純な関数です。
- MATCHは「何番目にあるか」という番号だけを返す関数
- INDEXは「何番目の値か」を取り出す関数
- 組み合わせるときは、先にMATCHだけ単独で動かして検算する
- 第3引数の0を省略しない
- MATCHの検索範囲とINDEXの範囲は、開始行を揃える
検索する列が左端に固定されない、列を挿入しても壊れない、縦横の交差点を取り出せる。この3つが揃っているので、長く使い続ける表ほど相性が良くなります。XLOOKUPが使える環境ならそちらのほうが手軽ですが、共有相手の環境が読めないときの保険として、INDEX+MATCHは今でも十分に現役です。
まずは手元の表で、MATCHだけを1つ書いてみてください。番号が返ってくる感覚がつかめたら、あとは組み立てるだけです。
INDEX+MATCHは、条件が複数になったり、参照先が複数シートにまたがったりすると、ここで紹介した基本形だけでは足りない場面が出てきます。そうした応用パターンは、そのまま貼り付けて使える数式の型として、noteに12本まとめています。もう一歩踏み込んで使い倒したい方は、あわせてご覧ください。
コメント