エクセル ある値をもとに指定したセルに〇を入力する方法を教えて下さい

Question

###sheet2をもとにsheet1の表に〇をつけたい

<sheet1> |||列①|列②|| |:--|:--:|--:| |||会場①|会場②| |||日付①|日付②| |所属|氏名①||| |所属|氏名②||| |所属|氏名③||| |...|...|...| <sheet2> |列1|列2|列3| |:--|:--:|--:| |日付①|日付②|| |会場①|会場②|| |所属Ａ|所属Ｃ|| |氏名①|...|| |氏名②|...|| |氏名③|...|| |所属Ｂ|...|| |氏名④|...|| |氏名⑤|...|| |…|...||

↑<sheet2>にある、縦列の日付、会場、所属、氏名が<sheet1>で合致しているセルに「〇」が打ち込まれる数式等あればご教授ください。
(Ex) <sheet2> 日付①→4月14日、会場①→日本、所属Ａ→アジア、氏名①→岡田
<sheet1> 氏名①→岡田　右横のセルに「〇」

試したこと

VLOOKUP等、試しましたが上手く引っ張れる方法が分かりません。

Answer 1

前提として、データの検索を行いたい場合は、データの形式も相応の形でないと検索しにくいです。

あまり表の形を変えないように考えてみますが、今の形式だとちょっと扱いにくいです。

---

考え方

1. 会場・日付の列を探す
2. 見つかった列から該当の氏名・所属を探す
3. 見つかったら○を入れる

会場・日付の列を探す

まずは、sheet2から会場・日付の列を探します。

MATCH(検索値（会場・日付）, 検索範囲（sheet2）, 0)
→見つかった列番号

なお、今の表の形式では会場や日付に被りがある場合上手く検索できません。
その場合はsheet2に一行挿入して日付と会場を連結した行を用意し、挿入した行で検索します。

列1	列2
日付①会場①	日付②会場②	←日付と会場を連結（検索範囲）
日付①	日付②
会場①	会場②
所属Ａ	所属Ｃ
氏名①	...
氏名②	...

見つかった列から該当の氏名・所属を探す

氏名・所属の検索自体は、「会場・日付の列を探す」と同じパターン、縦横が逆になっただけです。

MATCH(検索値（氏名・所属）, 検索範囲, 0)

問題となるのは検索範囲ですが、OFFSET関数と「会場・日付の列を探す」で見つかった列番号を使うと以下のように求められます。

OFFSET(sheet2の列1, 0, 1のMATCHの結果-1)
→sheet2の列1から「1のMATCHの結果-1」列右にずれた範囲

ここまでをまとめると以下のようになります。

MATCH(氏名・所属,
  OFFSET(sheet2の列1, 0,
    MATCH(会場・日付 , 検索範囲, 0) - 1),
  0)

ここで出てくる結果は該当者がいれば見つかった行番号、見つからなければエラー値になります。

見つかったら○を入れる

上記の式で見つかった場合は数字になりますので

=IF(ISNUMBER(上の式), "○", "")

としてあげれば見つかったセルに○が入ります。