EXCEL 部分一致でLookup

質問

お世話になります。

エクセルで悩んでいます。
Sheet2（値段表マスターデータ）を参考に、
Sheet1へ機種の値段を入力していきたいと思っています。

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

Sheet1　（☆に値段を入れたい）
　　機種名　値段
1　A-5　　　　☆
2　A-23SS　　　☆
3　A-101　　　☆
4　A-56ABC　　☆
5　B-1234　　　☆
・
・
・

Sheet2　（値段表のマスターデータ）
　　機種名　値段
1　A-5　1000
2　A-23　2000
3　A-56　3000
4　A-101　4000
5　B-1234　5000
・
・
・
（以下数千行）

【望む結果】
Sheet1
　　機種名　値段
1　A-5　　　　1000
2　A-23SS　　　2000
3　A-101　　　4000
4　A-56ABC　　3000
5　B-1234　　　5000
・
・
・

VLOOKUP関数で処理できるかと思ったのですが、
派生機種などがあり、後ろに違うアルファベットがついたりするので不可能でした。
　（上記Sheet1の「A-23SS」「A-56ABC」のようなもの）

Sheet2を参考に、「A-5」「A-56」などをちゃんと区別して
結果を出すことは可能でしょうか。

よろしくお願いします。

回答No.2

Wendy02 · ベストアンサー

こんにちは。

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)

条件
-------------------------------------------
Sheet2の値段表のマスターデータは、最初にソートされていること。
「A-5」等の機種名の派生記号には、最初に文字が付けられていること。
マスターデータの数字の桁は、現行では、9桁まで、
増やす場合は、($A$1:$A$10)の$A$10の10の部分を増やします。
-------------------------------------------
この条件でない場合は、別の方法が必要です。

お礼

できました！ありがとうございます！
条件に当てはまったので、気持ちよく結果を出すことができました。

とにかく年内に表を仕上げないといけないのでとても助かりました。
「なぜかできた」という豆鉄砲を食らったような状態なので、
後ほどしっかり式の理解をしたいと思います。

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

回答No.4

MackyNo1

Sheet2のリストの機種名が昇順に並べ替えられているなら、以下の配列数式で該当データを表示することができます。

Sheet2のA1セル以下に機種名が入力されていて、Sheet1のA1セルに機種名が入力されているならB1セルに以下の式を入力してCtrl+Shift+Enterで確定して下方向にオートフィルしてください。

=IF(COUNT(FIND(Sheet2!$A$1:$A$5,A1)),INDEX(Sheet2!$B$1:$B$5,MAX(ISNUMBER(FIND(Sheet2!$A$1:$A$5,A1))*ROW($A$1:$A$5))),"")

お礼

ご回答ありがとうございます！
こちらも配列数式ですね。勉強になります。
この式についても後ほどしっかり学ばせていただきたいと思います。

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

回答No.3

tom04

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

↓の画像のようのSheet1に作業用の列を使わせてもらっています。
作業列はA列の数字以降の文字を消すようにしてみました。
配列数式になってしまいますので、コンピュータの負担のことを考えると
あまり良い方法とは言えないかもしれません。

この画面からコピー＆ペーストしただけではエラーになると思いますので、
C2セルに貼り付け後、F2キーを押すか、数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrl＋Enterキーで確定してください。
数式の前後に｛　｝マークが入り配列数式になります。

Sheet1の作業列C2セルに
=IF(A2="","",LEFT(A2,2)&MID(A2,MATCH(TRUE,ISNUMBER(MID(A2,ROW($1:$20),1)*1),0),COUNT(MID(A2,ROW($1:$20),1)*1)))

として、Shift＋Ctrl＋Enterキーで確定です。
（とりあえずA列は20文字まで対応できるようにしています）

そして、B2セルに
=IF(C2="","",INDEX(Sheet2!$B$2:$B$5000,MATCH(C2,Sheet2!$A$2:$A$5000,0)))

これは配列数式ではありません。

最後にB2・C2セルは範囲指定し、C2セルのフィルハンドルでダブルクリックするか
下へオートフィルでコピーすると画像のような感じになります。

尚、数式は5000行まで対応できるようにしていますが、
データ量によってアレンジしてみてください。

以上、長々と書きましたが、
参考になれば幸いです。m(__)m

お礼

わざわざ画像までご用意いただきありがとうございます！
今回はWendy02様の方法で解決いたしましたが、
配列数式は使ったことがなかったので勉強になります。

いろいろな方法があるのですね。自分の未熟さを痛感します。
作業が終わったら配列数式についてもしっかり学びたいと思います。

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

回答No.1

ASIMOV

この例では、派生機種の機種名は、「数字の後にアルファベットが付く」となっていますが、すべての機種名にこのルールが当てはまるのでしょうか？

Sheet1の機種名から「元の機種名」を抽出する論理的なルールがないと、この課題は難しいと思います

お礼

すべてではないのですが、ほぼ当てはまります。
今後質問させていただく際にはもっとしっかり情報を記載しようと思います。
ご回答ありがとうございました。