yogi_Search For Names In Another Sheet And Pull Different Columns Associated With Searched Names

                                         Google Spreadsheet   Post  #1627
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-09-2014
post by: Erick Nyaga (https://productforums.google.com/forum/#!mydiscussions/docs/PnYHiAzmUQc)
Apply arrayformula to formula with filter and search functions

Greetings,

I'm using the function below to check a "Key" cell on the same row on Sheet1, search for a match of the contents in the "Key" cell on an entire column on another Sheet2. If found, the formula is able to look across the row where found on Sheet2 and select an item of information to return to Sheet1.

The problem I have is the formula seems pretty heavy and in my case needs to be copied to at least 500x3 rows to pull up specific data matched to the "Key" cell. 

Is there an arrayformula equivalent that can autoexpand down an entire column and return values only matched to the "Key" cell row in Sheet1, that are found on Sheet2?

The logic is that a user can resolve an issue by including the "Key" in a specific cell that the formula matches to the issue to provide selected info.

=index(if(isblank(A9),"", iferror(filter('Resolution Listing'!$B$2:$B,search($A9,'Resolution Listing'!$AF$2:$AF)),"UNRESOLVED")),1)

---

Hi Yogi.

I've introduced some additional dummy data. Please have a look and advise.

Many thanks for your help.

E

----------------------------------------------------------------------------------------------------------------------------------------------------------------