エクセルで指定列を文字列検索、そして値を代入

はじめまして。
エクセルが得意な方よろしくお願いします。
文字列を検索して、違うセルにその文字列から置き換えをしたデータを表示させたいです。

例えば

A列に品名が入っています。（りんご、みかん、なし・・・・）
B列にその品名の価格が入っています。（100、200、300・・・・）

C列に品名が型番とともに複数書いてあります。
（りんご-01、みかん-01、りんご-02、なし-01、、、)

C列の文字列を検索して、A列に記載した文字列と適合した場合、D列にB列の価格を表示させる。

このような検索置き換え？はSUBSTUTEで出来そうでしたが、上手くいきません。

説明がなかなか上手く伝えられず申し訳ないです。
おわかりの方、よろしくお願いいたします。

No.1 回答者： BLUEPIXY 回答日時： 2005/03/29 22:57

VLOOKUPを使って

検索する値(-NNを切り取って)
LEFT(C1,LEN(C1)-3)
検索する範囲
An:Bm
取り出す列
2
検査の型
FALSE
でやればいいと思います
例：
D1
=VLOOKUP(LEFT(C1,LEN(C1)-3),A1:B20,2,FALSE)
以下コピー

No.2 回答者： matsu_jun 回答日時： 2005/03/29 23:11

セルD1に

=OFFSET($A$1,MATCH(LEFT(C1,LEN(C1)-3),A:A,0)-1,1,1,1)*VALUE(RIGHT(C1,2))
を代入し、下へドラッグしてください。Cに何も入っていないと「#VALUE」が表示されますが、
これを嫌うのであれば、ISERR関数を使ってさらに条件分岐をしてください。ちなみにこんなに長くなりました。
=IF(ISERR(OFFSET($A$1,MATCH(LEFT(C1,LEN(C1)-3),A:A,0)-1,1,1,1)*VALUE(RIGHT(C1,2))),"",OFFSET($A$1,MATCH(LEFT(C1,LEN(C1)-3),A:A,0)-1,1,1,1)*VALUE(RIGHT(C1,2)))


ちょっと長いですか? よろしければ前半の式について以下数式の説明をしますね。

一応、LEFT(C1,LEN(C1)-3) で、セルC1の「りんご-01」という文字列から「りんご」を取り出しています。
また、VALUE(RIGHT(C1,2)) で、セルC1の「りんご-01」という文字列から数字の「1」を取り出しています。
したがって上の式は

=OFFSET($A$1,MATCH("りんご",A:A,0)-1,1,1,1) * 1

と言い換えられます。最後の「*1」はりんごの数量、前半がりんごの単価を求めています。
MATCH("りんご",A:A,0)
が、A列で「りんご」が何行目にあるかを探す式ですから、例ではセルA1に「りんご」があるので =1 となります。
で、OFFSET($A$1,1-1,1,1,1)と言い換えられます。これは、セルA1から下へ1-0、右へ1ずらした、高さ1、幅1のセルの値ということですので、つまりセルB1の値を指すことになります。

No.3 回答者： at121 回答日時： 2005/03/30 11:31

同じシートに　基準となる　価格表　では　あとあと　扱いにくいかも。

Sheets("価格表")　　シート名は基準なので固定
場所は自由　品名の　右に　価格
りんご100
みかん200

データのあるシート("データ")　シート名は任意
B列2　　　　　　C列　ユーザー定義「価格表から価格抽出」設定
りんご-01=価格表から価格抽出(B2)
みかん-05=価格表から価格抽出(B3)

同じシートに　価格表を置いて　参照するなら
Sheets("価格表").Cells.Find(検索品名)
　を　　Range("a:a").Find(検索品名)　　・・に変更か
　　　Range("価格表").Find(検索品名)　　・・に変更
　して　価格表の場所の名前を("価格表")にするなど・・

↓マクロの　標準モジュールに追加↓

Function 価格表から価格抽出(セル)

'セパレータ '半角/全角　ハイフン　"－"or "-"
検索品名 = Trim(Split(セル.Value, "－", 2, 1)(0))
If Not Sheets("価格表").Cells.Find(検索品名) Is Nothing Then
価格 = Sheets("価格表").Cells.Find(検索品名).Offset(0, 1).Value '品名の右の価格を取得
Else
価格 = "m(_ _)m" '見つかりません。
End If
価格表から価格抽出 = 価格

End Function