2 inputs to give range of returns

In C6, entered as an array (use CNTRL SHFT ENTER instead of a simple ENTER)
=IF(ROW(A1)>$B$4,"",INDEX(Sheet1!$C$2:$C$6,SMALL( IF(ISNUMBER(SEARCH(Sheet2!$B$2,Sheet1!$C$2:$C$6)),ROW(Sheet1!$C$2:$C$6)-1,10000),ROW(A1)))), dragged down

In B6
=IFERROR(INDEX(Sheet1!$B$2:$B$6,MATCH(C6,Sheet1!$C$2:$C$6,0)),"")
Does that work for you?

True, you do not have to set it up again each time you open the file. What it does is tells Excel that you are working with an array and to assign memory so that Excel can set up values within that array. If you have a lot of calculations and large arrays, it can slow down your spreadsheet calculations so always use judiciously.

Okay
=IF(ROW(A1)>$B$4,"",INDEX(Sheet1!$C$2:$C$6,SMALL( IF(ISNUMBER(SEARCH(Sheet2!$B$2,Sheet1!$C$2:$C$6)),ROW(Sheet1!$C$2:$C$6)-1,10000),ROW(A1))))
Working from the inside out,

IF(ISNUMBER(SEARCH(Sheet2!$B$2,Sheet1!$C$2:$C$6)),ROW(Sheet1!$C$2:$C$6)-1,10000)

What this does, is supply an array of numbers to be used by the SMALL function in the next step (this step is why we need to create the formula as an array)
For C2 through C6, it looks for (in your example " ZA" in each of these cells. If it find it, it'll return a number, if not, it returns an error

Then ISNUMBER converts these to True or False
So in your example, we get {False, True, True, False, False}. Excel is also evaluating the second half of that formula ROW(C2:C6)-1 which evaluates to { 2-1, 3-1,4-1, 5-1, 6-1} or {1,2,3,4,5}

Now we have
IF({False, True, True, False, False}, {1,2,3,4,5}, 10,000) So a False gives us 10000 and a true will be matched up to the corresponding number (or row in the series)
={10000,2,3,10000,10000}

So the answer to your first question, "why the -1" is to get a series that starts with 1. If the your data started in row 12, you would have a "-11"

Next step
SMALL( IF(ISNUMBER(SEARCH(Sheet2!$B$2,Sheet1!$C$2:$C$6)),ROW(Sheet1!$C$2:$C$6)-1,10000),ROW(A1)))
substituting what we just got
SMALL({10000,2,3,10000,10000}, ROW(A1)) becomes SMALL({10000,2,3,10000,10000}, 1) meaning find the 1st smallest value in that array. As you copy this down, you'll have ROW(A2), ROW(A3) or 2nd smallest and 3rd smallest

So Excel returns "2" (Q2, we use 10,0000 to get large numbers for all the values that don't match your search criteria. If your rows are in the 10s of thousands, you'd need to pick a bigger number)

And now we're left with the simple equation
= INDEX(Sheet1!$C$2:$C$6,2) which returns the value in C3
Make sense?