0

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!

Improve this question
1
  • Hi @Karyssa! Take the tour here. Looking at your sheet, it uses a lot of merged cells and from personal experience, those don't play well with functions or almost anything to be honest. I might still be able to figure something out though, as long as your actual data stays consistent.
    – David Tan
    Jun 4, 2022 at 3:06

1 Answer 1

Reset to default
0

Link to my sheet.

So firstly we make a helper table to reference for the next step. This uses the MATCH function to find the where Group X is. Using the OFFSET function, we offset 1 down and y amount to the right (y being the number from MATCH.) We also have to add a width and a height to that.

We now reference this helper table for the level. Same idea, MATCH then OFFSET, this time 2 down. We then use QUERY with the 'skipping' clause. We query it again to remove all blank spaces.

Formula for helper table:

=OFFSET(Sheet1!A1,1,MATCH(B1,Sheet1!1:1,0) -1,10,6)

Formula for final:

=QUERY(
    QUERY(
        OFFSET(
            E3,
            2, 
            MATCH(B2,E3:J3,0)-1,
            6,2),
        "SELECT * SKIPPING 2",0),
    "SELECT * WHERE Col1 IS NOT NULL",0)

P.S. You can hide the helper table, it won't affect anything. You can see this in my GOAL sheet.

Improve this answer
1
  • Thank you so much, David! This is exactly what I needed to get started. I appreciate the time you took to explain the answer.
    – Karyssa
    Jun 6, 2022 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.