エクセルの数式で質問があります

質問

以下のような面談表を作りました。
B10に『１』とうつとB2に『太郎』と出るようにVLOOKUPで数式を作りました。

次にこれを発展させてI14に『１』といれると
H10とI10にそれぞれ『6日（火』と『13:30～13:45』と表示されるようにするには
H10とI10にどういった関数を入れたらいいでしょうか。

自分なりに調べてはみたのですが、ある範囲の中から条件に合致するセルを
選び、指定された範囲の左端や上のセルを返すといったことをどうやってやるのかわからずで。
できれば式の解説まであると非常にありがたいです。
とても困っています・・・

回答No.3

noname#204879 · ベストアンサー

１．セル I16 に次式を入力して、此れを下方にズズーッと(セル I20 まで)ドラッグ＆ペースト
　　=IF(ISERROR(MATCH($I$14,B10:E10,0)),0,MATCH($I$14,B10:E10,0))
２．セル J15 に次式を入力して、此れを右方にズズーッと(セル M15 まで)ドラッグ＆ペースト
　　=IF(ISERROR(MATCH($I$14,B10:B14,0)),0,MATCH($I$14,B10:B14,0))
３．行15～20 を非表示に設定
４．次に示した左端の各セルに、それぞれ右に示した式を入力
　　H10: =INDEX(B9:E9,MAX(I16:I20))
　　I10: =INDEX(A10:A14,MAX(J15:M15))

お礼

皆さんありがとうございました

回答No.6

basic_gg

ちょっと遊んでみました。No.4 tom04さんとほぼ同じ式ですね。
多くの人の説明があるので、私の説明は省略します。
H10=IF(COUNTIF(B10:E14,"="&I14)=1,INDEX(B9:E9,1,SUMPRODUCT(($B$10:$E$14=$I$14)*COLUMN(A1:D1))),"")
I10=IF(COUNTIF(B10:E14,"="&I14)=1,INDEX(A10:A14,SUMPRODUCT(($B$10:$E$14=$I$14)*ROW(A1:A5)),1),"")

回答No.5

kagakusuki

別にANo.4様の様に表を一つに纏めなくとも、御質問者様の添付画像通りの配置でしたら、以下の関数で表示させる事が出来ます。
　但し、B～E列の範囲内に、I14に入力した数字と同じ数字が複数存在している場合には、検索出来なくなりますので、その場合は「重複があるため検索不能」と表示されます。

【H10セルに入力する関数】

=IF(AND($I$14<>"",COUNTIF($B$10:$E$14,$I$14)=1),INDEX($1:$1,SUMPRODUCT(COLUMN($B$1:$E$1)*(COUNTIF(OFFSET($A:$A,,COLUMN($B$1:$E$1)-COLUMN($A$1)),$I$14)-($B$1:$E$1=$I$14)*2=1))),"")

【I10セルに入力する関数】

=IF($I$14="","",IF(COUNTIF($B$10:$E$14,$I$14)=1,INDEX($A:$A,SUMPRODUCT(ROW($A$10:$A$14)*(COUNTIF(OFFSET($B$9:$E$9,ROW($A$10:$A$14)-ROW($A$9),),$I$14)=1))),IF(COUNTIF($B$10:$E$14,$I$14),"重複があるため"&CHAR(10)&"検索不能",IF(COUNTIF($H$2:$H$6,$I$14),"予定なし","該当者なし"))))

　尚、I10セルの表示が「重複があるため検索不能」となる場合には、表示される文が長いため、セルの書式設定を[折り返して全体を表示する]にしておかれる事を御奨め致します。
　それと、I14に入力した値がH2～H6の範囲に存在していない場合には、「該当者なし」と表示されます。
　又、I14に入力した値がH2～H6の範囲に存在している場合であっても、B10～E14（下の表）に記入されていなかった場合には、「予定なし」と表示されます。

　因みに、日付と時間帯を個別のセルに表示させなくとも、以下の様な関数

=IF(COUNTIF($H$2:$H$6,$I$14),IF(COUNTIF($B$10:$E$14,$I$14)=1,VLOOKUP($I$14,$H$2:$I$6,2)&"さんの面談は"&CHAR(10)&TEXT(INDEX($1:$1,SUMPRODUCT(COLUMN($B$1:$E$1)*(COUNTIF(OFFSET($A:$A,,COLUMN($B$1:$E$1)-COLUMN($A$1)),$I$14)-($B$1:$E$1=$I$14)*2=1))),"d日(aaa)")&" "&INDEX($A:$A,SUMPRODUCT(ROW($A$10:$A$14)*(COUNTIF(OFFSET($B$9:$E$9,ROW($A$10:$A$14)-ROW($A$9),),$I$14)=1)))&CHAR(10)&"です",IF(COUNTIF($B$10:$E$14,$I$14),"重複があるため"&CHAR(10)&"検索不能","予定なし")),IF($I$14="","","該当者なし"))

としますと、1つのセル内に例えば

大輔さんの面談は
6日(火) 13:30～13:45
です

等の様に表示させる事が出来ます。（添付画像のI17セルを参照の事）

回答No.4

tom04

こんばんは！
画像ではA～E列に同じ表が並んでいますが、
これは一つにまとめることはできないのでしょうか？
そうすれば関数で簡単に表示できます。

とりあえず画像の下の表にデータが入っていますので、それを利用する場合

H10セルに
=IF(AND(I14<>"",COUNTIF(B10:E14,I14)),INDEX(B9:E9,,SUMPRODUCT((B10:E14=I14)*COLUMN(A1:D1))),"")

I10セルに
=IF(AND(I14<>"",COUNTIF(B10:E14,I14)),INDEX(A10:A14,SUMPRODUCT((B10:E14=I14)*ROW(A1:A5))),"")

という数式を入れてみてください。

※　表内の数値データに重複はない！という前提です。

的外れならごめんなさいね。m(_ _)m

回答No.2

aokii

VBAなら簡単ですが、エクセル関数一発だと、ちょっと大変なので、

F10セルに、
=IF(ISNA(MATCH($I$14,B10:E10,0)),"",MATCH($I$14,B10:E10,0))
F18セルまで下にコピー、

B19セルに、
=IF(ISNA(MATCH($I$14,B10:B18,0)),"",MATCH($I$14,B10:B18,0))
E19セルまで右にコピー、

H10セルに、
=INDEX(B9:E9,1,SUM(F:F))　　
H10セルの書式は、ユーザー設定のd"日"(aaa)

I10セルに、
=INDEX(A10:A18,SUM(F:F),1)

でいかがでしょうか。

回答No.1

K Kazz（@JazzCorp）

H10：
="『6日(火』"
I10：
="『13:30～13:45』"