エクセルで複数行を検索し該当するセルと求める。

質問

お知恵を拝借したいのですが。
1チーム4名で10チームの組合せ表を作りたいのですが、
例）
　　　1　　　2　　　　　3　　　　4　　　　5
A　1組目　山田太郎　鈴木一夫　佐藤一郎　佐々木清
B　2組目　山下清　　山田栄二　斉藤仁司　仲居靖
C　3組目　豊田一郎　小田琢磨　小林琢磨　中村博
・
・
・
J　10組目　安田孝三　千田卓也　木村洋二　田中譲二
セル「K2」にセル「K1」で入力した氏名を入れると該当する組名を返す関数は無いでしょうか？
「K1」山田栄二→「K2」2組目
皆さんアドバイスをお願いします。

回答No.3

mu2011 · ベストアンサー

一例です。
行・列番号表記が紛らわしいので２通り回答します。
範囲がA1:E10のケース
=IF(COUNTIF(B1:E10,K1),INDEX(A:A,SUMPRODUCT((B1:E10=K1)*ROW(B1:E10))),"")
範囲がA1:J5のケース
=IF(COUNTIF(A2:J5,K1),INDEX(A1:J1,,SUMPRODUCT((A2:J5=K1)*COLUMN(A2:J5))),"")

お礼

ありがとうございます。
まさに、求めていたものです。
皆さん、行列番号が逆になっていて申し訳ありませんでした。あわてて質問したので、思いっきり間違えていました。とても助かりました。
またよろしくお願いします。

回答No.2

Nouble

誰も２組掛け持ちしないと仮定します

これはどうでしょう
=sumproduct(($A$1:$J$5=$K$1)*column($A$1:$J$1))&"組目"

要は此の５０個のセルの内１つしかＫ１と一致しない
つまりイコールを掛けた結果が
１つだけ１で
これ以外全部０になる訳ですが
その１がある列が何処か分かれば良いのですよね

※) エラー処理は省きました
するとしたら
=choose(sign(countif($A$1:$J$5,$K$1)-1)+2,"該当無し",sumproduct(($A$1:$J$5=$K$1)*column($A$1:$J$1))&"組目","重複あり")
極端な話し
全てのセルが一致してもエラーにはならない筈です

回答No.1

n_na_tto

表のレイアウトがどちらかわからないので..

●右方向に1組目,2組目...のとき
＿＿＿＿＿A＿＿＿＿_B＿＿＿＿_C＿＿＿＿_K_
1＿＿_1組目＿＿_2組目＿＿_3組目＿田中譲二_
2＿山田太郎＿＿山下清＿豊田一郎＿＿10組目_
3＿鈴木一夫＿山田栄二＿小田琢磨＿＿＿＿＿_
4＿佐藤一郎＿斉藤仁司＿小林琢磨＿＿＿＿＿_
5＿佐々木清＿＿仲居靖＿＿中村博＿＿＿＿＿_

K2
=IF(COUNTIF($A$2:$J$5,K1)=1,SUMPRODUCT(COLUMN($A:$J)*($A$2:$J$5=K1))&"組目",COUNTIF($A$2:$J$5,K1)&"!")

●下方向に1組目,2組目...のとき
＿＿＿＿＿_A＿＿＿＿_B＿＿＿＿_C＿＿＿＿_D＿＿＿＿_E_
_1＿＿_1組目＿山田太郎＿鈴木一夫＿佐藤一郎＿佐々木清_
_2＿＿_2組目＿＿山下清＿山田栄二＿斉藤仁司＿＿仲居靖_
_3＿＿_3組目＿豊田一郎＿小田琢磨＿小林琢磨＿＿中村博_
11＿山田太郎＿＿_1組目＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿_

B11
=IF(COUNTIF($B$1:$E$10,A11)=1,SUMPRODUCT(ROW($1:$10)*($B$1:$E$10=A11))&"組目",COUNTIF($B$1:$E$10,A11)&"!")

なければ0!、重複していれば2!を返します。