Constructing a formula to do what advanced filter does

[RCope]

Okay, I will attach my workbook in hopes someone can help me figure this out. What I have is 1 sheet called GD for Game Data. It is a list of every single College Football game to be played in 2012. On the next page I want to have a way to recall this data based on the criteria in a combo box. I have the combo boxes set up, but am having trouble find a formula that recalls the data I want.

On the first combo box furthest to the left, I have the names of the 12 difference Conferences of teams. When a Conference is selected, the teams that belong to that conference appear in the second combo box. When a team is selected in the second box, I would like for their schedule to appear in the outlined box below. I am looking for a function that searches the database of games and returns, the dates that the team plays on. The catch is, in order to shorten my data, I only put in the home schedule for each team. This makes it so that there are two columns to be searched in order to return each game on a teams schedule. I need to be able to have each cell look through both columns and find the teams name, then return the lowest date that is higher than the last cell above it to put them in the correct order.

It seemed much easier when I was conceiving this idea, but I am too far along to turn back now. I know that Advanced Filtering would work great, but it totally defeats the purpose of using the combo boxes. In addition to this, I am not wanting to recall each and every column in my data, or have to manually fill out the advanced filter to see my results. The idea is to be able to give this to my Dad to make it easier on him to look up what games are going to be played. Surely there is a way to accomplish this?

[Richard Buttrey]

Hi,

I can't help thinking a Pivot Table isn't a neater solution.

I've added a simple one here but there's nothing to stop you extending it and dicing and slicing it as you feel appropriate.

Regards

[NBVC]

Try:

=IFERROR(SMALL(IF(GD!$K$2:$N$712=$J$3,GD!$I$2:$L$712),ROWS($J$8:$J8)),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.