I'm trying to find a formula that will fill in two columns with data from another sheet based on values from a dropdown that correspond to header rows from the original dataset.
I have Sheet1 that contains my dataset. It's organized by two sets of headers: Groups (B1:M1) and Levels (B2:M2). Under each level are two columns that contain a Course title and Duration (B3:M8). Within these columns there are horizontally-merged cells as well.
In Sheet2, I've used data validation to create a dropdown list for "Groups" and Levels" in cells B1 and B2. There are headers in row 3 for Course and Duration.
My goal is to have the data (excluding blank cells) from Sheet1!B3:M8 populate the Course and Duration columns in Sheet2 based on the Group and Level criteria selected from the dropdown options.
Here is the link to a sample for reference (which includes a "Goal" tab to show what I'm trying to display in Sheet2): https://docs.google.com/spreadsheets/d/1ovLIosV65ISPmTZztWxPVljUo5_QvbZa_kLXyvHNBAs/edit?usp=sharing
I've tried VLOOKUP, HLOOKUP, INDEX, and QUERY formulas but haven't found a combination that works. Appreciate any help anyone can offer!