EXCELで表引き（入力文字と参照テーブル）

住所録からゼンリン地図の索引ページを自動的に取ってくるシートを作成しています。

下記のような参照テーブル（町名と索引ページ）表があります。
　　　　A　　　　　　　　　B　　　　Ｃ
1　　町名　　　　　　　番地　索引ページ
2　吉成　　　　　　　　　１　　　 105　　　　　　　（1番地から99番地）
3　吉成　　　　　　　　100　　　 108　　　　　　　（100番地から199番地）
4　吉成1丁目　　　　　　　　　　109
5　吉成南町1丁目　　　　　　　201

以下の入力セルに町名と番地が一致した検索ページを表示させたい。
　　　　　Ａ　　　　　　　　　　　　　　　　　Ｂ
10　　住所　　　　　　　　　　　　　　地図ページ
11　鳥取市吉成南町1丁目100　　　　201
12　鳥取県鳥取市吉成95番地　　　　105
13　鳥取市吉成106-1-5　　　　　　　　108
14　鳥取市吉成1丁目100番地　　　　109

参照表から入力住所と一致する地図ページを表引きするにはどのような関数を使用すればよろしいでしょうか？ＶＬＯＯＵＰ関数を使用してトライしていますが、文字の抜き出し方が良く分かりません。
アドバイスをお願いします。

No.3 ベストアンサー 回答者： tom04 回答日時： 2014/11/25 20:22

こんばんは！

関数ではかなり厄介だと思います。
そこでVBAになりますが、一案です。

↓の画像で左側のC列にページを表示したい「Sheet1」とし、右側が「参照」のSheet2とします。
ただ単にSheet2のデータを羅列してしまうと、何度も重複する「住所」が出現しますので、
Sheet2のD2セルに
=LEN(A2)
という数式を入れフィルハンドルでダブルクリック　→　これで文字数が表示されますので、
D列の「降順」で並び替えをしておきます。
これは最大文字数が含まれているデータから検索し、無駄なループを避けるためです。

この下準備ができた上で
Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, cnt As Long, c As Range
Dim str As String, buf As String, myCnt As String, wS As Worksheet
Set wS = Worksheets("Sheet2")
Application.ScreenUpdating = False
With Worksheets("Sheet1")
.Range("A:A").Insert
For i = 2 To .Cells(Rows.Count, "B").End(xlUp).Row
For k = 2 To wS.Cells(Rows.Count, "A").End(xlUp).Row
str = wS.Cells(k, "A")
If InStr(.Cells(i, "B"), str) > 0 Then
If wS.Cells(k, "B") = "" Then
.Cells(i, "C") = wS.Cells(k, "C")
Exit For
Else
.Cells(i, "A") = Mid(.Cells(i, "B"), InStr(.Cells(i, "B"), str) + Len(str), 10)
For cnt = 1 To Len(.Cells(i, "A"))
buf = Mid(.Cells(i, "A"), cnt, 1)
If buf Like "[0-9]" Then
myCnt = myCnt & buf
Else
Exit For
End If
Next cnt
If myCnt > 0 Then
If myCnt < 100 Then
.Cells(i, "A") = 1
Else
.Cells(i, "A") = WorksheetFunction.RoundDown(myCnt, -2)
End If
Set c = wS.Range("B:B").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
.Cells(i, "C") = c.Offset(, 1)
End If
myCnt = ""
Exit For
End If
End If
Next k
Next i
.Range("A:A").Delete
End With
Application.ScreenUpdating = True
End Sub 'この行まで

※　サンプル数が少ないのでお望み通りになっているかどうかは判りません。
※　じっくり考えればもっと簡単に出来るかもしれませんが、
まずはこの程度で・・・m(_ _)m

この回答へのお礼

的確なモジュール作成、ありがとうございます。

このモジュールを参考にアレンジしたいと考えます。
やはり複雑なことをやるにはVBAですね。
また、分からないことが出た場合には質問させていただきます。

よろしくお願いします。

お礼日時：2014/11/26 09:04

No.2 回答者： aokii 回答日時： 2014/11/25 16:52

参照データの番地はｘｘｘ番地までは索引〇〇〇ページとなっており、単純に完全一致では駄目ですので、番地データがある場合には、VLOOKUP関数の最後に,falseを記入しないようにしてはいかがでしょう。

この回答への補足

回答ありがとうございます。

falseを記入しないということは、数字扱い判定にすると言う事でしょうか？
ただ、鳥取市吉成106-1-5の場合、"-"を””に変換すると10615番地になってしまい、106番地の条件でなくなってしまいます。
やはりVBAでそれなりの関数モジュールを作成して高度なことをやらないと駄目でしょうか？

補足日時：2014/11/25 17:29

この回答へのお礼

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

VBAでの回答を別の方からいただきましたので、そちらでトライします。
お付き合いいただき、感謝しています。

お礼日時：2014/11/26 09:06

No.1 回答者： aokii 回答日時： 2014/11/25 15:33

VLOOKUP関数を使用するのがいいと思います。

検索ページの住所に鳥取県とかを追加して、完全な住所にしてください。
次に、参照テーブルのデータを結合（=A1&B1）してから、鳥取県とか鳥取市を追加して、検索ページ内の住所と完全に一致させる必要があると思います。
検索ページでの文字の抜き出しはせずに、丁目を"-"に、番地を””に一括で置き換えてみてください。

この回答への補足

早々の回答、ありがとうございます。

入力データを整えるのは問題ありませんが、参照データの番地はｘｘｘ番地までは索引〇〇〇ページとなっており、単純に完全一致では駄目です。番地データがある場合には、入力から番地数を抜き出して該当番地範囲以内の判断が必要かとも思われます。
また、「吉成」だけと「吉成〇丁目」「吉成〇町〇丁目」を区別するには、入力文字の抜き出しが必要ではないでしょうか？
他の質問でindex関数等を使用した例も見ましたが、ジャストフィットしません。
よろしくお願いします。

補足日時：2014/11/25 16:10