VLOOKUPを使っていて、左側以外を参照したい時ってありますよね。
そういう時は、INDEX関数とMATCH関数の使い方が分かると簡単に参照することができます。
あなたが今まで使っていたVLOOKUP関数の並び方は、こんな感じですよね。
このようにVLOOKUPでは一番左に「商品名」が入っていた時に右側にある「売上金額」を探す事ができますが、INDEX関数とMATCH関数を使うと参照先がどこにあるか一切気にすることなく探すことができます。
例えばこのように右側に商品名が入っていた時はVLOOKUPじゃ解けないはずです。
このような、VLOOKUPで左側以外に参照先がある場合は「INDEX関数」と「MATCH関数」を組み合わせて解きましょう！
そもそもVLOOKUP関数が分からないという方は「VLOOKUP関数の使い方」をご覧ください。
INDEX関数の使い方
INDEX関数とMATCH関数は単体で使う事が少ないのでを組み合わせて使いますが、１つ１つを理解していないとすぐに分からなくなってしまいます。
なので、まずはINDEX関数の使い方をしっかり覚えましょう。
INDEX関数とは縦と横を数字で指定するとそのセルに書いてある値を返してくれる関数が「INDEX関数」です。
まずは数式を確認しましょう。
数式INDEX(参照, 行番号, [列番号], [領域番号])
数式はこのようになっていますが、数式だけいきなり見ると難しそう・・・。って思いますよね。
大丈夫です！なぜならINDEX関数とMATCH関数を組み合わせるときは「INDEX(参照,行番号)」だけ覚えれば十分だからです。
では、実際にどうやって使っていくかというと、まずは参照先を「呼び出したい列」を選択します。
今回は商品名を取り出したいので「=INDEX(K5:K9」を選択します。
参照先が選択できたら、今度は「選択した列の上から何行目」を取り出したいか「数字」を入力します。
今回は「ゴルフクラブ」を設定したいので、「３」と入力します。
そうすると、「選択した列（商品名）から何行目（３行目）」がINDEX関数を使うと値で出てきます。
これだけだったらINDEX関数はめちゃくちゃ簡単ですよね。
全部覚えようとするとすごく難しいですが、この２つだけだったらすごく簡単です。
MATCH関数の使い方
MATCH関数は、選択した値が指定した範囲内の何行目にあるかを探してくれる関数です。
数式MATCH(検査値, 検査範囲, [照合の種類])
数式だけ見ると分かりにくいかもしれませんが、INDEX関数と同じく１つずつ理解していけば簡単です。
- 検査値＝探したい文字
- 検査範囲＝どこから
- 照合の種類＝０（0は完全一致の略)
の順番で入力していきます。
まずは検査値を入力していきます。
検査値は探したい文字なので「ゴルフクラブ」を選択しましょう。
そうしたら、検査範囲はどこからゴルフクラブを探すのか？ということになるので、商品名が書いてあるところの一覧からですよね。
なので、商品名が書いてあるセルを全て選択します。
そうしたら、「照合の種類」は「0」を入力しましょう！
0を入力することによって「文字が完全一致した時」を探し出してくれます。
このようになりましたね。
なので、このMATCH関数の答えは「３」が答えです。
MATCH関数を使えば「ゴルフクラブ」が３行目に入っているというのが分かりました。
このMATCH関数をINDEX関数の行数に適用すれば、VLOOKUP関数の代わりとして使う事ができます。
「0=完全一致」「1=検査値以下の最大の値を検索」「-1=検査値以上の最小の値を検索」です。
「1」を設定する際は、必ず並び替えで「昇順」に設定します。
「-1」を設定する際は、必ず並び替えで「降順」に設定します。
INDEX関数とMATCH関数でVLOOKUP関数の左側以外を参照する
INDEX関数とMATCH関数を使えるようになった所で、VLOOKUP関数の左側以外を参照する方法をやっていきましょう。
数式=INDEX(参照, MATCH(検査値, 検査範囲, 0))
VLOOKUP関数の代わりに使うINDEX関数とMATCH関数は、このような形の組み合わせになります。
先ほどのINDEX関数とMATCH関数の単体での使い方を理解しながら解いていきましょう！
今回は単価を求めていきたいので、まずはINDEX関数で一覧表の中から単価を全て選択します。
そうしたら、次の行番号にMATCH関数を組み合わせて、調べたい商品名を選択します。
基本的には数式を入れている隣になります。なので今回は隣にある「テニスラケット」です。
今度はテニスラケットが記載されている、商品名一覧を選択しましょう。
ここまで設定できたら最後は照合の種類です。
MATCH関数の照合の種類は「0」を入力します。「0」を入力することにより文字が完全一致した時の行数を出してくれます。
このように「テニスラケット」の単価が「18000」と表示されました。
INDEX関数とMATCH関数を使うことによって、VLOOKUP関数では左端に検査値がない場合でも簡単に解く事ができます。
INDEX関数とMATCH関数は絶対参照の必要がない！？
INDEX関数とMATCH関数を組み合わせた場合、VLOOKUPのように「絶対参照」を使う必要はありません。
えー！と思うかもしれませんが、INDEX関数とMATCH関数では「範囲指定」が必要ないんです。
じゃあどこを指定するのかというと「列番号」です。
「列番号」とは「A,B,C」という部分です。
このように参照範囲に「列番号」を使ってあげることよって、VLOOKUPみたいに全てコピーしても問題なく使えます。
ただし、VLOOKUPと同じく入力されていない場所にはエラーになるので注意しましょう。
INDEX関数とMATCH関数の#N/Aエラーの対処方法
VLOOKUP関数の代わりにINDEX関数とMATCH関数を指定してあげても、そのままではエラーが出てしまいます。
このエラーには、IF関数を使用することによって簡単に対処する事ができます。
数式=IF(C5=””,””,
このようにIF関数をINDEX関数とMATCH関数の前に入力することによって、簡単にエラーが出ないようになります。
「IF関数」「INDEX関数」「MATCH関数」を組み合わせるとこのようになります。
３つの関数を組み合わせると難しく感じるかもしれませんが、１つずつ理解すれば簡単に解けるようになります。
IF関数でエラーを無くす方法については、「VLOOKUP関数でのエラー回避方法」をご覧ください。
動画でも詳しく解説しているので、INDEX関数とMATCH関数にも同じ方法で使えます。
まとめ
INDEX関数とMATCH関数を使えば簡単にVLOOKUP関数の代わりで使う事ができます。
そしてVLOOKUP関数ではできない、検査値が左側以外にある場合でも対処する事ができます。
実はこのINDEX関数とMATCH関数を覚えれば、VLOOKUP関数を使う意味がないことに気がつきます。
なぜなら検査値が左側でも右側でも関係ないからです。
なので慣れてきたらVLOOKUP関数を使うのを止めて、INDEX関数とMATCH関数の組み合わせを使ってみてください！