Excel関数 表の中のデータを検索して表示したい

質問

検索範囲が列や行の一方向でないため行き詰まってしまいました。
いい式の組み立てがありましたら、教えていただけますようお願いいたします。
クラスのリスト
A B C D
1 1組 田中 吉田 織田 伊藤
2 2組 坂本 神奈 千葉 小笠原
3 3組 浜田 金沢 岡本 多田

A列の氏名を元にB列にクラスを表示したい
A　　　B
多田　3組
伊藤　1組
・
・
・
検索のキーとなる氏名に重複はありません。
=INDEX($B$1:F3,IF(COUNTIF($B$1:$F$3,A6),ROUND(MOD(SUMPRODUCT(($B$1:$F$3=A6)*(ROW($B$1:$F$3)+ROW($B$1:$F$3)/1000)),1)*1000,),""),1)
該当の文字が入力されている行番号を検索してINDEXをつかって表示する式をつくりました。
しかし、リストの表が1行目から作成されていないとエラーになってしまいます。
なにかいい式はないでしょうか？

回答No.2

keithin

＞たとえばB2:F4に一覧があるなら
＞B2:F4から多田を検索する

添付図参照願います。

＃まぁ，関数を読んでというのは無理だったかもしれませんね。
Excel2002以降をご利用でしたら，ワークシート分析（ツールメニューにあります）の数式の検証を使って，計算をトレースして理解してみてください。

お礼

丁寧に画像つきでありがとうございます。
=INDEX(リストの開始列,SUMPRODUCT((リスト範囲=検索値)*ROW(リスト範囲)))　で応用が利くこともわかりました。
感謝です。

回答No.1

keithin

＞検索のキーとなる氏名に重複はありません

たとえばB2:F4に一覧があるなら
=INDEX(A:A,SUMPRODUCT((B2:F4=A6)*ROW(B2:F4)))
「A:A」の部分だけ変更不可。

＃もしくは，理屈がわかれば
=INDEX(A2:A4,SUMPRODUCT((B2:F4=A6)*(ROW(B2:F4)-1)))
でも同じ。

補足

早速の回答ありがとうございます。
質問がわかりにくくてすみません。

=INDEX(A:A,SUMPRODUCT((B2:F4=A6)*ROW(B2:F4)))　を使うと
B2:F4までの表の中に、該当文字が入力されている行番号が表示されます。
1組 田中 吉田 織田 伊藤
2組 坂本 神奈 千葉 小笠原
3組 浜田 金沢 岡本 多田

たとえば、この表の中の中で多田を検索したときに、3行目という行番号ではなく、3組というクラスを表示したいのですが、どうすればいいでしょうか？

多田　と入力されている隣のセルに、B2:F4を検索して、B列のクラスを表示したいのです。
下の式だとエラー#REF!になってしまいました。
配列の使い方がいまいち理解できていないのでうまく組み立てられません。
よろしくお願いいたします。