VBAでのXLOOKUP関数の記載方法について

Question

前提・実現したいこと

エクセル・VBAの超初心者です。
VBAにXLOOKUP関数を使用して、別シートへデータを参照したいと思っています。
作成しているエクセルデータでは、元データシート「Sheet2」に

No 商品名 　産地
116 ケーキ 　ロシア
117 クッキー　フランス
118 アイス 　インドネシア
…
というデータがあり、
出力データシート「出力シート」のセル「A2」にNoを入力し、セル「B2」に商品名、セル「C2」に産地のデータを参照しようとしています。

発生している問題・エラーメッセージ

実行時エラー 9 インデックスが有効範囲にありません。

該当のソースコード

コードは以下の通りです。

Sub TEST2()

'XLookup関数で、別シートを参照する
Worksheets("出力シート").Range("B2").Value = WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:C"), Worksheets("Sheet2").Range("A:C"), """", 0, 1)

End Sub

試したこと

XLOOKUPでのVBA利用について自分なりに類似例を調べて記載してみたのですが、なかなかエラーから抜け出せず、非常に困っております。
（なお引数(見つからない場合)について””だけ入力すると、
実行時エラー 1004 worksheetfunctionクラスのXlookupプロパティを取得できません
と表示されました）

補足情報（FW/ツールのバージョンなど）

元々VLOOKUPでデータ参照しようと考えていましたが、実際に運用を検討しているデータ形式では一行に20近いセルデータがあるため、XLOOKUPを利用したいと考えています。

最近エクセル・VBAの勉強を始めた超初心者で、なかなかXLOOKUPのVBA使用実例を探し当てることもできず、苦戦しております。（おそらく単純な構文ミスによるエラーなのかと考えておりますが…）
VBAを使えるまで根気強くトライしていきたいと思っており、ご教示時いただけますと幸いです。
どうぞよろしくお願いいたします。

前提・実現したいこと

エクセル・VBAの超初心者です。
VBAにXLOOKUP関数を使用して、別シートへデータを参照したいと思っています。
作成しているエクセルデータでは、元データシート「Sheet2」に

No 商品名 　産地
116 ケーキ 　ロシア
117 クッキー　フランス
118 アイス 　インドネシア
…
というデータがあり、
出力データシート「出力シート」のセル「A2」にNoを入力し、セル「B2」に商品名、セル「C2」に産地のデータを参照しようとしています。

発生している問題・エラーメッセージ

実行時エラー 9 インデックスが有効範囲にありません。

該当のソースコード

コードは以下の通りです。

Sub TEST2()

'XLookup関数で、別シートを参照する
Worksheets("出力シート").Range("B2").Value = WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:C"), Worksheets("Sheet2").Range("A:C"), """", 0, 1)

End Sub

試したこと

XLOOKUPでのVBA利用について自分なりに類似例を調べて記載してみたのですが、なかなかエラーから抜け出せず、非常に困っております。
（なお引数(見つからない場合)について””だけ入力すると、
実行時エラー 1004 worksheetfunctionクラスのXlookupプロパティを取得できません
と表示されました）

補足情報（FW/ツールのバージョンなど）

元々VLOOKUPでデータ参照しようと考えていましたが、実際に運用を検討しているデータ形式では一行に20近いセルデータがあるため、XLOOKUPを利用したいと考えています。

最近エクセル・VBAの勉強を始めた超初心者で、なかなかXLOOKUPのVBA使用実例を探し当てることもできず、苦戦しております。（おそらく単純な構文ミスによるエラーなのかと考えておりますが…）
VBAを使えるまで根気強くトライしていきたいと思っており、ご教示時いただけますと幸いです。
どうぞよろしくお願いいたします。

Answer 1

引数を見ると、
「検索範囲」「戻り範囲」ともに、「Worksheets("Sheet2").Range("A:C")」となっていますね。ここが原因かと。
検索範囲はA列で、戻り範囲はB列かと。
また、見つからない場合の処理ですが、私の環境ではダブルクォートではなく「vbNullString」を指定すると、空欄になりました。

Comments

[yukiyuki1203]

ご回答いただきありがとうございます。
「検索範囲」「戻り範囲」をA列・B列に指定すると動きました。
(私の環境では””でも「vbNullString」でも処理はできました)
ただスピルにしたくて「戻り範囲」をB：Cに指定すると動かない(エラーは出ず処理されるけれど何も参照されない)なってしまうのですが、他にもなにか不足があるのでしょうか。

コードは以下です。
Worksheets("出力シート").Range("B2").Value = WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:A"), Worksheets("Sheet2").Range("B:C"), vbNullString, 0, 1)

何度も恐縮ですが、どうぞよろしくお願いいたします。

[hex309]

私もちょっと調べながらなので、確証がない部分もありますが、以下、ご参考まで。

まず、

Worksheets("出力シート").Range("B2").Value
ですが、
Worksheets("出力シート").Range("B2:C2").Value
ですね。
関数をワークシートに入力するのとは異なって、自動的に隣のセルに入力とは行かないようです。

また、
WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:A"), Worksheets("Sheet2").Range("B:C"), vbNullString, 0, 1)
を、TypeName関数で調べると「Range」が返されました。
そこで、

WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:A"), Worksheets("Sheet2").Range("B:C"), vbNullString, 0, 1).Value

と、最後に「.Value」を加えたところ、セルB2とC2に値が入力されました。
以上となります。

[yukiyuki1203]

ご指摘いただいた箇所を修正するとスピルで返されました。
ご教示いただきありがとうございます！

もう１点だけ、何度も恐縮ではございますがTypeName 関数での調べ方を教えていただけますと幸いです。今後エラー時に少しでも自分で調べられればと思います。
今回WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet2").Range("A:A"), Worksheets("Sheet2").Range("B:C"), vbNullString, 0, 1)に「.Value」指定が不足していた内容を確認するにあたり、関数の使い方としては単純に
Dim MyType
MyType = TypeName(WorksheetFunction.XLookup(Worksheets("出力シート").Range("A2").Value, Worksheets("Sheet1").Range("A:A"), Worksheets("Sheet2").Range("B:E"), "", 0, 1))
Debug.Print TypeName(MyType)
で良いのでしょうか？(結果実行時エラー9でインデックスが有効範囲にないと出てしまいました。。。)

何度も質問を返してしまい大変恐縮ではございますが、どうぞよろしくお願いいたします。

[hex309]

まず、エラーになってしまったコードですが、対象のシートはきちんと存在した状態でしたか？
エラーメッセージから、参照しているシートが無いように思われます。

また、使い方ですが、
提示されたコードですと、TypeName関数の結果を変数MyTypeに代入していますよね。
内容を確認したいのであれば、単純に以下でOKです。

Debug.Print MyType

次のようにしてしまうと、変数MyTypeの種類を調べることになります。
Debug.Print TypeName(MyType)

以下、ご参考まで。
Debug.Print TypeName(MyType)

[yukiyuki1203]

ご回答ありがとうございます。
エラーについては、同じシート名"Sheet1"を含む別ブックを同時に開いていたため発生したようでした(閉じると反映されました)。
変数としての扱いなど、以後注意して学んでいきたいと思います。

何度もご丁寧にご回答・ご説明いただき本当にありがとうございました！