Pages

Saturday, December 31, 2011

Search Box to Search and Display Data



In this excel tutorial, we are going to learn how to make a search box. After creating search box, we will use it to search and display data from the database. In the above video you can see what we are going to create in this tutorial. We are going to do this in two steps.
  1. Creating search box.
  2. Displaying search result.
Creating Search Box 

















In this section we are going to create search box and the display box. Instruction for creating search box and display box are given below.










  1. Select cells "C2:E2" and click on "Merge & Center".
  2. Select all the cell around the merged cells and fill it with black color.
  3. Click on "B2". Change font color to white, font size to 12 and make it bold then type "Search".
  4. Select cells B5 to F5, hold Ctrl and select C6:F6, C7:F7, C8:F8, C9:F9, C10:F10, C11:F11 and C12:F12 and click on "Merge & Center".
  5. Fill with color as shown in the picture above.
  6. Click "B5" hold Shift and click on F12.
  7. Click on "Thick Box Border" from the border options.
Displaying Search Result 
Here is the sample data which I will be using in this tutorial to show you how you can search and data from the data base.

Click Image to Enlarge















  1. Go to Sheet 2 in the same workbook.
  2. Click on the formula tab and then "Define Names".
  3. Create the following named ranges i.e. Name, Age, Gender, Phone, Email, Address, and Country and type in the following formulas in "Refers to:" text box respectively














  • "=OFFSET(Sheet2!$A$2,0,0,COUNTA(Shee2!$A:$A)-1)" 
  • "=OFFSET(Sheet2!$B$2,0,0,COUNTA(Shee2!$B:$B)-1)"
  • "=OFFSET(Sheet2!$C$2,0,0,COUNTA(Shee2!$C:$C)-1)"
  • "=OFFSET(Sheet2!$D$2,0,0,COUNTA(Shee2!$D:$D)-1)"
  • "=OFFSET(Sheet2!$E$2,0,0,COUNTA(Shee2!$E:$E)-1)"
  • "=OFFSET(Sheet2!$F$2,0,0,COUNTA(Shee2!$F:$F)-1)"
  • "=OFFSET(Sheet2!$G$2,0,0,COUNTA(Shee2!$G:$G)-1)"
Formula To Enable Search

















  1. Go back to "Sheet1".
  2. Click "C6" and type "=IF($C$2="", "", $C$2)".
  3. Click "C7" and type  "=IF($C$2="", "", INDEX(Age,MATCH($C$2,Name,0)))".
  4. Click "C8" and type  "=IF($C$2="", "", INDEX(Gender,MATCH($C$2,Name,0)))".
  5. Click "C9" and type  "=IF($C$2="", "", INDEX(Phone,MATCH($C$2,Name,0)))".
  6. Click "C10" and type  "=IF($C$2="", "", INDEX(Email,MATCH($C$2,Name,0)))".
  7. Click "C11" and type  "=IF($C$2="", "", INDEX(Address,MATCH($C$2,Name,0)))".
  8. Click "C12" and type  "=IF($C$2="", "", INDEX(Country,MATCH($C$2,Name,0)))".
Now click on the search box and type a name you want to search. If the name you are searching is in the database, it will be displayed in the search "Search Result...". If the name you are searching is not in the database it will display "#N/A" in all the fields except name field. If  you like this tutorial or if you have a query, leave a comment at the end of this tutorial. Happy searching.

