arrayformulaをしようするとエラーが出ます泣

初めての質問です。
不作法がありましたらすみません。

スプレッドシートで関数を組んでいるのですが、
ARRAYFORMULAを使わなければエラーにならず、
使うとエラーになってしまいます。
なぜエラーが起きるのか、
解消するにはどうしたらいいのか教えていただきたいです。

よろしくお願いします。


以下、作成中の関数です。（改行などは、素人なので適当です、見にくくすいません。）

=ARRAYFORMULA(
　IF(ISBLANK(L19:L),,
　　IF(N18="○○",VLOOKUP(L19:L,'シート1'!I3:J,2,0) &
　　　IF(COUNTIF('シート1'!I3:I,L19:L)<2,,CHAR(10)) &
　　　　IF(COUNTIF('シート1'!I3:I,L19:L)<2,,
　　　　　VLOOKUP(L19:L,INDIRECT("'シート1'!I"&MATCH(L19:L,'シート1'!I:I,0)+1&":J"),2,FALSE)),
　　　　　　IF(N18="××",VLOOKUP(L19:L,'シート1）'!L2:M,2,0),
　　　　)
　　　)
　　)
　)

下記ならエラーは起きず正しく返りました。
=IF(N18="○○",VLOOKUP(L19:L,'シート1'!I3:J,2,0) &
　IF(COUNTIF('シート1'!I3:I,L19:L)<2,,CHAR(10)) &
　　IF(COUNTIF('シート1'!I3:I,L19:L)<2,,
　　　VLOOKUP(L19:L,INDIRECT("'シート1'!I"&MATCH(L19:L,'シート1'!I:I,0)+1&":J"),2,FALSE)),
　　　　IF(N18="××",VLOOKUP(L19:L,'シート1'!L2:M,2,0),
　　)
　)


▼やりたいこと

N18が○○の時、
L列のデータをシート1のI列から探し出し、
L列のデータがシート1のI列に
一つならJ列の一つを返し
二つならひとつのセルに改行してJ列を二つ返させたい。
（××はシンプルなので正常に動きます）

L列　　　　シート1I列　J列
A　　　　　　　A　　　　F
B　　　　　　　A　　　　G
C　　　　　　　B　　　　H
　　　　　　　　C　　　　I
　　　　　　　　C　　　　J

結果
A　F
　 G
B　H
C　I
　 J

質問者からの補足コメント

• エラーは、
  
  MATCH の評価で値「」が見つかりませんでした。
  
  と出ます。

    補足日時：2020/06/30 17:44

• こちら、別方法で解決しましたが、
  記載の関数自体は解決していないので
  わかる方がいらっしゃいましたら回答いただけたら嬉しいです。

    補足日時：2020/07/02 19:19

No.2 回答者： ArranMALT 回答日時： 2020/07/02 22:18

№1です。

手を加えたすぎていたので、ちょっと修正しました。
結果は、少し違います。
=ARRAYFORMULA(
　IF(ISBLANK(L19:L),,
　　IF(N18="○○",VLOOKUP(L19:L,'シート1'!I3:J,2,0) &
　　　IF(COUNTIF('シート1'!I3:I,L19:L)<2,,CHAR(10)) &
　　　　IF(COUNTIF('シート1'!I3:I,L19:L)<2,,
　　　　　INDIRECT("'シート1'!I"&arrayformula(MATCH(L19,'シート1'!I:I,0))+1&":J")),
　　　　　　IF(N18="××",VLOOKUP(L19:L,'シート1）'!L2:M,2,0),
　　　　)
　　　)
　　)
　)

この回答へのお礼

返信が遅くなりすみません！
arrayformulaの中にarrayformulaを入れるという思考がありませんでした、、、
ご回答ありがとうございます！！！

お礼日時：2020/07/07 11:37

No.1 回答者： ArranMALT 回答日時： 2020/07/02 22:03

arrayformulaって便利な関数ですね。

今回初めて使ってみました。
回答にはなりませんが、分かった事を書きます。

はじめに確認ですが、
下記ならエラーは起きず正しく返りました。のコードは本当に動作しましたか？
何度試しても同じ”MATCHの評価で～”が表示されました。

試しにコードの一部だけ切り抜いてみました。

=MATCH(L19:L,'シート1'!I:I,0)
エラーになりました。”配列の値が見つかりませんでした。”

=arrayformula(MATCH(L19:L,'シート1'!I:I,0))
これは実行できました。

=INDIRECT("'シート1'!I"&arrayformula(MATCH(L19:L,'シート1'!I:I,0))+1&":J")
これも実行できました。


=arrayformula(VLOOKUP(L19:L,INDIRECT("'シート1'!I"&arrayformula(MATCH(L19:L,'シート1'!I:I,0))+1&":J"),2,FALSE))
これはエラーになりました。”MATCHの評価で～”

=arrayformula(VLOOKUP(L19:L,INDIRECT("'シート1'!I"&arrayformula(MATCH(L19,'シート1'!I:I,0))+1&":J"),2,FALSE))
これは実行できました。



ちょっと手を加えて動きましたが、少し違う結果になりそうです。
試してみてください。
=ARRAYFORMULA(
　IF(ISBLANK(L19:L),,
　　IF(N18="○○",VLOOKUP(L19:L,'シート1'!I3:J,2,0) &
　　　IF(COUNTIF('シート1'!I3:I,L19:L)<2,,CHAR(10)) &
　　　　IF(COUNTIF('シート1'!I3:I,L19:L)<2,,
　　　　　arrayformula(VLOOKUP(L19:L,INDIRECT("'シート1'!I"&arrayformula(MATCH(L19,'シート1'!I:I,0))+1&":J"),2,FALSE))),
　　　　　　IF(N18="××",VLOOKUP(L19:L,'シート1）'!L2:M,2,0),
　　　　)
　　　)
　　)
　)