【Excel・エクセル】MATCH関数とINDEX関数を組み合わせた使い方

検索

もりの

MATCH関数とINDEX関数を使って、VLOOKUP関数みたいな検索ができるって聞いたんだけど?

やまの

できるよ、やり方を見てみよう。

今回のポイント
  • INDEX関数とMATCH関数を組み合わせて、VLOOKUP関数の検索値が左になくてもできるVerになる
  • =INDEX(配列,MATCH(検査値,検査範囲,照合の種類),列番号)

スポンサーリンク

 MATCH関数とINDEX関数の組み合わせの練習ファイルダウンロード

もりの
 
 

一緒にやってみませんか?

【Excel・エクセル】柔軟に検索できる、MATCH関数とINDEX関数の組み合わせ

検索する関数といえば、VLOOKUP関数が思い浮かぶと思います。
が、検索値が範囲の一番左にないといけないという点で、ちょっと煩わしさを感じること、ありませんか?

MATCH関数とINDEX関数を組み合わせれば、検索値の位置を気にすることなく、検索できるんです!

やまの

まずは、MATCH関数とINDEX関数をおさらいしてみよう。

INDEX関数とは?簡単な使い方

INDEX関数は、指定した範囲の中で、指定した行番号、列番号の位置にある値を教えてくれる関数です。

指定する範囲が1つか複数かで、ちょっと書き方が違います。

INDEX関数の書き方(範囲1つ)

=INDEX(配列,行番号,列番号)

INDEXこの範囲の、この位置の値を教えて
配列この範囲の
行番号この位置(上から数えて何行目か)
配列が1列の時は、省略できる
列番号この位置(左から数えて何列目か)
配列が1行の時は、省略できる

範囲が一つの場合は、範囲と上から数えて何行目か、左から数えて何列目かを指定します。

INDEX関数の範囲が一つの場合
範囲が一つの場合

もう一つの書き方もありますが、今回は使わないので割愛しますね。

もりの

INDEX関数は、こっちで詳しく教えてもらったよ!

MATCH関数とは?簡単な使い方

MATCH関数は、指定した値が指定した範囲の中の何行目(何列目)にあるかを教えてくれます。

MATCH関数の書き方

=MATCH(検査値,検査範囲,照合の種類)

MATCHこの値は、この中の何番目にあるか教えて
検査値この値は
検査範囲この中の
照合の種類0:完全一致、-1:以上、1:以下

たとえばセルE2の値が、セルA2~A6の中の何行目にあるか知りたいとき。
式は「=MATCH(E2,A2:A6,0)となります。

MATCH関数で行数を出した画像
MATCH関数の使い方
やまの

詳しくは、こっちのページで解説しているよ。

【Excel・エクセル】MATCH関数とINDEX関数を組み合わせた使い方

組み合わせて使うと、こうなります。

2つを組み合わせた書き方

=INDEX(配列,MATCH(検査値,検査範囲,照合の種類),列番号)

やまの

INDEX関数の行番号を、MATCH関数で表す感じだね。

やってみたら意外と簡単にできちゃうので、ぜひお試しください!

MATCH関数とINDEX関数の組み合わせの使用例

ここに、商品一覧の表があります。

商品Noを使って、商品名を検索したいと思います。

MATCH関数&INDEX関数の練習問題
MATCH関数&INDEX関数の練習問題

VLOOKUP関数で出せそうですが、検索値になりそうな商品Noが、検索範囲の一番右にあるので使えません。

もりの

移動させてもいいけど、他の部分に影響出たら嫌だしな……

そこで、MATCH関数とINDEX関数を組み合わせて、商品名を出してみたいと思います。

やまの

=INDEX($A$2:$D$6,MATCH(A9,$D$2:$D$6,0),2)で、求められるよ。説明するね。

もりの

うわー、ややこしそう。

ステップ1

まずはINDEX関数を使います。

セルB9に「=INDEX(」と入力してください。

「=INDEX(」まで書いた画像
まずはINDEX関数から

ステップ2

次に配列、元になるデータの範囲ですね。

今回は商品一覧の表になるので、セルA2からD6までをドラッグします。

探したい範囲をドラッグした画像
探したい範囲をドラッグ

ステップ3

あとでオートフィルしたいので、範囲を固定するために絶対参照にしておきましょう。

「F4キー」で$マークを付けます。

範囲に$を付けた画像
$マークを付けて、範囲を固定させる

次の引数に移るので、「,(カンマ)」も入力してください。

ステップ4

次に行番号ですが、ここでMATCH関数の出番です!

「MATCH(」と入力して、MATCH関数を書いていきましょう。

「MATCH(」まで入力した画像
MATCH関数さーん、出番ですよー

先ほど選んだ範囲の「上から数えて何行目」部分を、MATCH関数で出してやろうってことですね。

ステップ5

おさらいしておくと、MATCH関数関数は「=MATCH(この値は,この中の何行目?,照合の種類)」でした。

商品Noを手掛かりに商品名を探すので、「この値は」の部分は、商品Noが書かれたセルA9ですね。

セルA9をクリックして、「,(カンマ)」を入力してください。

検索値を指定した画像
VLOOKUP関数の検索値の部分が、コレ

ステップ6

次に「この中の」の部分です。

検索値が商品Noなので、商品Noが書かれたセルD2~D6が該当しますね。

セルD2~D6をドラッグしてください。

検索範囲をドラッグした画像
検索範囲をドラッグ

ステップ7

この範囲もズレると困るので、「F4キー」で絶対参照にしておきましょう。

そして「,(カンマ)」

範囲を固定した画像
オートフィルするので、範囲を固定

ステップ8

次に照合の種類です。
コレ、簡単に言うと完全一致するものでいいのか、近い値のものでいいのかってことなんです。

今回は、1と2は近いから2でもいっか!なんて言われても困るので、完全一致の「0」を選びましょう。

今回は、1と2は近いから2でもいっか!なんて言われても困るので、完全一致の「0」を入力しましょう。

これでMATCH関数部分は終わりなので「)(カッコ閉じ)」します。

また、INDEX関数の行番号が指定できたので、「,(カンマ)」も入力します。

MATCH関数が完成した画像
MATCH関数は完了!INDEX関数も仕上げへ

ステップ9

最後はINDEX関数の列番号です。

最初に選んだ範囲(A2:D6)の、左から数えて何列目の値を知りたいか?ということですね。

今回は商品名を知りたいので、左から数えて2列目、つまり「2」と入力します。

INDEX関数の列番号を指定した画像
ほしい値が、最初に決めた範囲の何列目にあるか

完成!

長い道のりでしたが、ようやく式が完成したので、「)(カッコ閉じ)」を入力して、エンターキーを押してください。

無事、商品Noから商品名を検索できました!

MATCH関数とINDEX関数で、商品名を検索できた画像
MATCH関数とINDEX関数で、商品名を検索できた
もりの

長かった……

やまの

お疲れさま!

オートフィルしてみると、他の商品名も一括で検索できましたね!

オートフィルした画像
他の商品名もすぐにわかる

MATCH関数とINDEX関数の組み合わせを動画で見る

これで、検索値が検索範囲の左端になくても、値を検索できますね!
それでは、お疲れさまでした!

今回のおさらい
  • INDEX関数とMATCH関数を組み合わせて、VLOOKUP関数の検索値が左になくてもできるVerになる
  • =INDEX(配列,MATCH(検査値,検査範囲,照合の種類),列番号)

数式・関数,Excel検索

Posted by morino