Q:

Wildcard in Array Formula

I have searched through plenty of topics about this, but I am afraid I am not understanding enough of each portion of the code to adapt them to my specific issue.  I have the formula below...
=IF(ISERROR(INDEX('Master Schedule'!$A$1:$E$500,SMALL(IF('Master Schedule'!$C$1:$C$500="home game",ROW($C$1:$C$500)),ROW(1:1)),1)),"",INDEX('Master Schedule'!$A$1:$E$500,SMALL(IF('Master Schedule'!$C$1:$C$500="home game",ROW('Master Schedule'!$C$1:$C$500)),ROW(1:1)),1))
Full disclosure, this is a line of code that I found online while trying to determine if there was a way to search and return values where the referenced columns aren't in order like VLOOKUP requires.  I understood enough of what it was doing to adapt it to my specific spreadsheet.  This formula is returning information from the Master Schedule sheet that are labled as home game in column C.  I want to create a formula that will grab information that contains any type of game in that same column (home game, away game).  The Master Schedule shows all types of games, scrimmages, practices, and other events.  The separated sheets are showing only portions of information grabbed from that Master Schedule sheet.  Can anyone help me on how to modify this line of code so it will search for any type of game (basically ignoring "home" or "away")?

A:

Try this:
=INDEX('Master Schedule'!$A$1:$E$500,SMALL(IF(ISNUMBER(SEARCH("game",'Master Schedule'!$C$1:$C$500)),ROW($C$1:$C$500)),ROW(1:1)),1)

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

