2022.02.14スポンサーリンク今回の記事では、最強に便利と紹介したVLOOKUP関数と同じように条件に合う特定の値を取り出すことができ、代わりの関数としても使うことが出来る、INDEX関数とMATCH関数についてそれぞれ解説します。INDEX、MATCH関数はINDEX関数で範囲を指定し、MATCH関数で条件に一致するデータの行番号を検索するという流れでVLOOKUP関数と同じような働きをさせることができます。計算式は関数を入れ子(ネスト)にさせたりと複雑になってしまいますが、VLOOKUP関数が使えない時に代替関数として覚えておくととても便利です。どのような場合にVLOOKUP関数の代わりとして使えるかと、どのように計算式を作るかはこの先の記事で詳しく解説します。【応用】INDEX、MATCH関数で複数の条件を指定する方法とは?スポンサーリンク好きな項目から読めます1.VLOOKUPとの使い分けはどのようなときか2.INDEX関数とは3.MATCH関数とは4.INDEX、MATCH関数の使い方1.VLOOKUPとの使い分けはどのようなときかVLOOKUP関数と同じように特定の値に対する値を取り出すことができるINDEX関数とMATCH関数ですが、どのようなときに使い分ければ良いのでしょうか?VLOOKUP関数を使いなれている方なら、困ったことがあると思いますが、VLOOKUP関数は検索する列が範囲の一番左に無いと検索をすることができません。いうことかというと、下の表を使って説明していきます。表1VLOOKUP関数の説明で使った表を少し変えて、会社名を入れると取引先コードを表示する表を作ったとします。表をよく見るとマスタの取引先コードが会社名より左側にあります。ここからはおさらいですが、VLOOKUP関数は、=VLOOKUP(検索値,範囲,列番号,検索方法)という組み合わせでできています。Excel時短ワザ列番号は、検索値がある列から何列目という数字を入力しますが、”1”以上の数字を入力しなければいけません。そのため、”0”やマイナスの数値を入力することができないので、検索値と同じ列か右側にある列しか参照することができません。そこで役に立つのがINDEX関数とMATCH関数になります。こちらを使うことで、データの位置に左右されず参照をすることができます。それなら常にINDEX関数とMATCH関数を使った方がいいのでは?と思われるかもしれませんが、計算式のシンプルさなどVLOOKUP関数の方が優れている点もあるので、状況に応じて使い分けていただければと思います。2.INDEX関数とはそれではまずは、INDEX関数から紹介していきたいと思います。表2計算式のガイドを見ると何やら複雑そうな文字がたくさん出てきました。文字で見ると複雑ですが、表を使って説明すると分かりやすいです。表3=INDEX(A8:D13,3,4)まず、参照の部分にデータの範囲を入力します。ここではA8:D13の範囲です。参照の一番左上のセルから、指定した行番号、列番号分だけ移動させたセルの値を参照します。ここでは、A8セルから下に3行、右に4列動かした先の”千葉県”の値が返ってきました。決めた範囲の指定した行と列分移動した先の値を参照するのが、INDEX関数です。後で説明をしますが、実際にVLOOKUP関数のような使い方をする場合は、=INDEX(参照,MATCH関数,列番号)という使い方をします。それでは次にMATCH関数について紹介します。↓INDEX関数と似た使い方ができるOFFSET関数についてはこちらを参照ください↓VLOOKUPとネストで範囲が可変に!OFFSET関数の使い方とは?3.MATCH関数とはMATCH関数とは検索値がその表の中のどこにあるかを検索することができる関数です。表4こちらも何やら複雑そうな文字がたくさん出てきました。こちらも表を使って説明していきます。表5=MATCH(F8,D8:D13,0)まず、検査値の部分には、検索したいセルもしくは数値を直接入力します。※上の表ではF8の千葉県です。次に検査範囲に検索したい範囲を入力します。上の表では、所在地の列の何番目に千葉県があるか知りたいので、D8:D13の範囲を指定します。最後の照合の種類ですが、こちらはVLOOKUP関数と同じ使い方をする場合、完全一致を使ってください。※VLOOKUPのFALSEの部分です上の表の場合、千葉県はD8から3行目にあるので”3”という数値が返されます。今度は下の業種が何列目かを検索してみましょう。表6=MATCH(F9,A8:D8,0)今度は、業種という項目が検査範囲の何列目にあるか確認する計算式です。この場合、業種はA8から数えて3列目にあるので”3”という数値が返されます。それでは、いよいよINDEX関数とMATCH関数を使って、VLOOKUP関数のような使い方をしてみましょう。上でも紹介したように、計算先は、=INDEX(参照範囲,MATCH(検索値,検索する列,0),列番号)というように入力します。青い部分がINDEX関数で赤い部分がMATCH関数です。MATCH関数で返される数値が行番号になります。それでは、実際に説明しながら入力していきましょう。表7上の表は、セルB1に会社名を入力すると、下のマスタを参照し、セルA3からA5に内容が自動的に入力されるというものです。VLOOKUP関数のときとほとんど同じ表ですが、マスタの取引先コードが会社名より左にあるため、VLOOKUP関数を使うことができません。表8最初に、セルB3にINDEX関数の部分を入力します。検索範囲を選択するので、マスタ全体を選択します。※この時フィルをかける時のために、検索範囲には$をつけて絶対参照をしておきます。表9次に、コンマを打ち、MATCH関数の部分を入力します。MATCH関数は、検査値のところに検索値(今回はB1セル)を入力し、検査範囲の部分に会社名が入っているマスタの列全体を指定します。※こちらもそれぞれ$をつけて絶対参照しておきましょう。照合の種類は、上の説明の通り、”0”を入力します。表10MATCH関数を閉じ、コンマを打ったら、最後は列番号を指定します。INDEX関数で選択した範囲の中で取引先コードは1列目にあるので、”1”と入力します。表11出来上がった計算式は、=INDEX($A$9:$D$13,MATCH($B$1,$B$9:$B$13,0),1)カッコを閉じ、計算式を完成させると、無事、○○工業の取引先コードを入力することができました。表12後は、業種と所在地のセルまでフィルで計算式をコピーし、計算式の最後の参照する行の部分をそれぞれ業種と所在地の列番号に変更します。上の表では、所在地を入力したいので列番号に”4”を入力します。表13無事、業種と所在地も入力することができました。いかがだったでしょうか?VLOOKUP関数と比べると少し計算式が複雑ですが、検索したい列の場所に左右されないというのは大きなメリットです。VLOOKUP関数が使えない場合にぜひ使用してみてください。この記事の応用としてINDEX、MATCH関数で複数の条件を指定する方法を解説した記事も用意しました。INDEX、MATCH関数を使いこなしたいと考えている方はぜひそちらもお読みください。最後までお読みいただきありがとうございました。【応用】INDEX、MATCH関数で複数の条件を指定する方法とは?INDEX、MATCH関数を使っていて複数の条件を指定したいと思ったことはありませんか?INDEX、MATCH関数は計算式を変えることで複数の条件の指定を簡単にすることができます。この記事で詳しく解説していますのでぜひご覧ください。関数編VLOOKUP関数で別シートからデータを参照する方法とは?VLOOKUP関数の参照範囲を別シートに指定したいと思ったことはありませんか?この記事ではVLOOKUP関数での参照範囲を別シートに指定する方法と別シートにした方が良い理由を解説しています。VLOOKUP関数を使う前にぜひお読みください。関数編セルの数を数える!4つのCOUNT関数の使い分けかたとは?Excelを集計していて参加者の数を関数で表示したいと思ったことはありませんか?そのようなときはCOUNT関数を使うことで簡単に数を数えることができます。この記事で4つのCOUNT関数について詳しく解説していますのでぜひご覧ください。住所録の郵便番号を一度にハイフンで区切る2つの方法とは?Excelで大量のデータを扱うときにデータの入力方法が違っていて困ったことはありませんか?この記事では郵便番号のハイフンを例に指定したところに記号や文字列を追加する方法を解説しています。データの入力方法を一度に変換したい方はぜひご覧ください。【数値と文字列】VLOOKUP関数で同じ数字なのにエラーが出るときの対処法とは?ExcelでVLOOKUP関数を使っていて同じ数字なのに結果がエラーになってしまうことはありませんか?それは数字の設定が数値と文字列という違う表示形式にされている可能性があります、この記事で対処法を解説していますのでぜひご覧ください。Excel時短ワザ管理人のSHUNです。普段は会社員として働いています。会社員が仕事で使えるExcelの時短ワザを紹介しています。おすすめの記事はVLOOKUP関数とIF関数の記事です。記事のリクエストがありましたらツイッターかお問い合わせフォームにお気軽にリクエストをお願いします。defined.")}))})()タイトルとURLをコピーしました。