エクセルINDEX関数とMATCH関数でワイルドカードであいまい検索

エクセルINDEXとMATCH関数を組み合わせて、検索、抽出する方法はよく使われます。そこで、INDEXとMATCH関数に、ワイルドカードが使えたらどうでしょうか。最強といっていい検索、抽出ができるようになります。ワイルドカードは使えない関数もありますが、MATCH関数には使えます。ここでは、INDEX関数とMATCH関数を組み合わせて、ワイルドカードを使ったあいまい検索の検索と抽出の方法を、分かりやすく説明しています。

 

 

INDEX関数とMATCH関数を組み合わせて検索、抽出

INDEX関数とMATCH関数を組み合わせた、基本的な使い方です。VLOOKUP関数と同じ、値の検索ができます。

A列の「契約№」を検索値として、B列「合計金額」に、右表のG列「合計金額」を表示してみましょう。

 

B2に、式を入力します。

B2 =INDEX($G$2:$G$9,MATCH(A2,$D$2:$D$9,0),1)

B2の式はINDEX関数の行番号に「MATCH(A2,$E$2:$E$9,0)」を指定しています。「MATCH(A2,$E$2:$E$9,0)」が行番号になります。

「3,500」が返されます。

B2の式ドラッグして、下にコピーしましょう。

B列に、合計金額」が表示されました!

B2の式は配列が1列なので、列番号を省略できます。次の式でも「合計金額」を求められます。

C2 =INDEX($G$2:$G$9,MATCH(A2,$D$2:$D$9,0))

 

 

エクセルINDEXとMATCH関数にワイルドカードを使ってあいまい検索

検査値には「商品番号」を使います。INDEX関数とMATCH関数を組み合わせて、検査値にワイルドカードを使ってあいまい検索をしてみましょう。

B列の「金額」に、右表のF列「金額」を表示させます。

 

B2に、式を入力しましょう。

B2 =INDEX($F$2:$F$9,MATCH(“*”&A2,$D$2:$D$9,0))

B2の式の検索値「”*”&A2」は、「A2で終わる」文字列を表しています。「”*”&A2」を検査値として、範囲「D2:D9」のD列「商品№」から値を検索しています。

「1,000」が返されます。

B2の式をドラッグして、下にコピーしましょう。

B列に、「金額」が表示されます。

 

 

?疑問符であいまい検索

?疑問符を使って、B2に式を入力すると、次のようになります。

B2 =INDEX($F$2:$F$9,MATCH(“?”&A2,$D$2:$D$9,0))

「1,000」が返されます。

 

関数の説明

INDEX関数

エクセルINDEX関数は、行番号と列番号が交差する位置にあるセルの値を返す関数です。エクセルINDEX関数には、配列形式とセル範囲形式の2つの使い方があります。

配列形式は、指定した範囲からある値を返します。セル範囲形式は指定した範囲からセルの参照を返します。

INDEX関数、配列形式の書式と引数

INDEX(配列, 行番号, [列番号])

配列セルの範囲を指定します。
行番号値を返す行を数値で指定します。
列番号値を返す列を数値で指定します。

 

解説

配列:  配列が1行または1列の場合、行番号または列番号を省略できます。

行番号: 行番号を省略した場合は、必ず列番号を指定します。

列番号: 列番号を省略した場合は、必ず行番号を指定します。

INDEX関数、配列形式の基本的な使い方

数式説明
 F2 =INDEX(B2:D10,5,3)範囲「B2:D10」の1番上の行から5番目、左端から3列目の値が返されます。「D6」の値が返されます。

INDEX関数、セル範囲形式の書式と引数

INDEX(参照, 行番号, [列番号], [領域番号])

参照1つまたは複数のセルの参照を指定します。
行番号セル参照を返す行位置を数値で指定します。
列番号セル参照を返す列位置を数値で指定します。
領域番号任意です。行番号と列番号が交差する位置を返す参照の範囲を1つ選択します。最初の範囲は「1」、次は「2」というように番号で領域を指定します。
解説

参照:  複数の範囲を指定する場合は、複数指定した範囲全体ををかっこ () で囲み、1つの範囲と範囲の間をを半角のコンマ(,)で区切ります。各領域が1行または1列の場合、行番号または列番号はそれぞれ省略できます

INDEX関数、セル範囲形式の基本的な使い方

数式説明
C12 =INDEX((B2:D4,B7:D10),2,3,2)領域番号は2です。範囲の2番目「B7:D10」から値を返します。「B7:D10」の1番上の行から2番目、左端から3列目の値が返されます。「D8」の値が返されます。

 

 

MATCH関数

指定した範囲内から検査値を探しだし、その値の相対的な位置を返します。

MATCH関数の書式と引数

MATCH(検査値, 検査範囲, [照合の型])

検査値検索する値を指定します。検査値には、数値、文字列、または論理値、またはこれらの値に対するセル参照を指定できます。
検査範囲検索するセルの範囲を指定します。
照合の型-1、0、1の数値のいずれかを指定します。省略した場合は自動的に1が選択されます。

 

解説

照合の型

「1」:検査値以下の最大の値を返します。この場合、検査範囲を昇順の並べ替える必要があります。

「0」:検査値と一致する一番最初の値を返します。行の場合は左から、列の場合は上から最初の値を返します。

「-1」:検査値以上の最小の値が検索されます。この場合、検査範囲を降順に並べ替える必要があります。

MATCH関数の基本的な使い方

数式説明
 C2 =MATCH(“ノート”,A2:A10,0) 「A2:A10」の範囲から「ノート」と一致している値を探して、その位置を返します。「A6」に「ノート」が入力されています。「C2」には「5」が返されます。

MATCH関数で使えるワイルドカード

ワイルドカード説明
任意の文字列を検索
?任意の 1 文字を検索
~? や * の前につけて、ワイルドカード「*」「?」を検索

INDEX関数とMATCH関数で複数条件OR(または)、 AND(かつ)で抽出

エクセルINDEX関数とMATCH関数ではワイルドカードを使って検索、抽出ができますが、複数条件で値を検索することもできます。INDEX関数とMATCH関数を組み合わせると、OR(または)と、AND(かつ)の複数条件で値を検索することもでき、より複雑な抽出ができます。

こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って複数条件で値を取得する