スキルアップ・問題解決なら、モーグにおまかせ！

Excelの関数について質問です
 
F6からI9の範囲の中に16名のメンバーを入れ
その中から1人をL5に入れ、L5と同じ行の人をM5からO5のセルに反映させたいのですが
何度やってもL5と同じ人がM5からO5に入ってしまうことがあります。
 
そこで
L5の人がF列にいた場合は、同じ行にいるG列、H列、I列の人をM5からO5に反映させ
L5の人がG列にいた場合は、同じ行にいるF列、H列、I列の人をM5からO5に反映させ
L5の人がH列にいた場合は、同じ行にいるF列、G列、�T列の人をM5からO5に反映させ
L5の人がI列にいた場合は、同じ行にいるF列、G列、H列の人をM5からO5に反映し
該当しないセルは空白にしたいです。
 
現在作成したExcelは
=IFERROR(INDEX($F$6:$I$10, MATCH($L5, $F$6:$F$10, 0), 2), IFERROR(INDEX($F$6:$I$10, MATCH($L5, $G$6:$G$10, 0), 2), IFERROR(INDEX($F$6:$I$10, MATCH($L5, $H$6:$H$10, 0), 2), INDEX($F$6:$I$10, MATCH($L5, $I$6:$I$10, 0), 2))))
 
 
どなたか教えてください。
よろしくお願いいたします。

説明が理解できないところがあります。
 
>L5と同じ行の人をM5からO5のセルに反映させたい
 
L5と同じ行の人・・・5行目には、データが存在しないけど・・・
 
具体的な表を掲示し、
意図する結果を掲示して貰えませんか？
説明とINDEXで参照しているセル範囲も違っているし・・・
絶対参照について理解していますか？
 
表のレイアウト
　　F　 G H I
6 あ　い　う　え
7　 お か　き　く
8 け　こ　さ　し
9 す　せ　そ　た

M5セル =IF(COUNTIF($F6:$I6,$L5),INDEX($F6:$I6,AGGREGATE(15,6,{1,2,3,4}/($F6:$I6<>$L5),COLUMN(A1))),"")
 
右にコピー

　　F　 G　 H　 I　J　K　 L　 M　 N　 O
5　　　　　　　　　　　　H8　I8　F8　G8
6　F6　G6　H6　I6　　　　　　　　　　　
7　F7　G7　H7　I7　　　　　　　　　　　
8　F8　G8　H8　I8　　　　　　　　　　　
9　F9　G9　H9　I9　　　　　　　　　　　
 
M5
=INDIRECT(TEXT(SUMPRODUCT(($F6:$I9=L5)*(ROW($F6:$I9)*100+6+MOD(COLUMN($F6:$I9)-1,4))),"!R0C00"),FALSE)
右方向・→

WinArrow さんの引用:
説明が理解できないところがあります。
 
>L5と同じ行の人をM5からO5のセルに反映させたい
 
L5と同じ行の人・・・5行目には、データが存在しないけど・・・
 
具体的な表を掲示し、
意図する結果を掲示して貰えませんか？
説明とINDEXで参照しているセル範囲も違っているし・・・
絶対参照について理解していますか？
 
表のレイアウト
　　F　 G H I
6 あ　い　う　え
7　 お か　き　く
8 け　こ　さ　し
9 す　せ　そ　た

$F$6:$I$9 にするだけ。
 
　　F　 G　 H　 I　J　K　 L　 M　 N　 O
5　　　　　　　　　　　　H8　I8　F8　G8
6　F6　G6　H6　I6　　　　I6　F6　G6　H6
7　F7　G7　H7　I7　　　　G9　H9　I9　F9
8　F8　G8　H8　I8　　　　　　　　　　　
9　F9　G9　H9　I9　　　　　　　　　　　
 
M5
=IFERROR(INDIRECT(TEXT(SUMPRODUCT(($F$6:$I$9=L5)*(ROW($F$6:$I$9)*100+6+MOD(COLUMN($F$6:$I$9)-1,4))),"!R0C00"),FALSE),"")
右方向・→下方向・↓

んなっと さんの引用:
　　F　 G　 H　 I　J　K　 L　 M　 N　 O
5　　　　　　　　　　　　H8　I8　F8　G8
6　F6　G6　H6　I6　　　　　　　　　　　
7　F7　G7　H7　I7　　　　　　　　　　　
8　F8　G8　H8　I8　　　　　　　　　　　
9　F9　G9　H9　I9　　　　　　　　　　　
 
M5
=INDIRECT(TEXT(SUMPRODUCT(($F6:$I9=L5)*(ROW($F6:$I9)*100+6+MOD(COLUMN($F6:$I9)-1,4))),"!R0C00"),FALSE)
右方向・→

んなっと さんの引用:
$F$6:$I$9 にするだけ。
 
　　F　 G　 H　 I　J　K　 L　 M　 N　 O
5　　　　　　　　　　　　H8　I8　F8　G8
6　F6　G6　H6　I6　　　　I6　F6　G6　H6
7　F7　G7　H7　I7　　　　G9　H9　I9　F9
8　F8　G8　H8　I8　　　　　　　　　　　
9　F9　G9　H9　I9　　　　　　　　　　　
 
