Filter a range and then partially concatenate results.

[TonyD1016]

I've been banging head against the wall for almost a day now trying to figure out if this is possible.

I have a sheet that contains a directory of employee contact information arranged in columns of Last Name, First Name, Department, Job Title, Email, Cell Phone, Office Extension and a couple other columns of internal data.

I have so far come up with the following formula:

=INDEX(FILTER(Directory,ISNUMBER(SEARCH($B1,JobTitle)),"no results"),SEQUENCE(ROWS(FILTER(Directory,ISNUMBER(SEARCH($B1,JobTitle))))),{2,1,7,6,5})

Where B1 is an empty cell on another sheet to type a job title. This formula returns a spill array of every entry in the directory with with a certain job title (I.E. "Supervisor") in the order of First Name, Last Name, Extension number, Cell phone, and Email in adjacent cells in a row.

What I am trying to accomplish is to have the first and last name and office extension concatenated into a single cell, and the email and cell phone be left in their own individual cells.
Is there any way to make that happen? Preferably without macros (although I will accept them if necessary)?

 

[Fluff]

Hi & welcome to MrExcel.
Do you have the LET function?
Also are you looking for exact matches in Job Title, or partial matches?

 

[TonyD1016]

Hi & welcome to MrExcel.
Do you have the LET function?
Also are you looking for exact matches in Job Title, or partial matches?

Hello! Thank you, I am happy to be here.

I do have the LET function, and I am looking for partial matches in the Job Title, as day and night shift supervisors exist and other such conditions exist and I am looking to return all such employees. The formula does partial matches and returns exactly the data I want it to. I just can't figure out how to arrange and display it like I want.

 

[TonyD1016]

To clarify, I've defined the columns containing the data by their header name (FirstName, LastName, CellPhone, and so on) and I defined Directory as a range containing every column on the sheet

 

[Fluff]

Ok, how about

=LET(Fltr,FILTER(Directory,ISNUMBER(SEARCH($B1,JobTitle)),"no results"),Ary,INDEX(Fltr,SEQUENCE(ROWS(Fltr)),{2,1,7,6,5}),CHOOSE({1,2,3},INDEX(Ary,,1)&" "&INDEX(Ary,,2)&", "&INDEX(Ary,,3),INDEX(Ary,,4),INDEX(Ary,,5)))

 

[Fluff]

A slightly better version to get rid of errors, if nothing is found

=LET(Fltr,FILTER(Directory,ISNUMBER(SEARCH($B1,JobTitle)),""),Ary,INDEX(Fltr,SEQUENCE(ROWS(Fltr)),{2,1,7,6,5}),IF(COUNTA(Fltr)=1,"No Results",CHOOSE({1,2,3},INDEX(Ary,,1)&" "&INDEX(Ary,,2)&", "&INDEX(Ary,,3),INDEX(Ary,,4),INDEX(Ary,,5))))

 

[TonyD1016]

Ok, how about

=LET(Fltr,FILTER(Directory,ISNUMBER(SEARCH($B1,JobTitle)),"no results"),Ary,INDEX(Fltr,SEQUENCE(ROWS(Fltr)),{2,1,7,6,5}),CHOOSE({1,2,3},INDEX(Ary,,1)&" "&INDEX(Ary,,2)&", "&INDEX(Ary,,3),INDEX(Ary,,4),INDEX(Ary,,5)))

That worked perfect! Thank you so much.
Would it be further possible to use TRANSPOSE to display the email and cell phone vertically beneath the name, essentially making each column a collection of results?

 

[TonyD1016]

I answered my own question at the end. Thank you again for all the help. :D

 

[Fluff]

You're welcome & thanks for the feedback.

 

[TonyD1016]

All right I have a further follow-up question.

wrapping the LET function in TRANSPOSE displays each result as an adjacent column of data. Is it possible to have the results all show up in a single column, one on top of the next?