エクセルのVLOOKUPで一部を含む場合

質問

おそらくVLOOKUPでできると思うのですが
A列とB列にデータがあり
C列を検索値としてD列に値を表示したいのです。

A 　B
北海道北広島市　123
福岡県筑紫野市　5678
沖縄県名護市　91018

C D
福岡県筑紫野市山家999-99
北海道北広島市富ヶ岡112-998
沖縄県名護市字安部1121

D列に
5678
123
91018

と表示したいのです。

よろしくお願いします。

回答No.7

tom04 · ベストアンサー

No.6です！
投稿した後気づきました。

単にD列に表示するだけでなく、表示する順番も大切なのですよね？

VBAになってしまいますが、一例です。
画面左下にある操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j As Long
For i = 2 To Cells(Rows.Count, 3).End(xlUp).Row
For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 3) Like Cells(j, 1) & "*" Then
Cells(i, 4) = Cells(j, 2)
End If
Next j
Next i
End Sub 'この行まで

今回も1行目はタイトル行で2行目からデータがあるとしています。
何度も失礼しました。m(__)m

お礼

うまくいきました。

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

自分の力ではどうすることもできませんでしたので
とても助かりました。

他の方もいろいろと教えていただき
誠にありがとうございました。

回答No.6

tom04

こんばんは！
横からおじゃまします。

↓の画像でD2セルに
=IF(OR(ISNUMBER(FIND($A$2:$A$4,C2))),B2,"")
これは配列数式になってしまいますので、単にコピー＆ペーストしただけではちゃんと表示されないと思います。
この画面からD2セルに貼り付け後に数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
そしてD2セルのフィルハンドルでダブルクリックすると画像のような感じになります。

尚、数式内の範囲指定「$A$2:$A$4」は必ずA列のデータが入っている最後の行までを行います。
仮に空白行まで範囲指定をしてしまうとD列すべてにデータが表示されてしまいます。

以上、参考になれば良いのですが
的外れならごめんなさいね。m(__)m

回答No.5

mu2011

NO2です。
>A列には”市”だけではなく”村”や”群”で終わるものもあります。
　⇒数式が長くなりますのでお勧めではありませんが如何でしょうか。
=CHOOSE(IF(COUNTIF(C1,"*市*"),1,IF(COUNTIF(C1,"*郡*"),2,IF(COUNTIF(C1,"*村*"),3,4))),VLOOKUP(MID(C1,1,FIND("@",SUBSTITUTE(C1,"市","@",LEN(C1)-LEN(SUBSTITUTE(C1,"市",""))))),$A$1:$B$100,2,FALSE),VLOOKUP(MID(C1,1,FIND("@",SUBSTITUTE(C1,"郡","@",LEN(C1)-LEN(SUBSTITUTE(C1,"郡",""))))),$A$1:$B$100,2,FALSE),VLOOKUP(MID(C1,1,FIND("@",SUBSTITUTE(C1,"村","@",LEN(C1)-LEN(SUBSTITUTE(C1,"村",""))))),$A$1:$B$100,2,FALSE),"")

回答No.4

imogasi

お礼補足を読むと、実際のデータも全国住所的なものらしいね。
たとえで例を挙げていると市などで区切るという考えは普通不可能だよね。
C,列の文字列の何処までがAに当たるかは、一般には割り出すのが難しい。ですからVLOOKUPはそのままでは使えない。
ワイルドカード（＊）とといっ考えが、COUNTIF関数などで使える場合があるが、この場合は使えない。
C列のデータを対象に市や町や郡までに分離したいが、関数では式が長くなることや、郡山市や市川市や武蔵村山市などあって完全にはやりにくい
市だけに限って考える（関数式が長いが耐えられる字数）とか、人間が見て総合的判断でC列データで、境界（市区町村など）にスペースなど入れて、それを頼りに別列にC列からデータを作り、VLOOKUPを使うとか、の方法を考え付く。面倒くさいと思うが、実際はここに色々質問なんかやっている時間（数日）の１０分の１で、１０００件ぐらいの区切りはすんでしまうよ。
ーー
VBAでもすっきりした方法（比較をA列行数分回数、C列の全行数で繰り返す泥臭い方法以外は）が考えつかない。
C列を市、町、村、群などまでに分離するのも泥臭いし、市川市のような間違いは避けるのは自然には出来ない。
ーー
私がテストした方法を参考に。
E1:F9
cccc 1
dddd 2
aaa 3
hh 4
wk 5
g 6
eee 7
kak 8
past 9
E、F列は質問のA列、B列に当たる。
A列に下記データがあって、下記プログラムでB列をF列から引っ張ってきて出したもの。
A列　　　　B列
aaabb 3
ccccx 1
ddddxt 2
eeefff 7
gyut 6
hhkkk 4
wk123 5
aaaaab
kakgggg 8
pasthhh 9
aaaxxx
ーー
Sub test01()
For i = 1 To 9 'E列行数分くり返しの意味
Set x = Range("A1:A12").Find(what:=Range("E" & i), lookat:=xlPart) 'xlPartが部分一致を指定
If x Is Nothing Then
Else
Cells(x.Row, "B") = Cells(i, "F")
End If
Next i
End Sub
ーー
しかし上記B列で空白の行があるのは
第1行aaabbと
aaaaab、aaaxxxにはaaaという共通部分があって、上記のコードは完全ではなく、改良は簡単ではない。
広島市、北広島市などがおかしくなるかもしれない。表の先に北広島市を持ってくると旨く行くかもしれない、なんて問題もある（上記方法では、それも改良にならない）。
ただし質問者の例では案外こういうケースは無くて９９％は旨く索引できるかもしれないと言う気はする。
本番では数箇所（データ行数など）変える必要があるが、あくまで検討報告までに。

