ExcelでINDEX関数とMATCH関数の使い方をお探しですね。

広告

ExcelのVLOOKUPの弱点を克服!INDEX関数とMATCH関数の使い方をやさしく解説

Excelで「商品コードから商品名を表示したい」「社員番号から名前を引っ張ってきたい」というとき、多くの人が思い浮かべるのがVLOOKUP関数ですよね。

とても便利な関数なのですが、実は「検索する列より左側の値が取り出せない」「列を追加したり削除したりすると、結果がズレてしまう」「列番号を数えるのが面倒」といった困りごとに悩まされることも少なくありません。

この記事では、そんなVLOOKUPの弱点をカバーしてくれる、**INDEX関数とMATCH関数を組み合わせた検索方法**を、初心者の方にもわかりやすく解説していきます。

1. VLOOKUPの弱点って?INDEX関数とMATCH関数が必要な理由

VLOOKUPは、表の一番左の列を縦に検索して、指定した列番号の値を取り出してくれる関数です。

商品コードから商品名や単価を表示したいときなど、Excel作業ではよく使われています。

ただ、VLOOKUPには構造上の制約があるんです。

一番よく知られているのが、**「検索する列が、取り出したい列より左側にないと使いにくい」**という点。

たとえば、検索したい値が表の右側にあって、取得したい値がその左側にある場合、普通のVLOOKUPではうまく取り出せません。

もう一つの大きな弱点は、**列番号を手で入力しないといけない**ことです。

たとえば「3列目を取り出す」と指定している数式で、途中に列を追加したり削除したりすると、意図しない列を参照してしまったり、#REF!エラーが出たりすることがあります。

表の構成が変わらなければ問題ないのですが、実際の仕事では列の追加・削除・並び替えって頻繁に起こりますよね。

そのたびに数式を確認して修正するのは手間がかかるし、ミスの原因にもなります。

そこで役に立つのが、**INDEX関数とMATCH関数の組み合わせ**です。

– **INDEX関数**は、指定した範囲の中から「何行目・何列目」にある値を取り出す関数
– **MATCH関数**は、指定した値が範囲の中で「何番目にあるか」を調べる関数

この2つを組み合わせることで、検索値の位置をMATCH関数で探して、その位置に対応する値をINDEX関数で取り出すことができます。

検索列が左でも右でも使えるので、VLOOKUPよりもずっと柔軟な検索・抽出ができるようになります。

2. INDEX関数とMATCH関数の基本を理解しよう

INDEX関数とMATCH関数を組み合わせる前に、それぞれの役割を整理しておきましょう。

INDEX関数の基本

INDEX関数の基本形は次のとおりです。

“`
=INDEX(配列, 行番号, [列番号])
“`

– **配列**:検索対象となる範囲
– **行番号**:上から何行目か
– **列番号**:左から何列目か(省略可)

たとえば「=INDEX(B2:D6, 3, 2)」なら、B2:D6の範囲内で上から3行目、左から2列目にある値を返します。

つまり、**INDEX関数は”位置がわかっている値を取り出す関数”**と考えるとわかりやすいですね。

MATCH関数の基本

MATCH関数の基本形は次のとおりです。

“`
=MATCH(検査値, 検査範囲, [照合の種類])
“`

– **検査値**:探したい値
– **検査範囲**:検索する範囲
– **照合の種類**:完全一致なら「0」

たとえば「=MATCH(“A003”, A2:A6, 0)」と入力すると、A2:A6の中で「A003」が何番目にあるかを返してくれます。

実際の仕事でコードや名前を検索するときは、基本的に完全一致の「0」を使うと覚えておくといいですよ。

2つを組み合わせると…

この2つを組み合わせると、MATCH関数で「検索値が何行目にあるか」を調べて、その結果をINDEX関数の行番号として使えます。

代表的な形はこんな感じです。

“`
=INDEX(取り出したい範囲, MATCH(検索値, 検索範囲, 0))
“`

たとえば、A列に商品コード、B列に商品名、C列に単価があって、E2に入力した商品コードに対応する商品名を取り出すなら、次のように書きます。

“`
=INDEX(B2:B10, MATCH(E2, A2:A10, 0))
“`

これは「B2:B10から値を取り出すけど、A2:A10の中でE2のコードが何番目かをMATCHで探してね」という意味です。

3. INDEX関数とMATCH関数を組み合わせた実例

具体例で見ると、INDEX関数とMATCH関数の便利さがもっとはっきりします。

例1:社員IDから氏名を表示する

社員一覧表があって、A列に社員ID、B列に氏名、C列に部署、D列にメールアドレスが入っているとします。

別のセルに社員IDを入力したら、その社員の氏名を表示したい場合、数式はこうなります。

“`
=INDEX(B2:B100, MATCH(F2, A2:A100, 0))
“`

F2に入力された社員IDをA2:A100から探して、見つかった位置と同じ行にあるB列の氏名を返してくれます。

例2:左側の列を取り出す(VLOOKUPではできない!)

