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.

 

[ttray33y]

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.

from what you are saying it is very doable but unless you show us sample data or how your spreadsheet looks like, we can't help you.

 

[Bones55]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142, align: center"]AC_Region_Deskside[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141, align: center"]AC_Region_Engineering[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl66, width: 115, align: center"]AC_Region_Security[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl66, width: 114, align: center"]AC_Telecom[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl66, width: 138, align: center"]Accounting_Systems[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Alphonso Johns[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Barry Pullman[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Andre Giant[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl66, width: 115"]Anabelle Linder[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl66, width: 114"]Barbara Masters[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Anna Smith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Carlos Perez[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Charles Power[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl66, width: 115"]David West[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl66, width: 114"]Joshua Everbridge[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Chin Lee[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Eduardo Guzman[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Fancia Say[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl66, width: 115"]Joshua Everbridge[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl66, width: 114"]Michele McEber[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Grant Linda[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Elvis Rocks[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Harry Brook[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl66, width: 115"]Michele McEber[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl66, width: 114"]Thomas Hunter[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Jaycee Smith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Jair Car[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Jeffie Benford[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl66, width: 115"]Thomas Hefner[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Jose Moral[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Jeffrey Vans[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Jon Michael Vince[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl66, width: 115"]William Person[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Joshua Linseed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]John Garvey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Joshua Everbridge[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Martin Roberts[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Joshua Beech[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Michele McEber[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Michele McEber[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Joshua Everbridge[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Richard Keifer Sullivan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Nicholas Winter[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Lewis Bonafella[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Ross Wheat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Teresa Lynch[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Michele McEber[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Thanh Phenn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Thomas Hunter[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Paul Ridder[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Thomas Hefner[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]Toshia Mori[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Renee Roberson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]Walter Coxboxer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Robert Bowers[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141"]William Person[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Thomas Hunter[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]Vincent Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl66, width: 142"]William Finland[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Let A1:E18 house the data, headers included.

Let A21 house a name of interest like Joshua Everbridge (This can even be a selection from a dropdown list.)

In A22 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$1:$E$1,SMALL(IF($A$2:$E$18=A$21,COLUMN($A$1:$E$1)-COLUMN(INDEX($A$1:$E$1,1,1))+1),ROWS($1:1))),"")

 

[Bones55]

That gets me halfway there, is there anyway to search only by First name and find all those users say with the first name like Joshua and instead of what column the person resides in the actual name of all the groups "Joshua" resides in?

Right now i enter First name nothing happens, but if I enter the whole name it give me what column their reside in which I can use for another project.

thank you

 

[Bones55]

Disregard last message, it does return the group...but is there anyway to search only by First name and find all those users say with the first name like Joshua and show all the groups "Joshua" belongs too?

 

[Aladin Akyurek]

Disregard last message, it does return the group...but is there anyway to search only by First name and find all those users say with the first name like Joshua and show all the groups "Joshua" belongs too?

A21 = Joshua

In A22 control+shift+enter and copy down:

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

 

[Bones55]

That worked great, thank you very much...

 

[Aladin Akyurek]

That worked great, thank you very much...

You are welcome.

 

[Bones55]

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?