How to search by name and return what groups they reside in

[Bones55]

I have a spreadsheet with 20 different groups and each group has people assigned to them.


I would like to be able to search by typing their first name in a search box and have it return what group or groups they resides with. Any help would be appreciated.

 

[Aladin Akyurek]

Aladin,

How difficult would it be to place the formula on say sheet1 to extract the information from data on sheet3 and using Joshua as an example when the formula finds all the Joshua's on Sheet3 to show their full name and what group they belong too?

Book1
	A	B	C	D	E
1	AC_Region_Deskside	AC_Region_Engineering	AC_Region_Security	AC_Telecom	Accounting_Systems
2	Alphonso Johns	Barry Pullman	Andre Giant	Anabelle Linder	Barbara Masters
3	Anna Smith	Carlos Perez	Charles Power	David West	Joshua Everbridge
4	Chin Lee	Eduardo Guzman	Fancia Say	Joshua Everbridge	Michele McEber
5	Grant Linda	Elvis Rocks	Harry Brook	Michele McEber	Thomas Hunter
6	Jaycee Smith	Jair Car	Jeffie Benford	Thomas Hefner
7	Jose Moral	Jeffrey Vans	Jon Michael Vince	William Person
8	Joshua Linseed	John Garvey	Joshua Everbridge
9	Martin Roberts	Joshua Beech	Michele McEber
10	Michele McEber	Joshua Everbridge	Richard Keifer Sullivan
11	Nicholas Winter	Lewis Bonafella	Ross Wheat
12	Teresa Lynch	Michele McEber	Thanh Phenn
13	Thomas Hunter	Paul Ridder	Thomas Hefner
14	Toshia Mori	Renee Roberson	Walter Coxboxer
15		Robert Bowers	William Person
16		Thomas Hunter
17		Vincent Price
18		William Finland
Sheet1

Book1
	A	B
1	Joshua
2	AC_Region_Deskside	Joshua Linseed
3	AC_Region_Engineering	Joshua Beech
4	AC_Region_Engineering	Joshua Everbridge
5	AC_Region_Security	Joshua Everbridge
6	AC_Telecom	Joshua Everbridge
7	Accounting_Systems	Joshua Everbridge
8
Sheet3

In A2 of Sheet3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$A$1:$E$1,SMALL(IF(ISNUMBER(SEARCH("|"&$A$1,"|"&Sheet1!$A$2:$E$18)),COLUMN(Sheet1!$A$1:$E$1)-COLUMN(INDEX(Sheet1!$A$1:$E$1,1,1))+1),ROWS(Sheet1!$1:1))),"")

In B2 of Sheet3 control+shift+enter, not just enter, and copy down:

=IF($A2="","",INDEX(Sheet1!$A$2:$E$18,SMALL(IF(ISNUMBER(SEARCH($A$1,INDEX(Sheet1!$A$2:$E$18,0,MATCH($A2,Sheet1!$A$1:$E$1,0)))),ROW(Sheet1!$A$2:$E$18)-ROW(Sheet1!$A$2)+1),COUNTIFS($A$2:A2,A2)),MATCH($A2,Sheet1!$A$1:$E$1,0)))

 

[Bones55]

First let me say you’re fantastic, your formulas worked great…I’m trying to adapt it to my second spreadsheet – the first formula is fine…I change the sheet name and where it searches to L122 and it works great…no issues, now when I change the second formula I’m getting #N/A or #Value ! I can get the formula to work if it’s in Column A and B, but not in K and L

L122 has the search box i.e. Joshua, L123 has this code and is working as expected.

=IFERROR(INDEX('Data Do not touch'!$B$2:$CW$2,SMALL(IF(ISNUMBER(SEARCH("|"&$L$122,"|"&'Data Do not touch'!$B$2:$CW$54)),COLUMN('Data Do not touch'!$B$2:$CW$2)-COLUMN(INDEX('Data Do not touch'!$B$2:$CW$2,1,1))+1),ROWS('Data Do not touch'!$1:1))),"")

K123 I’ve entered this formula to look at both L122 and L123 and can’t get it to work…what am I missing?

=IF($L123="","",INDEX('Data Do not touch'!$B$2:$CW$54,SMALL(IF(ISNUMBER(SEARCH($L$122,INDEX('Data Do not touch'!$B$2:$CW$54,0,MATCH($L123,'Data Do not touch'!$B$2:$CW$2,0)))),ROW('Data Do not touch'!$B$2:$CW$54)-ROW('Data Do not touch'!$L$123)+1),COUNTIFS($L$123:L123,L123)),MATCH($L122,'Data Do not touch'!$B$2:$CW$2,0)))

 

[Aladin Akyurek]

Assuming that the data headers are in $B$1:$CW$1 of Data Do not touch...

L123 >>

=IFERROR(INDEX('Data Do not touch'!$B$1:$CW$1,SMALL(IF(ISNUMBER(SEARCH("|"&$L$122,"|"&'Data Do not touch'!$B$2:$CW$54)),COLUMN('Data Do not touch'!$B$1:$CW$1)-COLUMN(INDEX('Data Do not touch'!$B$1:$CW$1,1,1))+1),ROWS('Data Do not touch'!$1:1))),"")

K123 >>

=IF($L123="","",INDEX('Data Do not touch'!$B$2:$CW$54,SMALL(IF(ISNUMBER(SEARCH($L$122,INDEX('Data Do not touch'!$B$2:$CW$54,0,MATCH($L123,'Data Do not touch'!$B$1:$CW$1,0)))),ROW('Data Do not touch'!$B$2:$CW$54)-ROW('Data Do not touch'!$B$2)+1),COUNTIFS($L$123:L123,L123)),MATCH($L123,'Data Do not touch'!$B$1:$CW$1,0)))

You need to confirm these formulas with control+shift+enter...

 

[Bones55]

Again thank you...my data headers are not on the top row, but looking at that I was able to change $B$1:$CW$1 to $B$2:$CW$2 and this allowed the formula to work... Your expertise has been outstanding and I couldn't have accomplished this without your assistance...thank you once again.

 

[Aladin Akyurek]

Glad to help.