+ Reply to Thread
Results 1 to 10 of 10

Lookup+Search

  1. #1
    Registered User
    Join Date
    06-05-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    5

    Post Lookup+Search

    Hello! I am a newbie and I need some help.
    So here is the problem.
    I have a list of cooperatives in one excel file.
    In another excel file, I have a list of companies.
    I want to know if a certain cooperative is a company.
    So I made a formula with a lookup with a search function. So that it could look in the excel file with the list of companies and search it in the excel file with cooperatives and say "yes" if that company is a cooperative.

    This is my current formula, =LOOKUP(2,1/ISERROR(SEARCH([Book1.xlsx]Sheet1!$A$1:$A$16198,B13)),"Yes")
    Note: My cooperative list has about 20295 entries, so I copied the formula 20295 times. So that
    =LOOKUP(2,1/ISERROR(SEARCH([Book1.xlsx]Sheet1!$A$1:$A$16198,B13)),"Yes")
    =LOOKUP(2,1/ISERROR(SEARCH([Book1.xlsx]Sheet1!$A$1:$A$16198,B14)),"Yes")
    =LOOKUP(2,1/ISERROR(SEARCH([Book1.xlsx]Sheet1!$A$1:$A$16198,B15)),"Yes")
    =LOOKUP(2,1/ISERROR(SEARCH([Book1.xlsx]Sheet1!$A$1:$A$16198,B16)),"Yes")
    =LOOKUP(2,1/ISERROR(SEARCH([Book1.xlsx]Sheet1!$A$1:$A$16198,B17)),"Yes")
    =LOOKUP(2,1/ISERROR(SEARCH([Book1.xlsx]Sheet1!$A$1:$A$16198,B18)),"Yes")
    and so on.

    The problem is that all of the cells with my formula, display the error #VALUE! and I don't know how to fix it.

    Regards and Thanks in Advance.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Lookup+Search

    techbask,

    Give this a try:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    06-05-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Lookup+Search

    Hi! Thank you for your reply.
    But, there's a problem.
    In my cooperative excel file, I have the whole name of the cooperative, but, in the company excel file, I have only a part of the name of the cooperative.

    Example:
    In my cooperative excel file, I have: Beansters Consumers Cooperative
    In my company excel file, I have: Beansters Company

    Your code works only if I have the same name in my cooperative excel file and in my company excel file.
    Example:
    It works when:
    In my cooperative excel file, I have: Beansters Consumers Cooperative
    In my company excel file, I have: Beansters Consumers Cooperative

    It does not work when:
    In my cooperative excel file, I have: Beansters Consumers Cooperative
    In my company excel file, I have: Beansters Company

    Thank you for your help in advance.
    Last edited by techbask; 06-06-2012 at 01:29 AM.

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Lookup+Search

    How about a possibility of using only the first word say "Beansters" only is it ok with your file??

    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Registered User
    Join Date
    06-05-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Lookup+Search

    Hi vlady! Thanks for your input.

    But, I don't quite get what you are saying. Can you explain your code? Thankyou!

    P.S. The code does not work.

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Lookup+Search

    From original given
    =IF(ISNUMBER(MATCH("*"& B13 &"*",Sheet1!$A$1:$A$16198,0)),"Yes","")

    The problem is the two workbooks are not the same..
    e.g.

    Beansters Consumers Cooperative
    Beansters Company

    i suggest "if" it is possible to use only the first "word" for the match to find.. here in your sample the word "Beansters"

    so if your have in B13 = Beansters Company - we will use only the first word for the search

    the bold in the formula below will count how many words are there if only one then use that word
    otherwise get the "first word" --the underlined formula--which is Beanster from the words "Beansters Company" then this will be used as the search word.

    IF(LEN(B13)-LEN(SUBSTITUTE(B13," ",""))+1=1,B13,LEFT(B13,FIND(" ",B13)-1))

    by the way i took off the name of the "other workbook" [Book1.xlsx]Sheet1!$A$1:$A$16198 - i think that's why it isn't working..

    =IF(ISNUMBER(MATCH("*"&IF(LEN(B13)-LEN(SUBSTITUTE(B13," ",""))+1=1,B13,LEFT(B13,FIND(" ",B13)-1))&"*",[Book1.xlsx]Sheet1!$A$1:$A$16198,0)),"Yes","")

  7. #7
    Registered User
    Join Date
    06-05-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Lookup+Search

    Thanks vlady for your effort! Really appreciate it.
    But like the first code that tigeravatar gave me, it only works if the name is the same.

    It's like this again:
    Your code works only if I have the same name in my cooperative excel file and in my company excel file.
    Example:
    It works when:
    In my cooperative excel file, I have: Beansters Consumers Cooperative
    In my company excel file, I have: Beansters Consumers Cooperative

    It does not work when:
    In my cooperative excel file, I have: Beansters Consumers Cooperative
    In my company excel file, I have: Beansters Company

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Lookup+Search

    sorry i mis-interpret.

  9. #9
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Lookup+Search

    techbask,

    Give this a try:
    Please Login or Register  to view this content.

  10. #10
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Lookup+Search

    @ tiger i've slept with this(logic the op wants) last night, then i look at your formula above "shorter to get first word i and came up with this..

    Please Login or Register  to view this content.
    the word cooperative is the second key word to find. so in B14 the word "Beanster Company" becomes - "Beanster Cooperative" because we are searching if Beasnter is a cooperative in the search area.

    @ tech - now the problem here is if the word to search starts with "The" it becomes the first word..
    e.g. The Beanster Company...
    the above formula then will have the word words to search as " The Cooperative" so this is wrong.

    if you could post a sample of the Company names maybe we could have options to exclude some words...

    btw i didn't have time to verify the formula for other samples.
    thanks.

    hth..

+ 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