Extract Values into Column F if Condition is Met

Looking for formula (non-array preferred) to look into column A in sheet named House, column A and column E in sheet named Here and extract desired values into column F. Values to be extracted into column F is based on the selection in C2 of sheet named House.

See attached sample file.

Example:
If C2= First Name (sheet named House)
Extracted values into column F of sheet named Here would be (since it satisfies the requirement for First Name selection in C2:
Ah, John
Ah, John

If C2= Last Name (sheet named House)
Extracted values into column F of sheet named Here would be (since it satisfies the requirement for First Name selection in C2:
Akon, Oscar
Akon, Oscar
Allan, Ricux
Bankinin, Taco
Bankinin, Taco
Bankinin, Taco
Blue, Sky
Bohn, Luck Anner
Done, Christ
Done, Christ
Done, Christ
Done, Christ
Gonner, James
Hartter, Cross
Hartter, Cross
Hartter, Cross
Karlazo, Ross
Mat, Elalere
Mat, Elalere
McDonald, Mil
McDonald, Mil
McDonald, Mil
McDonald, Mil
Obruko, Han
Stall, Sharp

bjnockle,

I use the attached formula provided by Dave Bruns of Exceljet.

Does what you want without using an Array, or needing to seperate First or Family name, as it looks for any "string" in the list of names matching the Search criteria, whichever type of Name it is.

Col B contains the Names, which are purged of Duplicate First/Family and Family/First pairs.
Put one or more letters - case not an issue - in D5, and if you want you can limit the number of matches to the value you set in D8.
Formula in G5 copied down will find all matches in Names containing the string:

=IFERROR(IF(OR(search="",F5>ct),"",INDEX(Name,AGGREGATE(15,6,(ROW(Name)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,Name)),F5))),"")

If there is no String in D5, Col G remains blank, If the number of matches exceeds any limit you set in D8,the extra rows will be blank.

If you want to add more names to the list, you need to extend the NAME range (or make it dynamic).

Hope this helps

Ochimus