M5
=IFERROR(INDIRECT(TEXT(SUMPRODUCT(($F$6:$I$9=L5)*(ROW($F$6:$I$9)*100+6+MOD(COLUMN($F$6:$I$9)-1,4))),"!R0C00"),FALSE),"")
右方向・→下方向・↓

半平太 さんの引用:
M5セル =IF(COUNTIF($F6:$I6,$L5),INDEX($F6:$I6,AGGREGATE(15,6,{1,2,3,4}/($F6:$I6<>$L5),COLUMN(A1))),"")
 
右にコピー

imaima さんの引用:

んなっと さんの引用:
$F$6:$I$9 にするだけ。
 
　　F　 G　 H　 I　J　K　 L　 M　 N　 O
5　　　　　　　　　　　　H8　I8　F8　G8
6　F6　G6　H6　I6　　　　I6　F6　G6　H6
7　F7　G7　H7　I7　　　　G9　H9　I9　F9
8　F8　G8　H8　I8　　　　　　　　　　　
9　F9　G9　H9　I9　　　　　　　　　　　
 
M5
=IFERROR(INDIRECT(TEXT(SUMPRODUCT(($F$6:$I$9=L5)*(ROW($F$6:$I$9)*100+6+MOD(COLUMN($F$6:$I$9)-1,4))),"!R0C00"),FALSE),"")
右方向・→下方向・↓

 
 
出来ました。
ありがとうございます。
何日も悩んでたので本当に助かりました。
本当に本当にありがとうございました。

imaima さんの引用:

imaima さんの引用:

んなっと さんの引用:
$F$6:$I$9 にするだけ。
 
　　F　 G　 H　 I　J　K　 L　 M　 N　 O
5　　　　　　　　　　　　H8　I8　F8　G8
6　F6　G6　H6　I6　　　　I6　F6　G6　H6
7　F7　G7　H7　I7　　　　G9　H9　I9　F9
8　F8　G8　H8　I8　　　　　　　　　　　
9　F9　G9　H9　I9　　　　　　　　　　　
 
M5
=IFERROR(INDIRECT(TEXT(SUMPRODUCT(($F$6:$I$9=L5)*(ROW($F$6:$I$9)*100+6+MOD(COLUMN($F$6:$I$9)-1,4))),"!R0C00"),FALSE),"")
右方向・→下方向・↓

 
 
出来ました。
ありがとうございます。
何日も悩んでたので本当に助かりました。
本当に本当にありがとうございました。

 
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
 
すみません。
もう一度教えてください。
この式で　表が空欄だったら　空白　に指定してあるように思えるのですが
空白だと　0　となってしまいます。
表の中の人が空欄だった時にMからOを空白にするにはどうしたら良いでしょうか？
何度もすみません。
よろしくお願いいたします。

質問の際は、毎回必ず具体例を表形式で書くようにしてください。
↓こんな感じで。
 
　　F　 G　 H　 I　J　K　 L　 M　 N　 O
5　　　　　　　　　　　　H8　I8　F8　G8
6　F6　G6　H6　I6　　　　I7　F7　G7　H7
7　F7　G7　H7　I7　　　　G9　H9　　　F9
8　F8　G8　H8　I8　　　　　　　　　　　
9　F9　G9　H9　　←空白　　　　　　　　　
 
M5
=IF($L5="","",IFERROR(INDIRECT(TEXT(SUMPRODUCT(($F$6:$I$9=$L5)*(ROW($F$6:$I$9)*100+6+MOD(COLUMN($F$6:$I$9)-2+COLUMN(A5),4))),"!R0C00"),FALSE)&"",""))
右方向・→下方向・↓

もしかしたらこんなのも。
いったんM:N列の現在の式をすべて削除して
 
　　F　 G　 H　 I　J　K　 L　 M　 N　 O
5　　　　　　　　　　　　H8　F8　G8　I8
6　F6　G6　H6　I6　　　　I7　F7　G7　H7
7　F7　G7　H7　I7　　　　G9　F9　H9　　
8　F8　G8　H8　I8　　　　　　　　　　　
9　F9　G9　H9　　　　　　　　　　　　　
 
M5
=IF($L5="","",IFERROR(FILTER(INDEX($F:$I,AGGREGATE(15,6,ROW($F$6:$I$9)/($F$6:$I$9=$L5),1),)&"",INDEX($F:$I,AGGREGATE(15,6,ROW($F$6:$I$9)/($F$6:$I$9=$L5),1),)<>$L5),""))
下方向・↓

imaima さんの引用:

imaima さんの引用:

imaima さんの引用:

んなっと さんの引用:
$F$6:$I$9 にするだけ。
 
　　F　 G　 H　 I　J　K　 L　 M　 N　 O
5　　　　　　　　　　　　H8　I8　F8　G8
6　F6　G6　H6　I6　　　　I6　F6　G6　H6
7　F7　G7　H7　I7　　　　G9　H9　I9　F9
8　F8　G8　H8　I8　　　　　　　　　　　
9　F9　G9　H9　I9　　　　　　　　　　　
 