回答No.3

keithin

○寄せられる回答は，あなたが質問に書いた「こういう場合」に即して作成されています。
　まずあなたもご自分のエクセルで自分の書いたサンプル通りに作成し，そして寄せられた数式を試します。
　その後，サンプルデータのセルを本来のあるべき場所にずりずりとワークシート上でドラッグして動かしてみると，エクセルが自動的にセルの参照を修正してどんな式にすればよいのか教えてくれますので，参考にしてください。
　あるいは記入した数式を再度数式バーの中で１クリックして編集を開始すると，式が見ているセル範囲が青や赤の色枠で囲われて表示されます。その色の枠をワークシート上で本来のデータの位置やもっと広いセル範囲に直してみると，やはり数式が自動修正されるので参考にしてみて下さい。

○VLOOKUPで出来ないことも判ったし，どうやら難しそうなので諦めますという事なら，それもありです。
　誰の答えを待っていても，だいたい似たり寄ったりの面倒な式でしかできません。

○出来なくても諦めたくないなら，「教われば出来ると思っていたけど出来なかったので，ホントはこういうセル範囲にこんなデータで記入してあるのでもう一度教えてください。自分では教わった式をこういう具合に直してみたんだけど，こんな問題が起こりました」と質問を出し直してください。本来なら最初から事実を丁寧にご相談に書いておけば，お互いにこういった二度手間三度手間も避けられたわけですから，次回ご相談投稿時は気を付けてください。

○VLOOKUP関数を使い，限定的な簡易法ですがこんなやり方もあります。
　いま，Ａ列に記入されている言葉の「最小文字数」は「沖縄県名護市」の６文字です。Ａ列の全部のデータを見て，左から６文字あれば仕分けができそうなら
　D1:
　=VLOOKUP(LEFT(C1,6)&"*",A:B,2,FALSE)
　と計算が出来ます。

　この場合言わずもがなですが，「左から６文字まででは同じになってしまって識別できない」Ａ列のデータについては，個別に対処していく事になります。
　例えばＡ列にある「左６文字では同じになってしまうデータ」のＢ列には特別に記号を記入しておき，そちらは改めて別の式なり手動なりで修正していく格好ですね。
　これも出来なかった時は，やはり前述のように「事実はこうです，自分はこうしてみました」と改めてご相談を投稿し直してみてください。

　なお，場合によっては「６文字のＡ列データ」は切り捨てて個別対応することにして７文字（以上）で検索するとか，特定の言葉で始まるＣ列データは別のVLOOKUPをするようIF関数で仕分けてみるなど，応用の余地は色々あります。でも１００％カンペキに関数で作り込みたいと，こだわるだけ時間の無駄な場合も多いですので，ほどほどに。

回答No.2

mu2011

一例です。
”市”が無い場合は、空白にしています。
=IF(COUNTIF(C1,"*市*"),VLOOKUP(MID(C1,1,FIND("@",SUBSTITUTE(C1,"市","@",LEN(C1)-LEN(SUBSTITUTE(C1,"市",""))))),A:B,2,FALSE),"")

補足

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

例が悪かったのですが、
A列には”市”だけではなく”村”や”群”で
終わるものもあります。

回答No.1

keithin

VLOOKUP関数では出来ないので，たとえば次のような計算になります。
D1:
=INDEX(B:B,SUMPRODUCT(ROW($A$1:$A$3)*($A$1:$A$3=LEFT(C1,LEN($A$1:$A$3)))))

補足

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

試してみたのですが、
どうもうまくいきませんでした。

VLOOKUPではできないんですね。
参考になりました。