エクセル関数の検索について

質問

エクセルの関数についてご教授下さい。
以下のように文字列が記入されています。

(列A)
りんご
a
b
c
(w)
(k)
!
みかん
u
b
c
(w)
(k)
!

○”りんご”の行から”!”の行までを検索して、文字列”a”があるかを確認したい
○”みかん”の行から”!”の行までを検索して、文字列”a”があるかを確認したい
○()で記載している文字列は場合によって記入されているため、毎回同じ行数ではない

このような条件がある時に、関数で要件を満たす事はできますでしょうか？
イメージ的には、以下のようにできればと思ってます。

１．A列全体から”りんご”を検索
２．”りんご”行から下の”！”までの間で”a”があるかを確認
３．A列全体から”みかん”を検索
４．”みかん”行から下の”！”までの間で”a”があるかを確認

もしくは

１．A列全体から”りんご”を検索
２．”りんご”行から下で、”a”があるかを確認
３．A列全体から”みかん”を検索
４．”みかん”行から下で、”a”があるかを確認

このような感じで作れますでしょうか?
よろしくお願いいたします。

回答No.1

mshr1962

１．A列全体から”りんご”を検索
C1=MATCH("りんご",A:A,0)
２．”りんご”行から下の”！”までの間で”a”があるかを確認
C2=IF(ISNA(MATCH("a",OFFSET($A$1,C1,0,65535-C1),0)),"なし",IF(MATCH("!",OFFSET($A$1,C1,0,65535-C1),0)>MATCH("a",OFFSET($A$1,C1,0,65535-C1),0),"あり","なし"))

３．４．は上記１．２．と同じでC1の"りんご"が"みかん"になるだけです。
※最後のセルには必ず!を設定してください。

お礼

mshr1962様

早速のご返答ありがとうございます！
凄過ぎて、何が何だか理解できませんでしたが、そのままやったら完璧にできました！
いろいろなサイトに投稿しても出来なかったのですが、1回目の回答で出来るなんて感動です。
本当に良かったです。

今後ともよろしくお願いいたします<(_ _)>