エクセル関数であいまい検索

エクセル関数であいまい検索をする方法です。

以下の表（データ）があります。

	A	B	C	D	E	F
1	1	EXCEL
2	2	VLOOKUP
3	3	INDEX
4	4	MATCH
5	5	MAX
6	6	UPPER
7

D1に「e」と入力すると、「e」を含む「1 EXCEL」「3 INDEX」「6 UPPER」が表示されるようにします。
欲しい結果は以下となります。

	A	B	C	D	E	F
1	1	EXCEL		e	1	EXCEL
2	2	VLOOKUP			3	INDEX
3	3	INDEX			6	UPPER
4	4	MATCH
5	5	MAX
6	6	UPPER
7

まず、データに計算用の列をひとつ設けます。
C1に　=IF(ISERROR(FIND(UPPER($D$1),B1)),"",ROW())　と入力し、データの一番下までドラッグして下さい。

	A	B	C	D	E	F
1	1	EXCEL	1
2	2	VLOOKUP	2
3	3	INDEX	3
4	4	MATCH	4
5	5	MAX	5
6	6	UPPER	6
7

D1になにも入力していない状態なので、C列には上から１，２，３…と表示されます。

試しにD1に「e」と入力してみて下さい。以下のように、「e」を含む「EXCEL」「INDEX」「UPPER」の行だけに数字が表示されます。

	A	B	C	D	E	F
1	1	EXCEL	1	e
2	2	VLOOKUP
3	3	INDEX	3
4	4	MATCH
5	5	MAX
6	6	UPPER	6
7

次は、このC列を使ってE列に1,3,6を表示させます。
E1に　=IFERROR(INDEX(A:A,SMALL(C:C,ROW(C1)),1),"")　と入力、下までドラッグして下さい。

	A	B	C	D	E	F
1	1	EXCEL	1	e	1
2	2	VLOOKUP			3
3	3	INDEX	3		6
4	4	MATCH
5	5	MAX
6	6	UPPER	6
7

最後にF列に　=IFERROR(INDEX(B:B,SMALL(C:C,ROW(C1)),1),"")　と入力、下までドラッグします。

	A	B	C	D	E	F
1	1	EXCEL	1	e	1	EXCEL
2	2	VLOOKUP			3	INDEX
3	3	INDEX	3		6	UPPER
4	4	MATCH
5	5	MAX
6	6	UPPER	6
7

試しにD1に　「m」と入力してみて下さい。
結果は、以下のように、「m」を含む「MATCH」と「MAX」が表示されます。

	A	B	C	D	E	F
1	1	EXCEL		m	4	MATCH
2	2	VLOOKUP			5	MAX
3	3	INDEX
4	4	MATCH	4
5	5	MAX	5
6	6	UPPER
7

意味の説明は少し難しいのですが、SMALLを使い、C列から１番目（ROW(C1)＝１）に小さいもの、２番目（ROW(C2)＝２）に小さいもの、が上から何番目にあるかをまず計算し、 それを使ってA列とB列から値を拾っています。

C列のUPPER関数は、今回の例がB列が全て大文字の為、D1に「e」と小文字で入力しても「E」と認識される為に使っています。
UPPER関数は小文字を大文字に変換する関数、逆に大文字を小文字に変換する関数はLOWER関数です。

また、データの表とあいまい検索の表示は別シートでも問題ありません。