エクセルで縦横参照で見出し行のデータを抽出したい

質問

一般的なINDEX、MATCH関数の組合せではどうしてもわからずお知恵を拝借致したくお願い致します。
添付画像でＡ列とＢ２：Ｋ１１までの範囲を検索して、Ａ列のＦを右に見てＫ７のＦと交叉して見出し行の水をＢ１５セルに出力したいのですが、どのようにしたら良いでしょうか？
出来ましたら関数でお願いしたいのですが。
よろしくお願い申し上げます。

回答No.5

DOUGLAS_ · ベストアンサー

[回答番号：No.1] の DOUGLAS_ です。

＞最初にC13セルのFと同じFをＡ列で特定します。
　これは
=MATCH(C13,$A$2:$A$11,0)
となり、答えは「6」、つまり「$A$2」セルから数えて６つ下のセルですから「A7」セルになります。
「7行目」とお書きなのはこのことですよね。

＞更にA13セルのFをB2からK11までの範囲で
＞7行目のFを特定して
　これは「B7:K7」の中から「A13セルのF」を探すということですかね。
　単純な順序で考えたら、
=MATCH(A13,B7:K7,0)　・・・式Ａ
答えは「10」となりますが、もともと「7行目」というのが前述の答え「6」に関係していますので、「B7:K7」の部分を
OFFSET(A1,MATCH(C13,$A$2:$A$11,0),1,1,10)　・・・式Ｂ
に書き換える必要があります。

　ということで、式Ａは
=MATCH(A13,式Ｂ,0)
=MATCH(A13,OFFSET(A1,MATCH(C13,$A$2:$A$11,0),1,1,10),0)　・・・式Ｃ
となります。

＞その交わった見出し行の水をB15に出力したいのです。
　これは、「B1:K1」にある曜日見出しのなかから「10」番目の要素を選ぶことになりますので、
=INDEX($B$1:$K$1,式Ｃ)
つまり、B15:
=INDEX($B$1:$K$1,MATCH(A13,OFFSET(A1,MATCH(C13,$A$2:$A$11,0),1,1,10),0))
となります。

＞次にB13セルにAが入力されているとして、
ふむふむ．．．
＞同じようにして7行目のFを特定してその交わった
ここは、どうして「F」なのでしょうか？
＞見出し行の木をB20セルに出力出来るようにしたいのです。
そして、「7行目のF」とお書きなのが「K7」なら見出し行は「水」、「B8」なら「月」、また、「7行目のF」ではなくて、「7行目のA」というのでしたら、「D7」なら見出し行は「水」、「G8」なら「土」になり、いずれの場合も「見出し行の『木』」にはなりません。
　意味不明ですので、こちらはご勘弁ください。

お礼

DOUGLAS_様
この度は大変丁寧にご教授下さいまして厚く御礼申し上げます。
初めての会員登録＆投稿だった為、質問の仕方に不適切な面があり、
又、補足質問でもいざ送信しようとしたら何度もＮＧワードと出て
充分こちらの意を伝えることが出来ませんでした。
お手数をおかけ致しまして申し訳御座いませんでした。
今回の回答では当方のやりたいことが出来ず、後日改めて画像と共に再質問を予定しております。
その節にはどうぞよろしくお願い申し上げます。
有難う御座いました。

回答No.4

tom04

No.3です！
たびたびごめんなさい。

画像のA13とC13が逆になっているような気がします。

もう一度画像をアップさせていただきます。

数式は前回の通りで良いと思います。

どうも何度も失礼しました。m(__)m

回答No.3

tom04

こんばんは！
横から失礼します。

質問の画像はたまたまA列の項目と同じ物が表内にあるため
簡単な数式でできそうですが、

結局C13セルでA列を参照し、行を選択、次にA13セルで列を選択し
1行目の見出しを返したいという意味ですよね？

一応そういうことだと解釈しての回答になります。

色々方法はあるかと思いますが・・・
一例です。

↓の画像で全ての行を名前定義しておきます。
B2～K2セルを範囲指定し、挿入　→　名前　→　定義から　_A　
同様にB3～K3セルを範囲指定　名前の定義　→　_B　という具合に
全ての行に名前定義します。
（直接右下の画像のように名前ボックスに入力しても構いません）

今回は名前定義する場合数字やA・B・・・などの記号は名前定義できない場合がありますので、
敢えて、アンダーバーをアルファベットの前に付けています

最後にC20セルに
=IF(OR(A13="",C13=""),"",INDEX(B1:K1,,MATCH(A13,INDIRECT("_"&C13),0)))
という数式を入れています。

これでA13・C13セルにデータを入力すれば希望の見出しが表示されると思います。

以上、参考になれば幸いですが
他に良い方法があったり、
的外れなら読み流してくださいね。m(__)m

お礼

tom04様
この度は大変丁寧にお教え頂きまして有難う御座います。
今回は初めての投稿だった為、適切な質問が出来ず、当方のやりたいことが出来ませんでした。
後日改めて画像と共に再質問を予定しております。
その節にはどうかよろしくお願い申し上げます。

回答No.2

hallo-2007

Offset関数で、列方向の範囲を指定して、更にMatch関数で列方向に検索させては
如何ですか。
仮に、A列で　Fのある列を検索
検索して見つかった行で列方向に　E　を検索して得られた　数値で
1行目をINDEX関数で表示させます。
=INDEX(1:1,,MATCH("E",OFFSET(A1,MATCH("F",A:A,FALSE)-1,0,1,100),FALSE))
こんな感じで　火　が表示されました。

お礼

hallo-2007様
この度は大変丁寧にご教授下さいまして厚く御礼申し上げます。
初めての会員登録＆投稿だった為、質問の仕方に不適切な面があり、
当方のやりたいことが出来ず、後日改めて画像と共に再質問を予定しております。
その節にはどうぞよろしくお願い申し上げます。
有難う御座いました。

回答No.1

DOUGLAS_

＞Ａ列のＦを右に見てＫ７のＦと交叉
　「Ａ列のＦ」は A7 にありますが、B7:K7 の間に「Ｆ」は１つしか存在しないという条件でしょうか？
　そういうことでしたら、そのセルの番号は
=MATCH(A7,B7:K7,0)
になります。
　お示しの条件の場合は、上記の式の答えは「10」になります。

　次に、
＞見出し行の水をＢ１５セルに出力したい
とのことですが、これは、B1:K1 の間で「10」番目のセルを参照すればよいことになりますので、
=INDEX(B1:K1,10)
つまり、
B15:
=INDEX(B1:K1,MATCH(A7,B7:K7,0))
ということになります

　必要に応じて「B$1:K$1」などのように絶対参照にしてください。

お礼

DOUGLAS_様
NANJA7です。
度々申し訳御座いません。
お蔭様でばっちり出来ました。
嬉しくて嬉しくてとても感謝です。
有難う御座いました。

補足

DOUGLAS_様
有難う御座いました。
＞7行目の間にFは１つしか存在しません。
各行も同じです
説明不足があり、もう少しお願い致します。
A13セルとC13セルにFが入力されているとします。
次に最初にC13セルのFと同じFをＡ列で特定します。
更にA13セルのFをB2からK11までの範囲で
7行目のFを特定してその交わった見出し行の水をB15に出力したいのです。
次にB13セルにAが入力されているとして、
同じようにして7行目のFを特定してその交わった
見出し行の木をB20セルに出力出来るようにしたいのです。

大変お手数をおかけ致しますがよろしくご教授頂きたくお願い申し上げます。