この方法の強みは、**取り出す列と検索する列を別々に指定できる**点です。

VLOOKUPでは検索列を表の左端に置く必要がありますが、INDEXとMATCHではその制約がありません。

たとえば、A列に氏名、B列に部署、C列に社員IDがある表で、社員IDから氏名を取り出したい場合でも、こう書けば抽出できます。

“`
=INDEX(A2:A100, MATCH(F2, C2:C100, 0))
“`

検索範囲はC列、取り出す範囲はA列というように、**左方向の検索にも自然に対応できる**んです。

例3:縦横に検索する(応用編)

列見出しも使って縦横に検索したい場合は、INDEX関数にMATCH関数を2つ組み合わせる方法が便利です。

たとえば、行方向に商品コード、列方向に「商品名」「単価」「在庫数」などの見出しがある表で、コードと項目名を指定して値を取り出す場合は、こう書けます。

“`
=INDEX(B2:D100, MATCH(F2, A2:A100, 0), MATCH(G2, B1:D1, 0))
“`

1つ目のMATCHで行番号を探して、2つ目のMATCHで列番号を探す仕組みです。

これなら、列番号を手で数えなくても、見出し名に応じて必要な値を取り出せます。

数式をコピーするときの注意点

実際に使うときは、参照範囲を適切に固定することも大切です。

数式を下や横にコピーするなら、検索範囲や取り出し範囲がズレないように「$A$2:$A$100」のような**絶対参照**を使います。

たとえばこんな感じです。

“`
=INDEX($B$2:$B$100, MATCH(F2, $A$2:$A$100, 0))
“`

こうすれば、数式をコピーしても参照範囲が動きません。

Excelの検索・抽出では、関数そのものの理解だけでなく、**コピーしたときに数式がどう変化するか**を意識することがとても重要です。

4. VLOOKUPとINDEX+MATCHの使い分けとエラー対策

VLOOKUP関数とINDEX+MATCHは、どちらか一方だけを覚えればいいというものではありません。

それぞれの使いどころ

**VLOOKUPが向いている場面**
– 表の左端に検索列があって、右側の値を単純に取り出すだけ
– 簡単な一覧表から1項目だけを抽出する場合
– 数式も短く、初心者にも扱いやすい

**INDEX+MATCHが向いている場面**
– 検索列より左側の値を取り出したい
– 列の追加や削除に強い数式を作りたい
– 見出し名を使って柔軟に抽出したい

よくあるミスと注意点

INDEX+MATCHを使うときに注意したいのは、**検索範囲と取り出し範囲の行数をそろえる**ことです。

たとえば、検索範囲がA2:A100なのに、取り出し範囲がB3:B101になっていると、検索結果と返される値が1行ズレてしまいます。

数式自体はエラーにならないこともあるので、気づかないまま誤った結果を使ってしまう危険があります。

特に売上表、請求データ、在庫表など、業務判断に使うデータでは、**範囲の開始行と終了行が一致しているか**を必ず確認しましょう。

#N/Aエラーが出たら

よくあるエラーとして、検索値が見つからない場合の「#N/A」があります。

これは数式の間違いとは限らず、次のような原因でも発生します。

– 検索値が表に存在しない
– 余分なスペースが入っている
– 数値と文字列の形式が一致していない

見た目は同じ「001」でも、片方が文字列、片方が数値として扱われていると一致しない場合があります。

必要に応じてTRIM関数で余分な空白を取り除いたり、表示形式をそろえたりすることが大切です。

エラー表示を見やすくする方法

エラー表示を見やすくしたい場合は、**IFERROR関数**を組み合わせる方法があります。

“`
=IFERROR(INDEX(B2:B100, MATCH(F2, A2:A100, 0)), “該当なし”)
“`

こうすれば、検索値が見つからないときに「該当なし」と表示できます。

ただし、IFERRORは本来のエラー原因を隠してしまうこともあるので、最初から使うのではなく、**数式が正しく動くことを確認してから設定する**のがおすすめです。

原因調査が必要な段階では、あえてエラーを表示させたままにする方が安全な場合もあります。

XLOOKUP関数について

現在のExcelでは、Microsoft 365やExcel 2021以降で使える**XLOOKUP関数**もあります。

XLOOKUPはVLOOKUPの弱点を補う新しい検索関数で、左方向の検索にも対応しています。

ただし、職場や取引先とのファイル共有では、古いExcel環境を使っているケースもあります。

そのため、**互換性を考えるならINDEX+MATCHは今でも実務価値の高い方法**です。

VLOOKUPで対応できる場面、INDEX+MATCHが適している場面、XLOOKUPを使える場面を理解しておくと、Excelでの検索・抽出作業をより安定して進められますよ。

まとめ

この記事を参考に、ぜひINDEX関数とMATCH関数の組み合わせを試してみてください。

最初は少し難しく感じるかもしれませんが、慣れてくるとVLOOKUPよりも使いやすく感じる場面がきっと増えてくるはずです!

広告