エクセルVLOOKUP関数で値を検索する方法はよく知られていますが、エクセルINDEX関数とMATCH関数を組み合わせで検索、抽出する方法は知っているでしょうか。この２つの関数を組み合わせると、VLOOKUP関数ではエラーがでる場合でも、エラーになりません。検索する列がどこにあっても、値を抽出できます。エクセルINDEX関数とMATCH関数を組み合わせた抽出は、最強といっていいでしょう。INDEX関数とMATCH関数を組み合わせた使い方は、ぜひ覚えておきたい方法です。
目次
INDEX関数とMATCH関数を組み合わせて、VLOOKUPと同じ抽出
VLOOKUP関数と同じ、値の検索、抽出ができます。
A列の「契約№」を検索値として、C列「合計金額」に、右表のH列「合計金額」を表示してみましょう。
C2に、式を入力します。
C2 =INDEX($H$2:$H$9,MATCH(A2,$E$2:$E$9,0),1)
C2の式は「MATCH(A2,$E$2:$E$9,0)」で行番号を取得しています。
「5,200」が返されます。
C2の式を、下にドラッグしましょう。
C列に「合計金額」が表示されました。
C2の式は配列が１列なので、列番号を省略できます。次の式でも「合計金額」を求められます。
C2 =INDEX($H$2:$H$9,MATCH(A2,$E$2:$E$9,0))
INDEX関数の行番号と列番号にMATCH関数で指定して抽出
INDEX関数の行番号と列番号にMATCH関数を指定して、複雑な抽出をしてみましょう。
右表は、商品名に対応する型番の金額表です。金額表からD列に、商品名に対応する型番の「金額」を表示させます。
D2に、式を入力しましょう。
D2 =INDEX($G$2:$J$10,MATCH(B2,$F$2:$F$10,0),MATCH(A2,$G$1:$J$1,0))
「2,000」が返されます。
D2の式を、下にドラッグしましょう。
D列に「金額」が表示されました。
INDEX関数
エクセルINDEX関数は、行番号と列番号が交差する位置にあるセルの値を返す関数です。エクセルINDEX関数には、配列形式とセル範囲形式の2つの使い方があります。
配列形式は、指定した範囲からある値を返します。セル範囲形式は指定した範囲からセルの参照を返します。
INDEX関数、配列形式の書式と引数
INDEX(配列, 行番号, [列番号])
| 配列 | セルの範囲を指定します。 |
| 行番号 | 値を返す行を数値で指定します。 |
| 列番号 | 値を返す列を数値で指定します。 |
配列： 配列が１行または１列の場合、行番号または列番号を省略できます。
行番号： 行番号を省略した場合は、必ず列番号を指定します。
列番号： 列番号を省略した場合は、必ず行番号を指定します。
INDEX関数、配列形式の基本的な使い方
Aクラスの科目ごと、月別平均点の表があります。３月の数字の平均点を抽出してみましょう。
D11に、式を入力しましょう。
D11 =INDEX(B4:G9,3,2)
「68」が返されます。
D11には、範囲「B4:G9」の一番上から３番目の行と、左端から２番目の列が交差するセルの値が返されます。
INDEX関数、セル範囲形式の書式と引数
INDEX(参照, 行番号, [列番号], [領域番号])
| 参照 | 1つまたは複数のセルの参照を指定します。 |
| 行番号 | セル参照を返す行位置を数値で指定します。 |
| 列番号 | セル参照を返す列位置を数値で指定します。 |
| 領域番号 | 任意です。行番号と列番号が交差する位置を返す参照の範囲を1つ選択します。最初の範囲は「１」、次は「２」というように番号で領域を指定します。 |
参照： 複数の範囲を指定する場合は、複数指定した範囲全体ををかっこ () で囲み、１つの範囲と範囲の間をを半角のコンマ（,）で区切ります。各領域が1行または1列の場合、行番号または列番号はそれぞれ省略できます。
INDEX関数、セル範囲形式の基本的な使い方
６月の英語の平均点を２つの領域を指定して求めてみましょう。
D11に、式を入力します。
D11 =INDEX((B4:G5,B8:G9),2,6,2)
「65」が返されます。
D11の式は、２つの領域から２番目の領域が指定されています。
２番目の領域の、一番上の行から２つ目の行と、左端から６番目の列が交差するセルの値が返されます。
MATCH関数
指定した範囲内から検査値を探しだし、その値の相対的な位置を返します。
MATCH関数の書式と引数
MATCH(検査値, 検査範囲, [照合の型])
| 検査値 | 検索する値を指定します。検査値には、数値、文字列、または論理値、またはこれらの値に対するセル参照を指定できます。 |
| 検査範囲 | 検索するセルの範囲を指定します。 |
| 照合の型 | -1、0、1の数値のいずれかを指定します。省略した場合は自動的に１が選択されます。 |
照合の型
「１」：検査値以下の最大の値を返します。この場合、検査範囲を昇順の並べ替える必要があります。
「０」：検査値と一致する一番最初の値を返します。行の場合は左から、列の場合は上から最初の値を返します。
「-1」：検査値以上の最小の値が検索されます。この場合、検査範囲を降順に並べ替える必要があります。
MATCH関数の基本的な使い方
Aクラスの平均点の表から目的の値の位置を検索してだしてみましょう。
５月で、80点の位置を求めます。
D11に、式を入力しましょう。
D11 =MATCH(80,B8:G8,0)
「５」が返されます。
次は、科目から世界史の位置を求めてみましょう。
D13に式を入力します。
D13 =MATCH(“日本史”,B3:G3,0)
「４」が返されます。
こちらの記事もご覧ください。⇒ワード・エクセル・パワーポイント・アクセスが初心者でも短期間でマスターできる楽パソ！音声と映像で自宅で学べる。
こちらの記事もご覧ください。⇒マイクロソフトオフィスソフトとプログラミングスキルが初心者でも短期間でマスターできる楽パソ！音声と映像で自宅で学べる。
INDEX関数とMATCH関数でVLOOKUPにはできない検索ができる
INDEX関数とMATCH関数を組み合わせると、VLOOKUP関数と同じように検索、抽出ができますが、VLOOKUPでの検索よりも高度な検索ができます。VLOOKUP関数を使うと、検索列は検索範囲の一番左側になります。INDEX関数とMATCH関数を組み合わせると、検索列はどこにあっても検索できます。数式を作成した後、検索範囲内の列を削除しても、エラーになりません。VLOOKUPにはできない検索、抽出ができます。
こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って複数条件で値を取得する
| エクセル・ワード・パワーポイント・アクセス・マクロVBA・WEBプログラミングを入門から応用までらくらくマスターできる ➡ 動画パソコン教材 |
ワード・エクセル・パワーポイント・アクセスを入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓
ワード・エクセルパワーポイント・アクセスとエクセルマクロVBA・WEBプログラミング・パソコンソフト作成を入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓
ワード・エクセル・パワーポイント・アクセスとWEBプログラミングを入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓
エクセルのマクロとVBAを入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓
VBA・Java・WEB・C#・HTML/CSSプログラミングを入門から応用まで映像と音声でわかりやすく学ぶことができます。
↓ ↓ ↓ ↓ ↓
