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

Question

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

tom04 · Accepted Answer

こんにちは！
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

MarcoRossiItaly · Answer

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」を区別した上で、正しいほうの色が表示されます。大文字と小文字は区別されません。

MackyNo1 · Answer

No9の回答の補足です。

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

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

MackyNo1 · Answer

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

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

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

CoalTar · Answer

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

コードは(増やせるけど)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文字のほうを優先して抜き出している

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

CoalTar · Answer

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文字のほうを優先して、抜き出している

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

Visar · Answer

こんにちは。

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

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

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

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

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

KURUMITO · Answer

関数を使って対応するには、色の種類が少ないのでしたら力技で良いのですが多くなった場合には次のように作業列を作って対応することもできますね。
例えばＡ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行目を利用して自由に増やすこともできますね。
作業列が目障りでしたらそれらの列を選択したのちに右クリックして「非表示」を選択すればよいでしょう。

mt2008 · Answer

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

mu2011 · Answer

通常の関数では厳しい（私では無理）ので、ユーザ定義関数は如何でしょうか。
シートタブ上で右クリック→コードの表示→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

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

こんにちは！

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)

No9の回答の補足です。

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

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

2文字(増やせるけど)のコード かつ 表は998色まで増やせるとして(例は7色)

こんにちは。

関数を使って対応するには、色の種類が少ないのでしたら力技で良いのですが多くなった場合には次のように作業列を作って対応することもできますね。

ANo.2です。

通常の関数では厳しい（私では無理）ので、ユーザ定義関数は如何でしょうか。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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

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)

2文字(増やせるけど)のコードかつ表は998色まで増やせるとして(例は7色)