EXCEL 部分一致でLookup 条件が増えてしまいました

質問

お世話になります。
先日、こちらの質問をさせていただいた者です。

EXCEL　部分一致でLookup
http://oshiete1.goo.ne.jp/qa5552745.html

同じようなエクセルの処理なのですが、
さらに条件が増えたものが登場し困っております。

以下のような形式になっています。

Sheet1　（☆に値段を入れたい）
　　機種名　値段
1　A5　　　　　☆
2　AA23SS　　　☆
3　ABB101　　　☆
4　A56DAB　　　☆
5　BA1234　　　☆
・
・
・
（以下数千行）

Sheet2　（値段表のマスターデータ、ソート済）
　　機種名　値段
1　A5　　　1000
2　A56D　　2000
3　AA23　　3000
4　ABB101　4000
5　B1234　5000
・
・
・

【望む結果】
Sheet1
　　機種名　値段
1　A5　　　　1000
2　AA23SS　　3000
3　ABB101　　4000
4　A56DAB　　2000
5　BA1234　　5000
・
・
・

数字の後にアルファベットが付くものは派生機種です。
（上記Sheet1の「AA23『SS』」「A56D『AB』」のようなもの）

数字の前が「アルファベット1文字＋ハイフン」だった場合は、先日ご回答いただいた

B2～
=VLOOKUP(MID(A2,1,MATCH(TRUE,INDEX(ISERROR(MID(A2,ROW($A$1:$A$10)+2,1)*1),,),0)+1),Sheet2!A:B,2,0)

で解決したのですが、今回は

・数字の前のアルファベットが１～３文字（ハイフンは無し）
・派生機種でなくとも、後ろにアルファベットがついている場合がある

と、さらにややこしいものになってしまいました。

なお、派生記号に関してはアルファベットのみとなっています。

当方EXCEL2003を使用しておりますので、関数のネスト制限の問題もあり途方に暮れています。

どなたか知恵をお貸しいただけませんでしょうか。
よろしくお願いいたします。

回答No.2

end-u · ベストアンサー

参考程度に。

=INDEX(Sheet2!B:B,LOOKUP(10000,MATCH(MID(A2,1,ROW($A$1:$A$6)),Sheet2!A:A,0)))
こんな感じの関数で参照する事は可能です。
※『10000』...はマスタデータの最大行数より多く設定する必要があります。
※『ROW($A$1:$A$6)』...の範囲行数（6）はマスタデータの最大文字数より多く設定する必要があります。

...ですが、マスタデータが数千件で、参照データも数千件あるとなると、重くて使い物にならないと思われます。

Sheet1に入力する派生機種とは、いわゆる付加データでしょうから、マスタのkeyである文字列とは別の列に入力するなど、
データの持たせ方を見直したほうが良いです。
数千件ものデータを関数で部分一致させるのはちょっと無理があります。

お礼

ありがとうございます！
望んでいた結果が出ました！

データは他者が作ったものなので、どうにもなりませんでした。
確かに重くはなりましたが、再計算を手動にするとなんとか使えました。

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

回答No.3

tom04

こんばんは！
お役に立てるかどうか判りませんが・・・

考え方として、Sheet1・2共に数字のみを拾い出し、それを参照して値段を表示させてみるようにしてみました。

尚、この場合、機種名の列にA5　B5というように数値のみを表示させた場合同じ物があると使えないと思いますので、
とりあえず、数値のみの重複はないものとしています。

↓の画像のようにSheet1・2共に作業用の列を使わせてもらっています。

Sheet1開いたまま、Ctrlキーを押しながらSheet2のSheetタブをクリックします。
これでSheetのグループ化ができましたので、Sheet1の作業列C2セルに
数式を入力するとSheet2も同じ数式が入ります。

配列数式になってしまいますので
Shift＋Ctrl＋Enterキーで確定してください。
C2セルに
=IF(A2="","",MID(A2,MATCH(TRUE,ISNUMBER(MID(A2,ROW($A$1:$A$20),1)*1),0),COUNT(MID(A2,ROW($A$1:$A$20),1)*1)))
としてShift＋Ctrl＋Enterキーで確定です。
（A列のセル内が20文字まで対応できるようにしています）
これをオートフィルで下へずぃ～～～！っとコピーします。
C列は数値のみの表示になりますので、この数値を参照します。

B2セルに
=IF(C2="","",INDEX(Sheet2!$B$2:$B$1000,MATCH(C2,Sheet2!$C$2:$C$1000,0)))
（配列数式ではありません！）
としてフィルハンドルでダブルクリック、またはオートフィルで下へコピーしてみてください。

以上、長々と書きましたが
参考になれば幸いです。
しかし、最初に書いたように数値だけの参照では
希望通りにならなかったら読み流してくださいね。m(__)m

お礼

わざわざ画像までご用意いただきありがとうございます！

ですが、今回は数値の重複がありましたので使えませんでした。
私の説明不足だったようで申し訳ございません。

同様のケースがあった場合にはぜひ利用させていただきたいと思います。

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

回答No.1

avanzato

こんにちは。
ユーザー定義関数を使用したほうが簡単かと思います。
以下を標準モジュールに追記してください。
Option Explicit

Function NVLOOKUP(Str As String)
Dim I As Integer
For I = Len(Str) To 1 Step -1
NVLOOKUP = Application.VLookup(Left(Str, I), Range("Sheet2!A1:B5").Value, 2, False)
If IsError(NVLOOKUP) = False Then Exit For
Next I
End Function

Sheet1のB1に
=NVLOOKUP(A1)
にして、以降コピーで良いです。
これで結果が出ると思います。

動作の説明ですが
ユーザー定義関数NVLOOKUPの中で引数をVLOOKUPしています。
ただし、引数をLEFTを使い一文字づつ減らして検索します。
VLOOKUPの戻り値がエラーでなければそのときのVLOOKUPの戻り値が返されます。

お礼

ご回答ありがとうございます！
VBAはあまりわからないので勉強になりました。

試してみましたが、なぜか上手くいかない箇所がありました。
私のやり方がまずかったのかもしれませんが、今回はend-u様の方法を使わせていただきました。
VBAも学んでいきたいと思います。

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