If Cell Matches, Return Multiple Rows

[mikec82]

I'm working on a project that has two sheets. The first is what gets filled in manually, called "Sheet1", and looks like this:

Sport	What period(s) will they miss?	What day(s) will they miss?
Football	A Period, B Period	June 30th, 2016
Soccer	A Period, C Period, D Period	July 1st, 2016

<tbody>
</tbody>

"Sheet2" is the raw data, and will actually be hidden. It looks like this:

Name	Sport	A Period	B Period	C Period
John Smith	Football	English	Math	History
Matt Roberts	Soccer	Math	English	History

<tbody>
</tbody>

The process I'm working on is for a sports coach to fill out Sheet1, telling me when his team will be absent. Once that is populated, I'd like to add a third Sheet that takes everyone in Sheet2 who plays that sport, and returns their name, sport, the day the will miss, and what class they have for the periods they will miss.

Let's take the example that the football team is going to miss A Period and B Period on June 30th. Here's what I would like Sheet3 to look like:

Name	Sport	What day(s) will they miss?	A Period	B Period	C Period
John Smith	Football	June 30th, 2016	English	Math
Clayton Clark	Football	June 30th, 2016	Math	Science
Ryan Austin	Football	June 30th, 2016	Science	History

<tbody>
</tbody>

I've got the formula below to work for Sheet3!A2, but it only works for one person. I would need it to populate more than one line, if that makes sense. Maybe I'm not using the correct functions?

=IF(ISBLANK(Sheet1!A2),"",INDEX(Sheet2!A:A,MATCH(Sheet1!A2,Sheet2!B:B,0)))

 

[cerfani]

why are you writing a formula in A2 on sheet 3... cant you just copy the first two columns of sheet2 to sheet3?

 

[mikec82]

In Sheet3!A1, I'm wanting to return the name of the first person who plays football, according to Sheet2!B:B. In Sheet3!A2, I'm wanting the 2nd person who plays football, and so on. The coach is only inputting the team who is missing. I'm wanting to find all the people on that team in Sheet2 and put it into Sheet3.

 

[cerfani]

so basically sheet3 is only for 1 sport? Do you just hardcode "Football" into column B on sheet3?

 

[mikec82]

Sheet3 will be for multiple sports, and the goal is to have nothing hard-coded. I found this article, which sounds like what I'm looking for perhaps. It's taking some time to figure out what the formula they use does and how to convert it to my sheet, but this looked promising:

How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP

 

[cerfani]

yeah that link looks like what you need becase essentially you need to build lists in memory. But due to the environment of writing Excel formulas, it can get complicated. I would just write a subroutine and take advantage of vba collection objects. Macros are usually the easier way to get stuff done.

 

[mikec82]

Thank you for the feedback. In this case, I'm working in Google Sheets, so that is unfortunately not an option.

This array formula below works, but it doesn't return more results when I pull the formula down. I have about 20 football players in Sheet2, so I was hoping it would show all of their names.

=ArrayFormula(IF(ISERROR(INDEX(Sheet2!D:E,SMALL(IF(Sheet2!D:E=Sheet1!$B2,ROW(Sheet2!D:E)),ROW(1:1)),2)),"",INDEX(Sheet2!D:E,SMALL(IF(Sheet2!D:E=Sheet1!$B2,ROW(Sheet2!D:E)),ROW(1:1)),2)))

 

[cerfani]

that's why i laughed at my IT department when they suggested we use google docs instead of office

 

[mikec82]

Ha - understood (I obviously feel your pain)!

I don't know if it would help, but put some fake data in a copy of the spreadsheet, available here:

https://docs.google.com/spreadsheets/d/1TN1Bw8LXDWkCAh5MmveGhhL8JfCpSeo79G4QuNsYh4o/edit?usp=sharing

Sheet3!A2 is what I'm working on.

 

[cerfani]

if it was me I would look into the functionality of google app script...

https://developers.google.com/apps-script/

so you can automate stuff on google sheets, id honestly rather learn that than learn how to write the formula in excel

Other people on here are better suited to help you with that formula. I always resort to vba.