特定の文字列を含む場合指定の値を返す エクセル関数

質問

Vlookupだとセルの値全てで照合しますが、特定の文字列を含む場合でVlook的に利用するにはどうすればよいでしょうか。　　find やIFだとキリがないかな・・と思い格闘中です。　
アドバイスいただけると幸いです。よろしくお願いします。

回答No.1

tom04 · ベストアンサー

こんにちは！
VBAになってしまいますが、一例です。

↓の画像のようにSheet1にSheet2の表に基づいて表示するとします。

Alt+F11キー　→　メニュー　→　挿入　→　「標準モジュール」を選択　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

Application.ScreenUpdating = False
Range(wS1.Cells(2, 2), wS1.Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2)).ClearContents
For k = 2 To wS2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
If InStr(wS1.Cells(i, 1), wS2.Cells(k, 1)) > 0 Then
wS1.Cells(i, 2) = wS2.Cells(k, 2)
End If
Next i
Next k
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数でないので、データ変更があるたびにマクロを実行する必要があります。

お望みの方法でなかったらごめんなさいね。m(_ _)m

お礼

ありがとうございます。　しかも、すごいスピードで。

試してみたらうまくいきました。　いくつかCとCOとかで重複してしまうものがありましたが、仕方がないのかもしれません。

非常に助かりました。

回答No.11

MarcoRossiItaly

B5　=index(D$5:E$9,max(index((row(D$5:D$9)-row(D$4))*countif(a5,"*"&D$5:D$9&"*")*(len(D$5:D$9)=max(index(countif(a5,"*"&D$5:D$9&"*")*len(D$5:D$9),))),)),2)

一応この数式では、Ctrl+Shift+Enter の操作は不要です。しかしご覧のとおり、数式そのものがだいぶ難解です。なお「p」と「cp」、「g」と「rg」を区別した上で、正しいほうの色が表示されます。大文字と小文字は区別されません。

お礼

ありがとうございます。　画像もつけていただき非常にありがたいです。

数式がかなり入れ子になっているので、理解している人しか編集できませんが、勉強になりました。　こういうやり方もありですね。

回答No.10

MackyNo1

No9の回答の補足です。

質問の添付画像を見ないで一般論で回答してしましました。
検索するデータにはPとcp、およびGとMGがあるのですね。

この場合は、PおよびGはそれぞれ該当データが2つあることになりますので、下の方のデータ（cpとMGの行）を引っ張ってきます。
したがってcpを検索した（1つしか該当データが無い）場合には正確な位置を返しますので、一覧表でPはcpの下に配置してください。
同様にMGの下にGを配置しすれば、提示した数式をそのまま使用できます。

お礼

わざわざ有難うございます。

回答No.9

MackyNo1

添付画像のようなリストがある場合、以下のような式を入力してCtrl＋Shift+Enterで確定すればご希望の一部検索データを引っ張ってくることができます。

=INDEX(B:B,MAX(COUNTIF(D2,"*"&$A$2:$A$9&"*")*ROW($A$2:$A$9)))

該当データが2つある場合は下の方のデータを引っ張ってきます。
また、該当データが無い場合は、C1セルの値（返す値）を表示しますので、このセルに空白文字列（=""）またはスペースなどを入力しておけばエラー処理の代わりになります。

お礼

有難うございます。
エラー処理の方法まで親切にありがとうございます。
NAが出ちゃうと面倒ですからね。

シンプルな方法で非常に参考になりました。

回答No.8

某HN クロメート(Chromate)（@CoalTar）

添付図が表示されていないようなのでもう一回

コードは(増やせるけど)2文字まで かつ 表は998色まで増やせるとして(例は7色)
B4セルは
=INDEX($E$4:$E$11,MOD(MAX(EXACT(RIGHT(LEFT(A4,FIND(" ",A4&" ")-1),{1,2}),
$D$4:$D$11)*10^{4,5}+ROW($D$4:$D$11)-ROW($D$3)),10^4))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
添付図参照、

半角空白文字までを抜き出し、さらに、右から1文字と2文字を抜き出して、表と完全比較。
(大文字と小文字で違う文字とする)
1文字と2文字で両方一致する場合、2文字のほうを優先して抜き出している

詳細な解説はご要望があれば。

お礼

ありがとうございます。

こういうやり方もありですね。　皆さん色々工夫しますね。　
流石です。　ある意味エクセルの関数の組み合わせなどは
クイズみたいで面白いですね。

回答No.7

某HN クロメート(Chromate)（@CoalTar）

