Search box

[trevolly]

Hi all,

I'm wondering if it possible to have a worksheet (source) in my workbook that will have a long list of contact names in one column and then the contacts phone number in the next column.... then in a cell on a different worksheet apply the ability to type the contacts name and whilst typing this name, excel search the source worksheet and start to list results and their phone number?

I've been searching online but not sure of the function or ability to find any guides..

Thanks all.

 

[RasGhul]

You can return this is as a list, or you can data validate the name results into a Drop down for selection.

It's easier if you convert your source data into an excel table "Contacts", then highlight all names & numbers of the table can give name range "Output"

This will dynamically update as you add new contacts to your source list

Search box_trevolly.xlsx
	A	B	C
1	Last Name	Number	Search
2	Tony Stark	400624323	1
3	Steve Rogers	400624324	1
4	Chris Hemsworth.	400624325	1
5	Natasha Romanoff	400624326	1
6	Bruce Banner	400624327
7	Clint Barton	400624328
8	Josh Brolin.	400624329	1
9	Scott Lang	400624330	1
10	Robert Downey Jr.	400624331
11	Chris Evans.	400624332	1
12	Scarlett Johansson.	400624333	1
13	Mark Ruffalo.	400624334
14	Jeremy Renner.	400624335
15	Paul Rudd.	400624336
Source

Cell Formulas
Range	Formula
C2:C15	C2	=IF(ISNUMBER(SEARCH(Search!$C$3,[@[Last Name]])),1,"")

Named Ranges
Name	Refers To	Cells
Output	=Contacts[[Last Name]:[Number]]	C2

Search box_trevolly.xlsx
	B	C
3	Search	S
4
5	Name	Number
6	Tony Stark	400624323
7	Steve Rogers	400624324
8	Chris Hemsworth.	400624325
9	Natasha Romanoff	400624326
10	Josh Brolin.	400624329
11	Scott Lang	400624330
12	Chris Evans.	400624332
13	Scarlett Johansson.	400624333
Search

Cell Formulas
Range	Formula
B6:C13	B6	=FILTER(Output,Contacts[Search]=1)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Output	=Contacts[[Last Name]:[Number]]	B6:C13

 

[trevolly]

Thanks for your help!

 

[RasGhul]

Glad we could help