Which formula to use?

[Michael Timpano]

Stuck on which formula would work best in the following scenario; 

sheet 1 = column1 lists codes (eg. ADA1O)

sheet 2 = column1 lists codes with sections (eg. ADA1O.1, ADA1O.2) that fall in period 1

sheet 1 = column2 results

I want to populate the column Period 1 in sheet 1 that lists all the courses in column A from sheet 2 with the codes listed under the Period 1 column from sheet 2 without adding any blank rows.

I don't get the results I am after when trying different formulas like xlookup, if, vlookup etc.

SAMPLE SPREADSHEET 

Thanks for any suggestions. Take care.

[Alexander Ivanov]

Hi!

How about this formula

=ARRAYFORMULA(
  IFERROR(
    VLOOKUP(
      Sheet1!B1:E1&"_"&Sheet1!A2:A9,
      {FLATTEN(Sheet2!A1:D1&"_"&LEFT(Sheet2!A2:D10,5)),FLATTEN(IF(COLUMN(Sheet2!A1:D1),"","")&Sheet2!A2:D10)},
      2,
    ),
  )
)

It's not clean but It may be flexible for this task.

Also check my sample for this https://docs.google.com/spreadsheets/d/1G-rIT9r7KwLEnMArPnZyaAw5ifq1CdSgHuK4r1QTioQ/edit?usp=sharing

Cheers, Alex.