Search row for array match of a specific word, and return the dates from column A that match

Question

Objective:

• Looking up Class ID (ignoring anything past the **), and return corresponding date in (B2:B).
• Then Looking up Class Event, and return the corresponding date in (C2:C).

I have tried combinations of HLookup, VLookup, Index & Match, and Query, but cannot seem to get it to work correctly.

My Sheet:

Column |      A       |        B?       |          C?        |     D      |      E      |      F      |
Row1   | [Class ID's] | [Class ID Date] | [Class Event Date] |  [Dates]   |   [Name1]   |   [Name2]   |
Row2   |  Class ID1   |    01/02/2021   |     01/04/2021     | 01/01/2021 |             | Class ID3** |
Row3   |  Class ID2   |    01/08/2021   |     01/09/2021     | 01/02/2021 | Class ID1** |             |
Row4   |  Class ID3   |    01/01/2021   |     01/07/2021     | 01/03/2021 |             | Class ID4** |
Row5   |  Class ID4   |    01/03/2021   |     01/09/2021     | 01/04/2021 | Class Event |             |
Row6   |  Class ID5   | * Formula #1 *  |   * Formula #2 *   | 01/05/2021 |             |             |
Row7   |  Class ID6   |                 |                    | 01/06/2021 |             |             |
Row8   |  Class ID7   |                 |                    | 01/07/2021 |             | Class Event |
Row9   |  Class ID8   |                 |                    | 01/08/2021 | Class ID2** |             |
Row10  |  Class ID9   |                 |                    | 01/09/2021 | Class Event | Class Event |
Row11  |  Class ID10  |                 |                    | 01/10/2021 |             |             |

Formula #1 (Column: B)

• Find the class ID, and returning the dates into range: B2:B (Working, but not efficient)
  =IFERROR(INDEX($D$2:$D,MATCH($A2,E$2:E,0)),INDEX($D$2:$D,MATCH($A2,F$2:F,0)))
  ...and so on, for each column (There are 60 columns).

Formula #2 (Column: C)

• Find class ID, search column, find first instance of "Class Event", return date into range: C2:C
  ="I have absolutely no clue for this one"

Is this even possible in Google Sheets?
I can use excel if needed (but preferably not as this sheet pulls data from another Google Sheet)

Answer 1

C2:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B, {F2:F, E2:E; G2:G, E2:E}, 2, 0)))

D2:

as for now, how you stated the dataset, there is nothing to pair events with specific IDs - it would be possible if you would have

Class ID4 Event

instead of just

Class Event

---

update 1:

C2 for 60 columns would be:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B, 
 SPLIT(FLATTEN(IF(F2:BN="",,F2:BN&"×"&E2:E)), "×"), 2, 0)))

update 2:

try in D2 (but it will work only if class event will follow after each id class)

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B, {
 QUERY(SPLIT(FLATTEN(IF(TRANSPOSE(F2:BN)="",,
 TRANSPOSE(F2:BN)&"×"&TRANSPOSE(E2:E))), "×"), 
 "select Col1 where Col2 is not null", 0), {
 QUERY(QUERY(SPLIT(FLATTEN(IF(TRANSPOSE(F2:BN)="",,
 TRANSPOSE(F2:BN)&"×"&TRANSPOSE(E2:E))), "×"), 
 "select Col2 where Col2 is not null", 0), 
 "offset 1", 0); ""}}, 2, 0)))