+ Reply to Thread
Results 1 to 14 of 14

Lookup Formula Issue, Search issue

  1. #1
    Registered User
    Join Date
    04-02-2014
    Location
    Enfield, CT
    MS-Off Ver
    Excel 2007
    Posts
    8

    Lookup Formula Issue, Search issue

    I am trying to create a search database using excel. I have it mostly created but need a couple things to happen in order for it to be beneficial. I want the search box to be able to search partital words such as if a street name is Deer Run, if I search Deer or even Dee, it would find Deer Run. The other thing I need is if I have 2 of the same street names but in another column in the database it is a different town that I can somehow scroll threw them. Can anyone with any experience help me??? I can email out what I have so far so you can see what mess was created already.
    Last edited by kperitz; 04-02-2014 at 12:55 PM.

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,547

    Re: Lookup Formula Issue, Search issue

    Upload your sample workbook with enough sample data in it here itself.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    04-02-2014
    Location
    Enfield, CT
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Lookup Formula Issue, Search issue

    Here is the file I am working on. What I need done with it is:
    1) on the lookup tab in the "Search Box I want to be able to abbreviate the spelling of a street name, (ex Deer Run to be able to just type Dee or something.
    2) You will see in the database there are street names that are the same but in different districts. I need an option on the lookup tab to be able to scroll to the correct one when I enter in the search name.
    3) The search name will always be the street name
    Attached Files Attached Files

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,242

    Re: Lookup Formula Issue, Search issue

    Maybe this could help
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-02-2014
    Location
    Enfield, CT
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Lookup Formula Issue, Search issue

    YES!!!!
    It's almost perfect.
    All I need now is that on Streets tab the formula works for all new entries. I added one and its not showing on the search tab. I have several thousand rows to add into the street database.
    Also Is there a way so that on the search tab if there is nothing in the Street Search box the listing below is blank instead of it listing all the data?
    I attached the sheet again with some changes.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-02-2014
    Location
    Enfield, CT
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Lookup Formula Issue, Search issue

    YES!!!!
    It's almost perfect.
    All I need now is that on Streets tab the formula works for all new entries. I added one and its not showing on the search tab. I have several thousand rows to add into the street database.
    Also Is there a way so that on the search tab if there is nothing in the Street Search box the listing below is blank instead of it listing all the data?
    I attached the sheet again with some changes.
    Attached Files Attached Files

  7. #7
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,242

    Re: Lookup Formula Issue, Search issue

    In B6 and down, change to this:

    =IFERROR(IF($C$2="","",INDEX(Streets!A$2:A$6,SMALL(IF(ISNUMBER(SEARCH($C$2,Streets!$A$2:$A$6)),ROW(Streets!$A$2:$A$6)-MIN(ROW(Streets!$A$2:$A$6))+1),ROWS($1:1)))),"")

    Array Formula, you need to confirm press CTRL-SHiFT-ENTER button together, ENTER alone is not works
    Attached Files Attached Files

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,547

    Re: Lookup Formula Issue, Search issue

    Please see the attached sheet. You can add 50000 rows (you can increase this no. but this will make the sheet calculation slow) on streets sheet to reflect the search result on the search sheet.

    In the cell B8 of search sheet, place the below formula and confirm it with Ctrl+Shift+Enter and then drag across and down.
    Please Login or Register  to view this content.
    For detail see the attached sheet.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-02-2014
    Location
    Enfield, CT
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Lookup Formula Issue, Search issue

    I don't understand, Is it set up now correctly? I don't know where I need to place the formula. Also is there a way to not have the 0 show up in the search results if there is nothing entered in the Street Data tab? I'm trying to keep the Search results as clean as possible.

  10. #10
    Registered User
    Join Date
    04-02-2014
    Location
    Enfield, CT
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Lookup Formula Issue, Search issue

    I don't understand, Is it set up now correctly? I don't know where I need to place the formula. Also is there a way to not have the 0 show up in the search results if there is nothing entered in the Street Data tab? I'm trying to keep the Search results as clean as possible.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-02-2014
    Location
    Enfield, CT
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula issue

    Can anyone help me on the attached spreadsheet to make it not show the "0" in columns G and H on the Search tab if there is no data entered on the Streets tab on a particular row?
    Attached Files Attached Files

  12. #12
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,547

    Re: Lookup Formula Issue, Search issue

    Please find the attached sheet.
    Attached Files Attached Files

  13. #13
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,242

    Re: Lookup Formula Issue, Search issue

    Im sorry forget to tell that Column C through H has different formula with Column B (Street Names)

    Pls see the file
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    04-02-2014
    Location
    Enfield, CT
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Lookup Formula Issue, Search issue

    I believe it is perfect... Thank you all!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1