スプレッドシートで別シートをもとに組み合わせの可否を求めたい

Question

スプレッドシートでシートを参照して組み合わせの可否を求めたいのですが、どのように関数を組めば良いか分からず質問させていただきました。
口頭の説明が難しいため以下の表をまずご覧ください。

シート1

	A	B	C	D	E
a
b
c
d
e

シート2

A	B	C	D	E	a	b	c	d	e
2022/06/10	2022/04/10	2022/05/14	2022/06/25	2022/04/30	2022/04/09	2022/09/17	2022/05/21	2022/06/04	2022/05/05
2022/05/07	2022/06/11	2022/09/17		2022/06/04	2022/07/16	2022/07/17	2022/05/14	2022/08/06	2022/07/09
2022/06/04	2022/07/15	2022/07/01		2022/08/08	2022/07/09

シート1希望結果

	A	B	C	D	E
a
b			×
c			×
d	×				×
e

上記のようにA〜Eの日付の記録とa〜eの日付の記録をもとに同じ日付があった場合にバツを入れたいのですが、方法が全く思い浮かばず、検索ワードすら浮かばない状態です。
方法でなくとも検索ワードや必要な関数だけでも良いのでご教授いただけますと幸いです。

また、関数のみで行え場合はGASを使用することも視野に入れていますが、いずれにしても方法はおろか検索ワードすら分からず、GASが必須の場合も解決の手かがりだけでも教えていただけますと助かります。

質問内容に不備や、不足がございましたら申し訳ないのですがどうかご指摘ください。

Comments

[sawa]

希望結果の方、b-C や d-E には×が入らないんですか？

[teratailuser123]

ご指摘ありがとうございます。
見落としていました。
申し訳ございません。

[sawa]

修正ありがとうございます。
関数でやる場合の回答を入れておきました。

Answer 1

最近使えるようになった LAMBDAを使った式を。シート2の適当なセルに以下で。

=LAMBDA(target1,target2,MAKEARRAY(COLUMNS(target2)+1,COLUMNS(target1)+1,LAMBDA(r,c,IFS(AND(r=1,C=1),"",r=1,INDEX(target1,r,c-1),c=1,INDEX(target2,c,r-1),true,IF(COUNTA(INDEX(target1,,c-1),INDEX(target2,,r-1))>COUNTUNIQUE(INDEX(target1,,c-1),INDEX(target2,,r-1)),"×",)))))('シート2'!A1:E4,'シート2'!F1:J4)

↓インデントつけて見やすくしたバージョン

  =LAMBDA(target1,target2,
    MAKEARRAY(
      COLUMNS(target2)+1,COLUMNS(target1)+1,
      LAMBDA(
        r,c,
        IFS(
          AND(r=1,C=1),"",
          r=1,INDEX(target1,r,c-1),
          c=1,INDEX(target2,c,r-1),
          true,
          IF(
            COUNTA(INDEX(target1,,c-1),INDEX(target2,,r-1))
            >COUNTUNIQUE(INDEX(target1,,c-1),INDEX(target2,,r-1)),
            "×",
          )
        )
      )
    )
  )
  ('シート2'!A1:E4,'シート2'!F1:J4)

Comments

[teratailuser123]

ご回答誠にありがとうございます！
LAMBDAは初めて知りました…
また、関数を作ってくださりありがとうございます泣

インシデントを見ましたが、非常にまとめられてて応用も効きやすく素晴らしいです…
この度は誠にありがとうございました！

[sawa]

書き忘れたので補足ですが、重複あるなし を COUNTA() とCOUNTUNIQUE() の比較で処理してます。

もし元データの同じ列内に同じ日付がある（例えば Aの下に 2022/06/10 が2つ以上ある等の）場合は、この方法だと正しい回答が得られません。FILTERを使った重複判定処理に変更する必要があります。
さらに式が長くなるので避けましたが。

Answer 2

効率等は全く考えていませんが、
仕組みとしては

\	A	B
a	1	6
b	2	7
c	3	8
d	4	9
e	5	10

というの順番で確認していき、
チェックのやり方はそれぞれのデータの配列を比較していくのが無難でしょうかね...

const A = ["2022/06/10","2022/05/07","2022/06/04"];
const a = ["2022/04/09","2022/07/16","2022/07/09"];
// 省きます
const d = ["2022/06/04","2022/08/06"];

const isAa = A.some((v)=>a.includes(v)); // false
// 省きます
const isAd = A.some((v)=>d.includes(v)); // true

というような形で取得できます
もちろんスプレッドシートから取得する必要があるので
上記のように一つ一つ変数に入れてチェックというのはあまり効率的ではないので、

const smallData = [
  ["2022/06/10","2022/05/07","2022/06/04"], // aのデータ配列
  ["bのデータ配列"],
  ["cのデータ配列"],
  ["dのデータ配列"],
  ["eのデータ配列"],
];
const AColumnResult = smallData.map((smallValues)=>A.some((v)=>smallValues.includes(v)));
// 多分こんな感じのデータになる
// [false,false,false,true,false]

というような形に直した上で、setValuesを使ってまとめてチェックボックスの値を入れてあげれば良いかと思います。

\	A
a	false
b	false
c	false
d	true
e	false

これをAのデータのみならずA~Eをmap等を用いてやれるようになれば良いかと思います。

setValuesの仕組み上、column単位ではなくrow単位でやったほうが良いかもしれません
下記のようなデータにすれば一回のsetValuesで済むためです。（column単位でやると転値するか1列毎にデータを挿入することになると思うので）

[
  [false,false,false,false,false],
  [false,false,false,false,false],
  [false,false,true,false,false],
  [true,false,false,false,false],
  [false,false,false,false,false],
]

Comments

[teratailuser123]

この度は回答いただき誠にありがとうございます！
なるべくスプレッドシート上で完結させたかったためsawaさんの回答をベストアンサーにいたしましたが、こちらも大変参考になりました。
いろんなアプローチがあってすごいなぁと思いつつ自分も一層勉強していこうと思いました！