Searching for multiple words

[jamesalot]

I'm making a sheet that organizes from master sheet to 3 seperate sheets. I know how to search and pull from one word but not multiple words and thats what i cant seem to find.

=ArrayFormula(IFERROR(INDEX(Master!$A$1:$A$298, SMALL(IF("BOARDMAN"=Master!$D$1:$D$298, ROW(Master!$A$1:$A$298)-ROW(Master!$A$1)+1), ROW(1:1))),""))

this is my formula that works. but where boardman is i want to have it also search for ECHO, HERMISTON, and a couple others but I can't find how to write that.

Idea is that if on "master" row D1 thru D300 has "x or y or z" then in grid copy the name from corresponding A1-A300 in this square. then next row would say same thing with out copying what is already on the sheet. so currently i can do that for 1 word but i cand find out how to do it with multiple words. i was trying IF(OR but i think i was writing it all wrong if someone could help me out

 

[RoryA]

I suggest you post a link to a sample file so helpers don't have to recreate all your data for testing. For Sheets, you can almost certainly use a much simpler FILTER formula.