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)?
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)?
