Excelでセル同士のうちで、一部でも合致した場合、その値を返す方法はないでしょうか？

勉強不足で申し訳ありません。
A列とB列に不規則なデータ入力がある状況で、一部の文字が合致する場合にC列に合致するA列の
データを返すことは可能なものでしょうか？

（例）
A1セル　〇〇関西〇〇　　　
A2セル　××東北××

B1セル：◇◇◇東北◇◇　→　東北が部分一致するので、C列にA2の値を返す
B２セル：◆◆◆関西◆◆◆　→　関西が部分一致するので、C列にA1の値を返す

No.6 回答者： goomania 回答日時： 2022/01/10 18:09

No.４、5です。

MS365の環境で数式を作成してしまい、「配列数式にする」という説明を漏らしていました。
MS365および最新バージョン以外では配列数式にする必要があります。
前回回答のC1セルに記述する数式

=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($1:$100)/(MMULT(IFERROR(FIND(MID($B1&REPT(" ",2),COLUMN(INDIRECT("R1C1:R1C"&LEN($B1),0)),2),$A$1:$A$100),0),(ROW(INDIRECT("1:"&LEN($B1)))>0)*1)>0),COLUMN(A:A))),"")

は「CTRL+SHIFT+ENTER」で確定してください。

No.5 回答者： goomania 回答日時： 2022/01/06 16:41

No.4です。

大変失礼しました。前回回答の数式(b)において、終端処理を漏らしていました。前回添付画像の例では問題ないのですが、問題が発生するケースがあります。

添付画像①をご覧ください。最低２文字一致ということは左から開始位置をずらして、２文字づつ抽出するとすれば、添付画像①のようにB2セルの右端に「関東」の文字がある場合、開始位置をずらして右端に到達すると、MID関数で２文字抽出しようとしても１文字しか抽出されず、しかもERRORにもなりません。

従って、「東」の一次が抽出され、これと一致する「東北」が抽出されてしまいます。
つまり、文字列の右終端では１文字にならないよう文字数を確保する処理しなければいけません。これを漏らしていたため、上記のような問題が発生してしまうわけです。

そこで、数式(b)を以下のように訂正します。添付画像➁をご覧ください。ご迷惑をおかけいたしました。

=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($1:$100)/(MMULT(IFERROR(FIND(MID($B1&REPT(" ",2),COLUMN(INDIRECT("R1C1:R1C"&LEN($B1),0)),2),$A$1:$A$100),0),(ROW(INDIRECT("1:"&LEN($B1)))>0)*1)>0),COLUMN(A:A))),"")

この数式で一致文字数を増やすのであれば、上記数式の「～REPT(" ",2)～」「～LEN($B1),0)),2～」の「2」の部分をお好みの数値に変更してください。

No.4 回答者： goomania 回答日時： 2022/01/06 15:04

fujillinさんがユーザー定義関数での回答をお示しになっていますが、fujillinさんの関数は説明にあるとおり、

>最初に見つかった値を返します
ということなので、「全てを返す」を組み込み関数で考えてみました。

前提条件として、データA列、B列とも100行以下とします。

添付画像①をごらんください。C1セルに

=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($1:$100)/(MMULT(IFERROR(FIND(MID($B1,COLUMN(INDIRECT("R1C1:R1C"&LEN($B1),0)),1),$A$1:$A$100),0),(ROW(INDIRECT("1:"&LEN($B1)))>0)*1)>0),COLUMN(A:A))),"")･･････(a)

という数式を記述し、C1～E5にコピーしています。

B1セルを例にとると、B1セルの一部(最低１文字)を含むA列のセルを全てC1セルから右方向へ表示します。

しかし、やってみると判りますが、最低１文字、つまりfujillinさんのユーザー定義関数でいうと、デフォルト状態で使用した場合、意図しないものが抽出されるのではないかという懸念がわきました。

添付画像①をみると、「東北」を含むものを探す意図なのに最低１文字にしたため、D1セルには「東」が一致する「関東」も抽出されてしまいます。
また、「関西」を含むものを探す意図なのに、同様にD2セルには「関」が一致する「関東」も抽出されてしまいます。
さらに、「中国」を含むものを探す意図なのに、同様にC4セルには「国」が一致する「四国」も抽出されてしまいます。

という訳で最低１文字は現実的でないと判断し、最低２文字の数式に修正したものが添付画像➁です。C1セルに、

=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($1:$100)/(MMULT(IFERROR(FIND(MID($B1,COLUMN(INDIRECT("R1C1:R1C"&LEN($B1),0)),2),$A$1:$A$100),0),(ROW(INDIRECT("1:"&LEN($B1)))>0)*1)>0),COLUMN(A:A))),"")･･････(b)

という数式を記述し、C1～E5にコピーしています。
コピー範囲は右方向、下方向とも「空白」が表示されるまでオートフィルでコピーすれば良いと思います。

これで、ご質問者のご希望の状態になるのではないかと思います。

数式(a)と数式(b)を比較いただければ分かると思いますが、一致文字数を増やす場合は(a)の「1」が(b)で「2」となっている部分をお好みの数値に変更してください。

No.3 回答者： mike_g 回答日時： 2022/01/05 21:28

私の備忘録の中の一つの、どこかのサイトで、どなたかの昔の回答を応用したつもりなのですが、該当式は私には難解過ぎて上手に解説できません。

そういう次第なので、応用した数式を呈示するだけです。m(_._)m
貴方の希望通りの結果になるかどうか検証してみてください。
うまく動作しなかったら、その旨をお知らせください。m(_._)m

E2: =INDEX(検索文字,MATCH(TRUE,ISNUMBER(FIND(検索文字,A2)),0))
C2: =INDEX(A:A,MATCH(INDEX(検索文字,MATCH(TRUE,ISNUMBER(FIND(検索文字,B2)),0)),E:E,0))
【お断わり】上式は必何れも配列(CSE)数式として入力のこと
式を入力したら、Ctrl+Shift+Enterで確定操作をする数式です。

No.2 回答者： fujillin 回答日時： 2022/01/05 16:21

こんにちは

組み込み関数でやるとかなり面倒そうなので、ユーザ定義関数で処理する一例です。

使い方は、以下のようになります。
　=PartialMatch(検索文字列, 対象セル範囲 [, 最低文字数])

※ ご提示の内容であれば、C1セルに
　=PartialMatch(B1, A$1:A$10)
などと入力し、下方にフィルコピーすれば良いでしょう。
「対象セル範囲」の中から、「検索文字列」と部分一致する値を探し、最初に見つかった値を返します。
「最低文字数」は、連続するn文字以上のマッチを探すことを意味します。
（デフォルトは1です）


※ 事前準備として以下のコードを標準モジュールにコピペしてください。

Function PartialMatch(ByVal S0 As String, ByRef R As Range, Optional n As Long = 1) As String
Dim V0, v
Dim s() As String, i As Long
PartialMatch = ""
If n < 1 Or Len(S0) < n Then Exit Function

ReDim s(1 To Len(S0) - n + 1)
For i = 1 To UBound(s)
　s(i) = Mid(S0, i, n)
Next i

V0 = R.Value
For Each v In V0
　For i = 1 To UBound(s)
　　If InStr(v, s(i)) > 0 Then
　　　PartialMatch = v
　　　Exit Function
　　End If
　Next i
Next v
End Function

No.1 回答者： ggogooID 回答日時： 2022/01/05 13:50

一部でも合致、という条件を判断するには、

1文字ずつ全部判定するしかないね。
式が長ーくなると思うけど、できなくはない。