2文字(増やせるけど)のコード かつ 表は998色まで増やせるとして(例は7色)
B4セルは
=INDEX($E$4:$E$11,MOD(MAX(EXACT(RIGHT(LEFT(A4,FIND(" ",A4&" ")-1),{1,2}),$D$4:$D$11)*10^{4,5}+ROW($D$4:$D$11)-3),10^4))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
添付図参照、
半角空白文字までの右から1文字と2文字を抜き出して、完全比較。
2文字のほうを優先して、抜き出している

詳細な解説はご要望があれば。

お礼

ありがとうございました。

このようなやり方も研究してみます。
まったく思いつきませんでした。

回答No.6

Visar

こんにちは。

難しく考えすぎずに、
VLOOKUPの第四引数をFALSEにして、

VLOOKUP(検索値, 範囲, 列番号, [検索の型])

検索値にワイルドカードを使用すれば、
VLOOKUP関数自身で解決できませんか。?

例 - 検索値を"み*"にすると、"みどり"等にヒットしますが・・・。

はずしていたら、ごめんなさい。

お礼

ありがとうございます。

検索対象の文字列が結構長いのがあったりして、
ワイルドカードだと関係ないのもひっパテしまいます。。

とはいえ、Vlookdでワイルドカードというやり方は
今後活用させていただきます。勉強になりました。

回答No.5

KURUMITO

関数を使って対応するには、色の種類が少ないのでしたら力技で良いのですが多くなった場合には次のように作業列を作って対応することもできますね。
例えばＡ3セルから下方にバックＰといった製品名が入力されているとします。
Ｄ1セルから右横方向にはＰ、ｃｐ、Ｒ、Ｇといった文字が入力されその2行目にはピンク、チェリーピンクなどの色が入力されているとします。
Ｄ3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。

=IF(D$1="","",IF(COUNTIF($A3,"*"&D$1&"*")=0,"",MAX($C3:C3)+1))

最後にお求めの色の表示をＢ列に表示させるためにＢ3セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(D3:XX3)=0,"",INDEX(D:XX,2,MATCH(MAX(D3:XX3),D3:XX3,0)))

これで対応する色が表示されますね。なお色の種類は作業列の1行目と2行目を利用して自由に増やすこともできますね。
作業列が目障りでしたらそれらの列を選択したのちに右クリックして「非表示」を選択すればよいでしょう。

お礼

ありがとうございました。

このような関数の組み合わせの仕方は全く思いつきませんでした。
参考にさせていただきます。

回答No.4

mt2008

ANo.2です。
一つ訂正です。
C3に入れて配列式として確定する式は↓にしてください
=MAX(ISNUMBER(FIND(F$3:F$7,A3))*E$3:E$7)

お礼

わざわざ有難うございます。

回答No.3

mu2011

通常の関数では厳しい（私では無理）ので、ユーザ定義関数は如何でしょうか。
シートタブ上で右クリック→コードの表示→VBE画面メニューで挿入→標準モジュール→サンプルコード貼り付けてお試しください。

使用例
仮に抽出表をA1:B4、対応表がD1:E5、先頭行は見出し行とします。
抽出表のB2に=mylook(A2,$D$2:$E$5)、下方向にオートフィル

Function mylook(r1, r2)
Dim i As Long
For i = 1 To r2.Rows.Count
If r2(i, 1) <> "" Then
If r1 Like "*" & r2(i, 1) & "*" Then
mylook = r2(i, 2)
Exit Function
End If
End If
Next
mylook = CVErr(xlErrNA)
End Function

お礼

ありがとうございます。

なるほど・・　ユーザー定義を利用してしまうんですね。
ちょっと面白そうなので勉強してみます。

回答No.2

mt2008

作業列と配列式を使った例です。
色の一覧表の先頭に連番を付けて下さい（添付の図ではE列）。
次に、添付の図のC3セルに↓の式を入れてCtrl+Shift+Enterで配列式として確定してください。
=MAX(ISNUMBER(FIND(F3:F7,A3))*E3:E7)

次にB3セルに↓を入力
=VLOOKUP(C3,E$3:G$7,3,FALSE)

次にB3:C3セルをB4:C5にもコピー。
最後に、作業列（C列、E列）を非表示にします(見せたくないなら）。

ただし、「バッグPR」の様にカラーコードと一致する文字が複数使用されている場合、後勝ち（この場合レッド）になります。

お礼

ありがとうございます。

＞ただし、「バッグPR」の様にカラーコードと一致する文字が複数使用＞されている場合、後勝ち（この場合レッド）になります。

そうですね。対象文字列が長いため、コードが競合してしまいます。。
ある程度はアナログで対応できるよう、検索文字列を一時的に置換するなど工夫してみます。