VLOOKUPによる他シートの値参照

質問

初心者で以下の処理を行うコーディングの方法が分りません。
いろいろと他の質問、サイトを調べましたが、解決できませんでしたので、ご指導頂ければ有り難いです。

(1)"Sheet1"に下記例の通り、２列にデータが並んでいる。
　左列には商品、右列にはその単価がセットされている。

（例）

商品1　　\11
商品2　　\22
商品3　　\33
：

(2)別シート"Sheet2"の左列には、商品が"sheet1"とは
異なる順序で並んでいる。また同一商品が複数存在することもある。
この商品の右列にそれぞれの単価をセットしたい。

商品3　　（Sheet1を参照して値をセット）
商品7　　（Sheet1を参照して値をセット）
商品1　　（Sheet1を参照して値をセット）
：

、って感じです。EXCEL関数の"VLOOKUP"を使おうと思っているのですが、どうも思った通りの結果がでません。
商品のデータ数は当然変動するのでVLOOKUPの参照セルに変数を使おうと思っているのですが、
その方法がまずいのでしょうか？

回答No.3

papayuka · ベストアンサー

#1です。

VBAで？
Sheet2 にVLOOKUP式を入れたいなら可変させるのは入れたい範囲だけで良いと思います。

Sub Test()
Dim r As Range
　With Worksheets("Sheet2")
　　.Columns(2).ClearContents
　　Set r = Range("A65536").End(xlUp).Offset(0, 1)
　　.Range("B1", r) = "=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$2:$B$4,2,0))" & _
　　　　　　　　　　　　　　",""""," & "VLOOKUP(A1,Sheet1!$A$2:$B$4,2,0))"
　End With
End Sub

お礼

ご回答ありがとうございました。
内容を参考にさせていただき、無事解決できました。
本当に助かりました！

回答No.4

Wendy02

#2のWendy02 です。

うーん、VBAとはね!ちょっとびっくりですね。

＞VLOOKUP(Cells(i, 1),Sheet2!Range(Cells(1, 1),Cells(j,2)),2,FALSE)"

＞　　「"Sheet2"側の変数として"i"を、"Sheet1"側の
＞　　　変数として"j"を、それぞれ利用して、下記のように、
＞　　　VLOOKUPの中にi、jを埋め込もうとしたのですが..
＞　　　こんなのってダメなんでしょうか？」

確かに、埋め込みは出来ますが、VBAだと、全体の雰囲気が違ってくるはずです。
細かな解説はしませんが、Vlookupなど、エラーを吐く関数を使うときは、それなりに技術が必要ですね。以下のサンプルで、VlookupのVBAの使い方（わたし流）をみてください。

Sub TestSample()
Dim Sh1 As Worksheet, Sh2 As Worksheet
Dim i As Long, j As Long, Ret As Variant
Set Sh1 = Worksheets("Sheet1")
Set Sh2 = Worksheets("Sheet2")
'
i = 2 'シート2の検索値の行
j = 10 'シート1 のデータの行
'
If IsEmpty(Sh2.Cells(i, 1)) Then
　 MsgBox "検索値がありません。", vbInformation
　 Exit Sub
End If
With Sh1
Ret = Application.VLookup(Sh2.Cells(i, 1).Value, .Range(.Cells(1, 1), .Cells(j, 2)), 2, False)
If Not IsError(Ret) Then
　MsgBox Ret
　'Sh2.Range("A1").Value = Ret　'戻り値代入
Else
　MsgBox "検索値 = " & Sh2.Cells(i, 1).Value & " に対して" & Chr(13) & _
　　　　"値が見つかりません。", vbCritical
　'Sh2.Range("A1").Value = CVErr(xlErrNA)　'戻り値代入
End If
End With
Set Sh1 = Nothing : Set Sh2 = Nothing
End Sub

お礼

ご丁寧な回答ありがとうございました。
おかげさまで問題は解決できました！

回答No.2

Wendy02

こんばんは。
#1 のpapayukaさんにかぶってしまいますが、

＞商品のデータ数は当然変動するのでVLOOKUPの参照セルに変数を使おうと思っているのですが、その方法がまずいのでしょうか？

私は、Excelの関数は不得意ですが、でも、文章を読んでいて、おかしなところは見当たらないので、本来は、「式」を見せていただくのが一番ですね。

（Sheet1を参照して値をセット）
ということだから、

Sheet2の
A1 ～データがあるとして、

B1 に、=VLOOKUP(A1,Sheet1!$A$1:$B$10,2,0)

「Sheet1!$A$1:$B$10」がデータ範囲として、このように入れれば、問題はないように思います。

エラー処理をするなら、

B1 に、 =IF(COUNTIF(Sheet1!$A$1:$B$10,A1)=0,"",VLOOKUP(A1,Sheet1!$A$1:$B$10,2,0))
というところかしらね。

お礼

ご丁寧な回答ありがとうございます。
参考にさせて頂きます。

"Sheet2"側の変数として"i"を、"Sheet1"側の
変数として"j"を、それぞれ利用して、下記のように、
VLOOKUPの中にi、jを埋め込もうとしたのですが..
こんなのってダメなんでしょうか？

VLOOKUP(Cells(i, 1),Sheet2!Range(Cells(1, 1),Cells(j,2)),2,FALSE)"

回答No.1

papayuka

Sheet1がマスターですね？
Sheet1（マスター）のリスト範囲がA2:B4として、
Sheet2のB2に

=IF(ISERROR(VLOOKUP(A2,Sheet1!$A$2:$B$4,2,0)),"",VLOOKUP(A2,Sheet1!$A$2:$B$4,2,0))

のような感じですけど。
マスターが増えるなら範囲に名前（ここでは「商品リスト」）を付けておいて、

=IF(ISERROR(VLOOKUP(A2,商品リスト,2,0)),"",VLOOKUP(A2,商品リスト,2,0))

お礼

早速のご回答ありがとうございます。
大変参考になりました。

"Sheet2"の商品数は随時変動するので、
"VLOOKUP"を埋め込む範囲をDo Untilと変数を
使って指定しようと考えておりました。
よって、VLOOKUP(●●,Sheet1!...）の"●●"の
部分は変数でセルを指定しようとしていますが、
それがうまくできていないのかもしれません。

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