EXCEL 「または」で行を検索して一致する一番右の列番号を使ってセルの値を表示したい

下のような表があるとします。
３行目B列～J列のセルには「H20」～「H28」（年度）が入力されいます。
４行目以降のB列～J列のセルには「会長」、「立会」、「会計」のいずれかが入力されているか、空白です。
４行目以降のk列が表のように表示される数式ができないでしょうか？
「会長」または「立会」が入力されてる一番右側の列番号の3行目のセルの値（年度）です。
「会計」が入力されいるところは無視しないといけません。
また、「会長」または「立会」が入力されていない場合に、エラーではなく「なし」のような文字列が表示できれば完璧です。

　　　A　　　B　　　C　　 　D　　　E　　　F　　　 G　　　 H　　　 I　　　 J　　　　k
3　　年度　　H20　 H21　 H22　　H23　　H24　　H25　　H26　　H27　　H28　　最終
4　　Aさん　会長　　　　　立会　　　　　　　　　　会計　　立会　　会長　　　　　　H27
5　　Bさん　立会　　　　　　　　　　　　　会長　　　　　　　　　　会計　　会長　　H28
6　　Cさん　会計　　　　　　　　　会長　　立会　　　　　　会長　　　　　　会計　　H26
7　　Dさん　　　　会長　　会計　　　　　　　　　　立会　　　　　　立会　　立会　　H28
8　　Eさん　　　　立会　　　　　　　　　　　　　　会長　　会計　　　　　　　　　　H25
9　　Fさん　　　　会計　　　　　　立会　　　　　　　　　　　　　　　　　　　　　　H23
10　 Gさん　　　　　　　　会長　　会計　　　　　　　　　　　　　　　　　　　　　　H22
11　 Hさん　　　　　　　　　　　　　　　　会計　　　　　　　　　　　　　　　　　　なし

{=INDIRECT(ADDRESS(3,○○))｝の○○のところを「会長」または「立会」が入力されてる一番右側の列番号にする数式と、入力されていない場合の処理ができずに困っています。

No.7 ベストアンサー 回答者： CoalTar 回答日時： 2016/04/24 23:02

さらに、エラー処理の応用編とか

=IF(SUM(COUNTIF(B4:J4,{"会長";"立会"})),
LOOKUP(1,0/((B4:J4="会長")+(B4:J4="立会")),$B$3:$J$3),"なし")

ここまでしなくても、XL2007以降なら
=IFERROR(LOOKUP(1,0/((B4:J4="会長")+(B4:J4="立会")),$B$3:$J$3),"なし")
で。

この回答へのお礼

いろんなパターンを教えていただき、ありがとうございました。
要件は満たしています。
私の知識不足で、完璧には理解できていないのですが、処理できました。

お礼日時：2016/05/06 22:02

No.6 回答者： mike_g 回答日時： 2016/04/24 18:52

[No.5]の式、唐突に「N４」なんてなセルを持ち出してすみませんでした。

下記が訂正版です。
K4: =IF(SUM((B4:J4="会長")+(B4:J4="立会")),INDEX(A$3:J$3,MAX(((B4:J4="会長")+(B4:J4="立会"))*COLUMN(B4:J4))),"なし")
【お断り】上式は配列数式として入力のこと

No.5 回答者： mike_g 回答日時： 2016/04/24 18:48

K4: =IF(N4,INDEX(A$3:J$3,MAX(((B4:J4="会長")+(B4:J4="立会"))*COLUMN(B4:J4))),"なし")

【お断り】上式は配列数式として入力のこと

No.4 回答者： CoalTar 回答日時： 2016/04/24 18:33

No.1,3です

エラー処理もしてみました
=IF(ISNA(LOOKUP(1,0/((B4:J4="会長")+(B4:J4="立会")))),"なし",
LOOKUP(1,0/((B4:J4="会長")+(B4:J4="立会")),$B$3:$J$3))
普通に[Enter]で確定でよいです

この回答へのお礼

いろんなパターンを教えていただき、ありがとうございました。
要件は満たしています。
私の知識不足で、完璧には理解できていないのですが、処理できました。

お礼日時：2016/05/06 21:58

No.3 回答者： CoalTar 回答日時： 2016/04/24 15:23

3行目、日付で入力なら

=TEXT(MAX((B4:J4={"会長";"立会"})*($B$3:$J$3)),"ge;;""なし""")

20,21・・・28　にHを付けた書式なら
=TEXT(MAX((B4:J4={"会長";"立会"})*($B$3:$J$3)),"!H0;;""なし""")

文字列なら
=INDEX($B$3:$J$3,MAX(IF(B4:J4={"会長";"立会"},COLUMN($B$3:$J$3)))-1)
これはエラー処理なし

いずれも[Ctrl]+[Shift]+[Enter]で確定。配列数式

No.2 回答者： tom04 回答日時： 2016/04/24 08:53

こんにちは！

すこし長くなりますが、K4セルに
=IF(OR(COUNTIF(B4:J4,"会長"),COUNTIF(B4:J4,"立会")),INDEX(B$3:J$3,MAX(IF(B4:J4="会長",COLUMN(B4:J4)-1),IF(B4:J4="立会",COLUMN(B4:J4)-1))),"なし")
配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　K4セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
最後に下へフィル＆コピーしてみてください。

※　細かいエラー処理はしていません。m(_ _)m

この回答へのお礼

ありがとうございました。
要件は満たしています。
私の知識不足で、完璧には理解できていないのですが、処理できました。

お礼日時：2016/05/06 21:59

No.1 回答者： CoalTar 回答日時： 2016/04/24 07:26

[年度]は日付にして、表示形式で戻すのはアリですか？

ナシでもできますが、複雑な数式かなっておもったので。