[[20240801080104]]　『XLOOKUP　戻り範囲を可変させたい。』（ヘラちょんぺ）　ページの最後に飛ぶ

『XLOOKUP　戻り範囲を可変させたい。』（ヘラちょんぺ）

XLOOKUPで、戻り範囲を可変させようと考えていますが、#VALUEエラーとなります。

前提として　検索させるテーブルは以下の内容です。
Ａ列　　B列　　Ｃ列　Ｄ列　　Ｅ列
本体１　OPA-1　OPA-2　OPB-1　OPB-2　…
本体２　OPA-2　OPA-3　OPB-1　OPB-3　…
本体３　OPA-3　OPA-4　OPB-2　-　　　…

ここで範囲名を、A列を「本体」、Ｂ・Ｃ列を「OPA」　D･E列を「OPB」とします。

それを踏まえて、別シートで以下の表を作成したいと考えています。
A1セルに検索値となる本体名、B列に戻り値となる範囲名を入力して、
C列にはXLOOKUP関数で、上記引数から呼び出した値を表示したいと思います。
　　Ａ列　　Ｂ列　C列
１　本体Ａ　OPA　OPA-1,OPA-2　
２　　　　　OPB　OPB-1,OPB-2　
（実際にはC列の内容は入力規則のリスト候補にしたいと考えてます。）

それを踏まえて、上記の場合C1セルに、
=XLOOKUP（$A$1,本体,B1）　あるいは
=XLOOKUP（$A$1,本体,INDIRECT(B1)）としてみましたが、#VALUEエラーが返されます。
なお戻り値はセル参照でなく、直接範囲名とした場合はきちんと返されます。

恐らく戻り値の参照方法がまずいと思いますが、対処方法がわからないので、
お知恵をお借りしたく、お願いします。

ちなみにこの発想に至った経緯として…
とある機器の発注票を作りたいと考えており、本体毎に対応したオプションを入力規則からのリストで入力させたいと考えております。
上記では端折っていますが、実際には本体1台に最大30品目と多いのと、
オプション群ごとにシートを作るのも、後のメンテが大変なので、
上記のように範囲名で仕切って、リストを絞れないかと思った次第です。

＜使用 Excel：Microsoft365、使用 OS：Windows11 ＞

＞ #VALUEエラーが返されます。
「本体」と「OPA」と「OPB」の行数が一致しないのでは？
（d-q-t-p） 2024/08/01(木) 13:01:45

＞なお戻り値はセル参照でなく、直接範囲名とした場合はきちんと返されます。
XLOOKUPの戻り値は、「セル参照」でも「直接範囲名」でもなく配列です。

戻り値である配列をセルに代入する際に、「スピル」という機能が働いて、
配列を1つずつセルに代入されるだけです。

「セル参照でなく、直接範囲名」とは、
C1セルのお話をしているのでしょうか？

もし、C1セルの話をしているのなら、「#VALUEエラーが返されます」の時の
C1セルに入力した値(式)を教えて下さい。

＞=XLOOKUP（$A$1,本体,B1）　あるいは
＞=XLOOKUP（$A$1,本体,INDIRECT(B1)）としてみましたが、#VALUEエラーが返されます。
ちなみに、こちらでテストしたところ、どちらも
「#VALUE!」は出ずに参照した値が、C列、D列に表示されます。
（匿名） 2024/08/02(金) 17:13:27

回答ではないけど、
＞XLOOKUPの戻り値は、「セル参照」でも「直接範囲名」でもなく配列です。
XLOOKUPはセル参照を返します。
A1セルに1と入力して、別のセルに =CELL("address",XLOOKUP(1,A1,A1)) といれると結果は $A$1
（QED） 2024/08/03(土) 08:47:38

 既に指摘がありますが、ROWS(本体),ROWS(OPA),ROWS(OPB)をそれぞれ確認してください。一致していますか?

（xyz） 2024/08/03(土) 09:23:35

皆様回答ありがとうございます。
結論としては範囲名の参照範囲のセル番地を絶対参照にすることで解決しました。セル番地の列番号に$を入れてなかったせいか、当初設定していたところから全然別のセル番地に書き換わっていました。（なぜそうなるか理解できませんが、本題とは別なのでここでは割愛します。）
大変お騒がせしました。

匿名さん誤解を招き大変失礼しました。
正確には、「戻り範囲となる範囲名をセル参照ではなく、式内に直接書き込んだ場合」でした。
でもそうなると、逆になぜそれで成立したのか、それはそれで不思議ですが…

QEDさんなるほど、仕組みを理解しました。

（ヘラちょんぺ） 2024/08/04(日) 10:57:43

コメント返信：

[ 一覧(最新更新順) ]

[[20240801080104]] 『XLOOKUP 戻り範囲を可変させたい。』（ヘラちょんぺ） ページの最後に飛ぶ

[[20240801080104]]　『XLOOKUP　戻り範囲を可変させたい。』（ヘラちょんぺ）　ページの最後に飛ぶ