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. Commented 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. Commented Jun 6, 2022 at 17:35

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.