Search Function for Department Directory

[Becca83]

Hello, I am looking for some help around my Company Directory.

I want to be able to have a user friendly search function, which searches my second worksheet to pull up staff info. I've been googling all afternoon, and made a million formula changes, and cant get it to work.

here is my sheet 1
first.JPG

my sheet 2 is where I keep all the raw data
second.JPG

How can I get this to work? I know its possible! I haven't worked with these types of formulas in YEARS, and feel like ive forgotten everything. HELP

Is it evev possible to email the document to a professional and have them code it? at my wits end!

Becca

[Logit]

.
Here is a similar project :

Please Login or Register  to view this content.

[Rigged]

Hello Becca,

Are you only going to be searching based on the first name or can a user input any of the information (name, title, location, etc) and expect the results?

[Becca83]

People here search first names mostly, id say 90% of the time.

[Becca83]

Logit: I'm going to play with what you've sent me this morning!!! I'll let you know how I made out! thank you!!!!!

[63falcondude]

People here search first names mostly, id say 90% of the time.

What should happen if someone searches a first name where there are multiple matches?

If you want a formulaic approach, please attach a small representative sample of your data along with the desired results (manually entered) of the formula/s.

Also, a solution has to work 100% of the time so you need to decide if it should only work for first names or not.

[Becca83]

What should happen if someone searches a first name where there are multiple matches?

If you want a formulaic approach, please attach a small representative sample of your data along with the desired results (manually entered) of the formula/s.

I see the issue, my search box ive built only shows one. What would be the most efficient way?

I thought I was doing something easy, but I'm obviously in over my head

[63falcondude]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Becca83]

This is what I started on and got lost on........I started with my gut, then made the mistake of using other forums that confused me.....and here I am

[63falcondude]

First off, you have trailing spaces in your data. For example, double click on Sheet2 A132. You will see that there is a trailing space at the end of Rebecca.

While we can incorporate something in the formula to account for this, it is better to clean the data.
I don't see any spaces in your column A data so a quick way to remove all spaces would be to select column A > Find & Select > Replace > Find what: type in a single space > Replace with: leave blank > Replace All

I would recommend formatting your data in Sheet2 as a table in case you want to add more people to the data in the future (that way you can refer to the entire table column instead of a static range), but I will write the formulas for your data as is for now.

Assuming that you want to return multiple matches, try these: (I un-merged the columns to account for multiple matches):

C7 =IFERROR(INDEX(LastName,SMALL(IF(FirstName=$C2,ROW(FirstName)-(ROW($A2)-1)),COLUMNS($A1:A1))),"") Ctrl Shift Enter

C8 =IFERROR(INDEX(Title,INDEX(MATCH($C6&C7,FirstName&LastName,0),0)),"")

C9 =IFERROR(INDEX(Location,INDEX(MATCH($C6&C7,FirstName&LastName,0),0))&"","")

C10 =IFERROR(INDEX(Phone,INDEX(MATCH($C6&C7,FirstName&LastName,0),0))&"","")

C11 =IFERROR(INDEX(Ext,INDEX(MATCH($C6&C7,FirstName&LastName,0),0))&"","")

C12 =IFERROR(INDEX(Cell,INDEX(MATCH($C6&C7,FirstName&LastName,0),0))&"","")

Dragging the formulas through column F will allow for up to 4 matches (i.e. 4 people with the same first name). You can drag the formula over as far as you'd like.

See attachment for clarification.

[Logit]

.

Please Login or Register  to view this content.

This format allows you to search for anything: First name, Last name, Phone number, etc. etc. Everything is on one sheet though ...

[Becca83]

First off, you have trailing spaces in your data. For example, double click on Sheet2 A132. You will see that there is a trailing space at the end of Rebecca.

While we can incorporate something in the formula to account for this, it is better to clean the data.
I don't see any spaces in your column A data so a quick way to remove all spaces would be to select column A > Find & Select > Replace > Find what: type in a single space > Replace with: leave blank > Replace All

I would recommend formatting your data in Sheet2 as a table in case you want to add more people to the data in the future (that way you can refer to the entire table column instead of a static range), but I will write the formulas for your data as is for now.

Assuming that you want to return multiple matches, try these: (I un-merged the columns to account for multiple matches):

C7 =IFERROR(INDEX(LastName,SMALL(IF(FirstName=$C2,ROW(FirstName)-(ROW($A2)-1)),COLUMNS($A1:A1))),"") Ctrl Shift Enter

C8 =IFERROR(INDEX(Title,INDEX(MATCH($C6&C7,FirstName&LastName,0),0)),"")

C9 =IFERROR(INDEX(Location,INDEX(MATCH($C6&C7,FirstName&LastName,0),0))&"","")

C10 =IFERROR(INDEX(Phone,INDEX(MATCH($C6&C7,FirstName&LastName,0),0))&"","")

C11 =IFERROR(INDEX(Ext,INDEX(MATCH($C6&C7,FirstName&LastName,0),0))&"","")

C12 =IFERROR(INDEX(Cell,INDEX(MATCH($C6&C7,FirstName&LastName,0),0))&"","")

Dragging the formulas through column F will allow for up to 4 matches (i.e. 4 people with the same first name). You can drag the formula over as far as you'd like.

See attachment for clarification.

MAGIC!!!! Yes!!!! this looks great. and yes I will need the ability to add more and remove some as we grow

[Becca83]

.

Please Login or Register  to view this content.

This format allows you to search for anything: First name, Last name, Phone number, etc. etc. Everything is on one sheet though ...

ANOTHER fantastic option. this is the best forum ever. I will play with these two and see which works best for my needs. you are fantastic!

[63falcondude]

Great, glad that we could help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

If you plan to add and remove people, select A1:G159 > Insert > Table > OK. You can then get rid of your named ranges and adjust the formulas to refer to the entire table columns instead of the named range.

[Becca83]

Great, glad that we could help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

If you plan to add and remove people, select A1:G159 > Insert > Table > OK. You can then get rid of your named ranges and adjust the formulas to refer to the entire table columns instead of the named range.

In my name manager?
Capture.JPG

so I sould change the G2-G159 to just, column G?

[63falcondude]

Those are the names that I am referring to.

If the only thing that you use them for was for the formulas in Sheet1, you can delete them since they will no longer be needed.

Then, after making the data into a table, change the formula from "Last Name" to "Table1[Last Name]", Cell to "Table1[Cell Number]", etc.

Just keep in mind that copying the formulas across may change the column references since there isn't a way to make a table reference absolute.

See attachment.

[Becca83]

Made it into a table, cant figure out renaming formula. I tried and it was inputting names?

[63falcondude]

I edited post #16 with an attachment of how the formulas should look after changing your data from a range to a table.

Looking at your workbook from post #17, you didn't delete the named ranges.

[Becca83]

Oh, sorry, yes I knew you meant the name manager, where I had defined all the names. THANK YOU.

I'll show this to IT, who is against Forums, and show them the capability of cooperatives ! WHOOHOOOOOOOOOOOOOOOOOO

[63falcondude]

Great, happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Logit]

.
You are welcome.

I can't comment on IT depts. My experience has been very negative. Apologies to forum members who work in IT.