53 comments:

  1. Hi Anant!

    I found your guide here very useful indeed. It took me while but I now have a brilliant search box for my work thanks to you and your wonderful knowledge. I do have a query though...

    The search criteria for mine only consists of Surnanme, Name and then an 'Employee Number' (the purpose of the search tool is to find the Employee Number). The data which supplies the results is a rather large pool of people and I've chosen to make the search possible by using the Surname to do so - but there are some people on my list with the same Surname.

    Any ideas how I might be able to show all results for the same Surname in one search? For instance, we have 2 Joneses, Mark and Morgan. But if I search 'Jones' it will only display Mark.

    Hope you can help!

    Thanks again

    Ben

    ReplyDelete
  2. very nice Anant !
    but one query...
    it searches if exact text is entered.
    what can we do for search for part of text in cell ?

    vd. manish joshi, nashik

    ReplyDelete
  3. Anant, can you modify this into searching only text strings, I wanted to create a knowledge base for co-workers, the search should display links to text files depending upon the keywords searched.

    Thanx in advance.

    ReplyDelete
  4. Hi could you email me please at jamiebainbridge1981@hotmail.co.uk I need some help with my search box for my excel database.

    ReplyDelete
  5. Nice and simple. I like it. What if I have a hyperlink in one of the cells and need the it to be in the search result?

    ReplyDelete
  6. One of my columns contains hyperlinks. How can I get hyperlinks to show?

    ReplyDelete
    Replies
    1. I am assuming that you have your hyperlinks in column "H" in Sheet 2.
      First of all go to Sheet 2 and click on formula tab.
      In the formula tab click on "Define Name" to create a new named range "Hyper".
      Type in the following formula in the "Refers to" text box

      =OFFSET(Sheet2!$H$2,0,0,COUNTA(Sheet2!$H:$H)-1)

      Then go to sheet 1.
      Click on "C13" and type in the following formula

      =IF(#$C$2="","",INDEX(Hyper,MATCH($C$2,Name,0)))

      Now your search box should also display hyperlinks on your sheet.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Please note that the "#" sign should not be in the "If" formula. So be sure to remove it before using it in your sheet.

      Delete
  7. Tried again...definitely not working.
    I have hyperlinks in column "I", Name range is "Guides". Result shows in C14 but the result is not Hyperlinked. I modified the formulas to reflect the correct names and fields.

    ReplyDelete
    Replies
    1. Hey Anjoe

      You want to search result to show working hyperlinks in the result. If this is what you want then follow the simple steps below.

      1. Open Excel file.
      2. Press Alt+F11 on the keyboard to open vba editor.
      3. Click on "Insert" menu and then click on "Insert Module".
      4. Then Copy and Paste the following VBA code into the editor.

      Sub Hyper()
      Dim Cell As Range
      For Each Cell In Intersect (Selection, ActiveSheet.UsedRange)
      If Cell <> "" Then
      ActiveSheet.Hyperlinks.Add Cell, Cell.Value
      End If
      Next
      End Sub

      5. Then Exit the vba editor and try searching using search box.

      Delete
    2. Dear Anant pls look on my excel sheet getting error.

      Search ceat


      Search Result…
      BRAND ceat
      SIZE #N/A
      ID #N/A
      OD #N/A
      SW #N/A
      SH #N/A
      SP #N/A
      VOS #REF!
      VALVE CODE #N/A
      TUBE WEIGHT #N/A
      TUBE GAUGE #N/A


      BRAND SIZE ID OD SW SH SP VOS VALVE CODE TUBE WEIGHT TUBE GAUGE
      CEAT 20 30 20 30

      Nirdosh

      Delete
  8. I have a prob here..when i insert the formula, for example =OFFSET(Sheet2!$A$2,0,0,COUNTA(Shee2!$A:$A)-1, it detect has an error. The error is $A$2,0,0,COUNTA. do you have any idea..?

    ReplyDelete
    Replies
    1. The error you quoted above is because you are forgetting to close offset bracket.
      It should be like the following

      =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2$A:$A)-1)

      see if it helps

      Delete
    2. Anant, this is great search option db. Do you have a formula to search for any part of the text? currently you have to enter the exact name but it would be helpful if you can search by any letter.

      Delete
  9. Hi! What if I will get the data from other workbook? and I did try to search for the row 200 data but it display NA. Thanks for the help :)

    ReplyDelete
  10. Hi... This is A great Tutorial However , I have question,

    Is it possible to search anything else except from the name but it still comes up with all the information. And i f possible please coud you help :)

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Hello my friend and thanks for tutorial. Im trying to put the formula =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2$A:$A)-1) but it says that there is a problem with the formula.

    ReplyDelete
  13. Hello Anant,

    I just love how the search and display box look, but I have a problem. When I try to enter formula, it gives me an error. Could you please help me with this? Do I need to click on cell "Name" when I want to define name? Cause when I do, it won't let me enter the formula you wrote =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2$A:$A)-1)
    It marks this part: $A$2,0,0,COUNTA

    I really need this badly, so I hope that I'll get a reply...

    ReplyDelete
    Replies
    1. Hello Bojana

      No. You don't have to click on cell "Name" to define name. Instead you have to type everything in the "Edit Name" dialogue box.

      Delete
  14. hi
    can i use in the next sheet ?
    Please help me how i can i need it
    or send me email
    mahmudi4u@gmail.com

    ReplyDelete
    Replies
    1. Hi Mahmudi

      Can you clarify what exactly you want to do?

      Delete
  15. Pls can anyone make a video tutorial for this lesson?
    I can't understand this way plz....

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. GREAT SEARCH TOOL, I HAVE A QUESTION HOW CAN I ADD MULTIPLE DATA ON THE SAME "COLUMN" FOR EXAMPLE

    PART NUMBER--------ADDRESS
    2345 ------------------- 1
    2345 ------------------- 5

    I WANT WHEN I DO THE SEARCH IT GIVE ME THE 2 DIFFIDENT ADDRESSES

    ReplyDelete
  18. Please Can you send me an E-Mail, I need Urgent Help on this.
    Thanks
    Coenie36@gmail.com

    ReplyDelete
  19. I am having a problem with 4 of my rows showing the #VALUE! error. If you could be so kind to email me when you have a chance so that I may try to fix this.

    Thanks,

    karhan@live.ca

    ReplyDelete
  20. CAN you please do the same and email me the file . cesar209@gmail.com

    ReplyDelete
  21. i have also a problem with the rest of the rows showing #NAME? except name. please help me how to fix this.

    thanks

    froebel18@yahoo.com

    ReplyDelete
  22. This comment has been removed by the author.

    ReplyDelete
  23. This is working for me. However, when I add new items to my data and try to search for the new data, I get #REF! errors.

    The only way I can get rid of the #REF errors. Is to re-enter all of the fomulas.

    Click "C6" and type "=IF($C$2="", "", $C$2)".
    Click "C7" and type "=IF($C$2="", "", INDEX(Age,MATCH($C$2,Name,0)))".
    Click "C8" and type "=IF($C$2="", "", INDEX(Gender,MATCH($C$2,Name,0)))".
    Click "C9" and type "=IF($C$2="", "", INDEX(Phone,MATCH($C$2,Name,0)))".
    Click "C10" and type "=IF($C$2="", "", INDEX(Email,MATCH($C$2,Name,0)))".
    Click "C11" and type "=IF($C$2="", "", INDEX(Address,MATCH($C$2,Name,0)))".
    Click "C12" and type "=IF($C$2="", "", INDEX(Country,MATCH($C$2,Name,0)))".

    Any idea why?

    ReplyDelete
  24. Anant, Can you please email me, I need some help with this. I have to fix a couple of things and I don't know how to.

    ReplyDelete
  25. Is there a way to have a seperate box for multiple instances of the search parameter?

    My search is based on a location name for projects, there may be multiple projects and I would like to have another results "box" for the next instance.

    ReplyDelete
  26. hi, can anyone help me to make an excel sheet with this search option,
    my email is
    saahil.pk@gmail.com

    ReplyDelete
  27. Its urgent please,
    please do the needful

    ReplyDelete
  28. hi, can anyone send me excel sheet with this search option (will be great if your search db search more than one word or have option and/or)
    my email is romon.ft@gmail.com
    Thank you in advance.

    ReplyDelete
  29. was anyone able to switch from exact name search to any part of the text search?? if yes, can you share??

    ReplyDelete
  30. Anant, this is a great search box DB. Do you have a formula to search any part of the text instead of the exact text?? if yes, can you post or email me??

    ReplyDelete
  31. this is Babitha. Thank u so much, I was searching for the exact tutorial since morning anf found it over here, thank u so much for such a great work.

    ReplyDelete
  32. how can I add multiple results while using this method? only one type of result is coming out at the minute but I have many different results for one search

    ReplyDelete
  33. Thank you for share this information. Happy to visit this blog.
    Display Boxes

    ReplyDelete
  34. Can I Copy This Tricks To My Website

    ReplyDelete
  35. i want this search box on the same page on which data exist. pls help. also help to understand the logic

    ReplyDelete
    Replies
    1. define same Sheet on current page name is enough

      Delete
  36. i like the search box template but what happened where a variable need to be entered twice like 2 people using the same phone number?

    ReplyDelete
  37. how to add multiple Sheets in offset commands

    ReplyDelete
  38. Can u give me the sample excel sheet on mail . My mail id is
    prathameshdande@gmail.com

    ReplyDelete
  39. Your blog has given me that thing which I never expect to get from all over the websites. Nice post guys!


    Web Hosting Services

    ReplyDelete

Recent Posts

HostGator Promos