M5
=IFERROR(INDIRECT(TEXT(SUMPRODUCT(($F$6:$I$9=L5)*(ROW($F$6:$I$9)*100+6+MOD(COLUMN($F$6:$I$9)-1,4))),"!R0C00"),FALSE),"")
右方向・→下方向・↓

 
 
出来ました。
ありがとうございます。
何日も悩んでたので本当に助かりました。
本当に本当にありがとうございました。

 
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
 
すみません。
もう一度教えてください。
この式で　表が空欄だったら　空白　に指定してあるように思えるのですが
空白だと　0　となってしまいます。
 
表の中の人が空欄だった時にMからOを空白にするにはどうしたら良いでしょうか？
また、表の人数を増やしたい場合、範囲を広げると出来なくなります。どうしたら良いでしょうか
何度もすみません。
よろしくお願いいたします。

引用は使わないように。
それから、試した結果を手抜きせずに書きなさい。
「表の人数を増やしたい場合...」は、今のところ無視します。

K列まで増やしたとき。
  
　　F　 G　 H　 I　 J　 K　 L　 M　 N　 O　 P　 Q
5　　　　　　　　　　　　　H8　F8　G8　I8　　　　
6　F6　G6　H6　I6　J6　　　I7　F7　G7　H7　J7　　
7　F7　G7　H7　I7　J7　　　G9　F9　H9　I9　J9　K9
8　F8　G8　H8　I8　　　　　　　　　　　　　　　　
9　F9　G9　H9　I9　J9　K9　　　　　　　　　　　　
  
M5
=IF($L5="","",IFERROR(FILTER(INDEX($F:$K,AGGREGATE(15,6,ROW($F$6:$K$9)/($F$6:$K$9=$L5),1),)&"",INDEX($F:$K,AGGREGATE(15,6,ROW($F$6:$K$9)/($F$6:$K$9=$L5),1),)<>$L5),""))
下方向・↓
 
※FILTER関数が使えない環境ならだめ。

長いけど、こんなのも。
  
　　F　 G　 H　 I　 J　 K　 L　 M　 N　 O　 P　 Q
5　　　　　　　　　　　　　H8　F8　G8　I8　　　　
6　F6　G6　H6　I6　J6　　　I7　F7　G7　H7　J7　　
7　F7　G7　H7　I7　J7　　　G9　F9　H9　I9　J9　K9
8　F8　G8　H8　I8　　　　　　　　　　　　　　　　
9　F9　G9　H9　I9　J9　K9　　　　　　　　　　　　
  
M5
=IF($L5="","",IFERROR(INDEX($A:$K,AGGREGATE(15,6,ROW($F$6:$K$9)/($F$6:$K$9=$L5),1),
AGGREGATE(15,6,1*TEXT(COLUMN($F$6:$K$6),"[<>"&MOD(AGGREGATE(15,6,100*ROW($F$6:$K$9)
+COLUMN($F$6:$K$9)/($F$6:$K$9=$L5),1),100)&"]0;;"),COLUMN(A5)))&"",""))
  
右方向・→下方向・↓

んなっと様
 
早速返信いただきありがとうございます。
10月4日7時29分に投稿頂いた関数で試してみたところ　出来ました！！
朝早くから投稿頂いたおかげで、今日からこちらの関数を使うことが出来て本当に助かりました。
試した結果としては
M5に教えて頂いた関数を入れ、結果を入れたい範囲に式をコピーすると
F6からK9の表の中からL5に指定した人がM5からR5に自動で入ってきます。
空欄のところはちゃんと空白にもなりました。
自分で参照範囲をK10まで増やしたりしてやってみましたがちゃんと検索できました。
しかもちゃんとFからKの順番を優先してMからRに結果が入ってきます。
関数の意味はまだ理解できませんが、範囲を変更することは何とか変更出来ました。
今後人数やグループが増えても使えるので本当に助かりました。
※今回の関数で、AからK全体を範囲指定していて、A5のセルも参照？するのが何故なのか、その意味する事もわかりませんが今後理解できるように研究していきたいと思います。
 
ずっと何日も悩んでおり、このサイトを見つけて今回初めて投稿をしたため
投稿の仕方や作法がわからずご迷惑をおかけしてしまいました。
今後は【引用】を使わず、どなたへのお返事でも【コメント】にて投稿するようにいたします。
また、Excelの質問は　んなっと様が投稿してくださったようなExcelの表を作成し
お伺いするということも知りました。
色々教えて頂き、本当に、本当にありがとうございました。
 
最後に、、
10月3日19時55分に投稿頂いた関数は
んなっと様のいう様にFILTER関数が使えない環境のようで
【その関数は正しくありません】となってしまいました。　
いろいろ考えてくださって本当にありがとうございました。
長々と書いてしまいましたが本当に本当にありがとうございました。

回答いただいた皆様
 
いろいろと回答いただきありがとうございました。
おかげさまで解決しました。
本当にありがとうございました。