【改めて】エクセルで「VLOOKUP関数」を使って複数の検索結果を表示できるようにした【書き直し】

前回から記事を書き直しました。

エクセル「VLOOKUP関数」を使って、表から複数の検索結果を引っ張ってこられるようにしました。

検索メニューは、下記のとおりです。

この項目について、エクセルで検索をかけます。

「メロン」、「みかん」、「りんご」、「青森」について、検索をかけてみました。

メロン

みかん

りんご

青森

検索がうまく言っているようです。

前回同様、「VLOOKUP関数」と、「OFFSET関数」を使用しています。

関数は、以下のとおりです。

C列の、5行目から39行目は、以下の関数が入力されています。

C列

=IFERROR(VLOOKUP("*"&$C$2&"*",$C$45:$C$84,1,FALSE),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E5,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E6,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E7,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E8,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E9,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E10,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E11,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E12,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E13,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E14,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E15,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E16,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E17,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E18,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E19,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E20,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E21,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E22,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E23,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E24,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E25,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E26,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E27,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E28,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E29,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E30,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E31,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E32,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E33,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E34,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E35,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E36,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E37,0),1,FALSE)),"")
=IFERROR(IF($C$2="","",VLOOKUP("*"&$C$2&"*",OFFSET($C$45:$C$84,E38,0),1,FALSE)),"")

D列の、5行目から39行目は、以下の関数が入力されています。

D列

ヒットした行番号

=IFERROR(MATCH(C5,$C$45:$C$84,0),"")
=IFERROR(MATCH(C6,OFFSET($C$45:$C$72,D5,0),0),"")
=IFERROR(MATCH(C7,OFFSET($C$45:$C$72,E6,0),0),"")
=IFERROR(MATCH(C8,OFFSET($C$45:$C$72,E7,0),0),"")
=IFERROR(MATCH(C9,OFFSET($C$45:$C$72,E8,0),0),"")
=IFERROR(MATCH(C10,OFFSET($C$45:$C$72,E9,0),0),"")
=IFERROR(MATCH(C11,OFFSET($C$45:$C$72,E10,0),0),"")
=IFERROR(MATCH(C12,OFFSET($C$45:$C$72,E11,0),0),"")
=IFERROR(MATCH(C13,OFFSET($C$45:$C$72,E12,0),0),"")
=IFERROR(MATCH(C14,OFFSET($C$45:$C$72,E13,0),0),"")
=IFERROR(MATCH(C15,OFFSET($C$45:$C$72,E14,0),0),"")
=IFERROR(MATCH(C16,OFFSET($C$45:$C$72,E15,0),0),"")
=IFERROR(MATCH(C17,OFFSET($C$45:$C$72,E16,0),0),"")
=IFERROR(MATCH(C18,OFFSET($C$45:$C$72,E17,0),0),"")
=IFERROR(MATCH(C19,OFFSET($C$45:$C$72,E18,0),0),"")
=IFERROR(MATCH(C20,OFFSET($C$45:$C$72,E19,0),0),"")
=IFERROR(MATCH(C21,OFFSET($C$45:$C$72,E20,0),0),"")
=IFERROR(MATCH(C22,OFFSET($C$45:$C$72,E21,0),0),"")
=IFERROR(MATCH(C23,OFFSET($C$45:$C$72,E22,0),0),"")
=IFERROR(MATCH(C24,OFFSET($C$45:$C$72,E23,0),0),"")
=IFERROR(MATCH(C25,OFFSET($C$45:$C$72,E24,0),0),"")
=IFERROR(MATCH(C26,OFFSET($C$45:$C$72,E25,0),0),"")
=IFERROR(MATCH(C27,OFFSET($C$45:$C$72,E26,0),0),"")
=IFERROR(MATCH(C28,OFFSET($C$45:$C$72,E27,0),0),"")
=IFERROR(MATCH(C29,OFFSET($C$45:$C$72,E28,0),0),"")
=IFERROR(MATCH(C30,OFFSET($C$45:$C$72,E29,0),0),"")
=IFERROR(MATCH(C31,OFFSET($C$45:$C$72,E30,0),0),"")
=IFERROR(MATCH(C32,OFFSET($C$45:$C$72,E31,0),0),"")
=IFERROR(MATCH(C33,OFFSET($C$45:$C$72,E32,0),0),"")
=IFERROR(MATCH(C34,OFFSET($C$45:$C$72,E33,0),0),"")
=IFERROR(MATCH(C35,OFFSET($C$45:$C$72,E34,0),0),"")
=IFERROR(MATCH(C36,OFFSET($C$45:$C$72,E35,0),0),"")
=IFERROR(MATCH(C37,OFFSET($C$45:$C$72,E36,0),0),"")
=IFERROR(MATCH(C38,OFFSET($C$45:$C$72,E37,0),0),"")
=IFERROR(MATCH(C39,OFFSET($C$45:$C$72,E38,0),0),"")

E列の、5行目から39行目は、以下の関数が入力されています。

E列

参照番号

=D5
=IF(D6="","",E5+D6)
=IF(D7="","",E6+D7)
=IF(D8="","",E7+D8)
=IF(D9="","",E8+D9)
=IF(D10="","",E9+D10)
=IF(D11="","",E10+D11)
=IF(D12="","",E11+D12)
=IF(D13="","",E12+D13)
=IF(D14="","",E13+D14)
=IF(D15="","",E14+D15)
=IF(D16="","",E15+D16)
=IF(D17="","",E16+D17)
=IF(D18="","",E17+D18)
=IF(D19="","",E18+D19)
=IF(D20="","",E19+D20)
=IF(D21="","",E20+D21)
=IF(D22="","",E21+D22)
=IF(D23="","",E22+D23)
=IF(D24="","",E23+D24)
=IF(D25="","",E24+D25)
=IF(D26="","",E25+D26)
=IF(D27="","",E26+D27)
=IF(D28="","",E27+D28)
=IF(D29="","",E28+D29)
=IF(D30="","",E29+D30)
=IF(D31="","",E30+D31)
=IF(D32="","",E31+D32)
=IF(D33="","",E32+D33)
=IF(D34="","",E33+D34)
=IF(D35="","",E34+D35)
=IF(D36="","",E35+D36)
=IF(D37="","",E36+D37)
=IF(D38="","",E37+D38)
=IF(D39="","",E38+D39)

これで、検索した値を参照できます。