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

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

1 INDEX関数とMATCH関数を組み合わせて検索、抽出
2 エクセルINDEXとMATCH関数にワイルドカードを使ってあいまい検索
3 INDEX関数
4 MATCH関数
5 INDEX関数とMATCH関数で複数条件OR(または)、 AND(かつ)で抽出

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の式は配列が１列なので、列番号を省略できます。次の式でも「合計金額」を求められます。

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」の１番上の行から５番目、左端から３列目の値が返されます。「D6」の値が返されます。

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

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

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

解説

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

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

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

MATCH関数

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

MATCH関数の書式と引数

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

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

解説

照合の型

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

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

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

MATCH関数の基本的な使い方

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

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

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