エクセルの数式で教えてください。

セルW17に=HLOOKUP("構造_主要",$A1:$PA2,2,0)」の数式があります。
A1列に該当する「構造_主要」があった場合にそのセルのすぐ下のセル値を表示します。
例えば
セルIH1に「構造_主要」がある場合はその下のセルIH2の文字「鉄骨造」がW17に表示されますが、
物件によってはIH2～IH12までの範囲に文字「鉄骨造」が表示されるので、IH2の文字「鉄骨造」が無い場合はW17には何も表示されない不具合が発生してしまいます。
数式を変更して該当セルのすぐ下だけではなく、セル2～12までの範囲文字を表示出来るようにできる方法を教えてください。
尚、セル2～12までの文字は全て同じ文字が表示させますので、同じ文字の内、W17には一つの文字「例：鉄骨造」の表示としたいです。
よろしくお願いいたします。

No.1 ベストアンサー 回答者： むっすー 回答日時： 2023/02/15 09:51

質問拝見いたしました。

解決するための関数と、何故、うまくいかなかったのかについても回答いたします。面倒でしたら前者の方だけお読みください。

■解決するための関数

HLOOKUPを使う場合は、
=IFERROR(HLOOKUP("構造_主要",$A1:$PA12,MATCH("*",IH2:IH12,0)+1,FALSE),"")

またはこうです。
=IFERROR(INDIRECT("IH"&MATCH("*",IH2:IH12,0)+1),"")

この数式をW17セルに記入してみてください。
ポイントは以下の通り。
・ＩＨ列の2行目以降に何かしらの文字が入っていればそれを表示
・優先順位はIH2＞IH12で、上の方のセルに書いてある文字が優先
・IH2～IH12のすべてのセルが空白の場合は空白になる


■質問者様の関数が思った通りに動かなかったのはなぜか

質問者様が書いた関数はコレですね。
HLOOKUP("構造_主要",$A1:$PA2,2,0)
分かりやすく引数を分解するとこうです。

第１引数："構造_主要"
第２引数：$A1:$PA2
第３引数：2
第４引数：0

じっくり見ていきましょう。
第２引数で指定しているのはこの関数の適用範囲です。
検索範囲と言ってもよいでしょう。
$A1:$PA2と指定していますので、
A列の１行目からPA列の２行目までが適用範囲になりますね。
ここで大事なのは１行目～２行目が適用範囲ということです。
範囲が２行目までしかありませんので、３行目以降に何が書かれていようがこの関数には全く関係がありません。

質問者様は
＞物件によってはIH2～IH12までの範囲に
＞文字「鉄骨造」が表示されるので、
＞IH2の文字「鉄骨造」が無い場合は
＞W17には何も表示されない不具合が発生してしまいます。

このようにお話しされておりますが、それもそのはずです。
適用範囲に含まれているのはIH2だけで、IH3以降は無関係だからです。
適用範囲を例えば１２行目まで広げるのであれば、
第２引数を$A1:$PA12とすればよいでしょう。

次に問題なのが第３引数です。
２を指定していますね。これは、構造_主要というセルがある列（IH列）の
２行目を表示するという意味になります。
ですので、先ほどの適用範囲を広げたとしてもこの数値が２のままでは
必ず２行目、つまりIH2のセルの中身を表示することになります。
ご質問者様がおっしゃる通り、
IH2が空白であれば関数の結果も空白になりますね。
この数字を３にすればIH3、4にすればIH4の中身を表示することができます。

まとめますと、
第２引数と第３引数の指定の仕方が間違っています。

第２引数：$A1:$PA2　→　$A1:$PA12
第３引数：2　→　表示させたい行番号

しかしこの方法ではいちいち第３引数に数字を指定する必要があり面倒ですので普通はこのようなやり方はしません。
今回はこの数字を取得するためにMATCH関数を使用することで無理やりHLOOKUP関数で実現していますが、普通はVLOOKUP関数を使用します。
もしその情報が必要でしたらコメントください。

この回答へのお礼

回答ありがとうございました。
詳しく説明していただきまして
感謝いたします。
理解出来ました。

お礼日時：2023/02/15 10:04