+ Reply to Thread
Results 1 to 7 of 7

Wildcard Lookup Using Excel

  1. #1
    Registered User
    Join Date
    04-28-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Wildcard Lookup Using Excel

    Hello All,

    Ive tried all possible formulas to perform wildcard lookup bu invain

    My requirement is as described below:
    Sheet 1 have the following data in column A.
    Solar
    Wind
    Fuel Cells
    Renewable

    Sheet 2 have the following data in column A
    American Renewable Corp.
    Infosys Ltd.
    Tata BP Solar

    I need flags in Sheet 2 if the text has words mentioned in Sheet 1.
    Eg: American Renewable Corp. (sheet 2). has the word Renewable (sheet 1). Hence I want excel to add some kind of a flag in Sheet 2 adjacent to the cell which has the text "American Renewable Corp.".

    Similarly I want excel to compare other texts which is on Sheet 2.

    Please can someone help me

    Thanks!

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Wildcard Lookup Using Excel

    Assuming data starts at row 2 in each sheet then try this formula in B2 in sheet2

    =IF(COUNT(LOOKUP(2^15,SEARCH(Sheet1!A$2:A$5,A2))),"Yes","")

    copy down
    Audere est facere

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Wildcard Lookup Using Excel

    One way might be to use named ranges and conditional formatting.

    Select from the dropdown in C2, this is the named range "FindWord" with data validation = "RefTable"
    "RefTable" is dynamic, add more words to find as required.

    Hope this helps.

    [EDIT]
    Added count of matches found in Sheet2 to cell D2 Sheet1
    Attached Files Attached Files
    Last edited by Marcol; 04-28-2011 at 05:55 AM. Reason: Added to attachment
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Registered User
    Join Date
    04-28-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Wildcard Lookup Using Excel

    Quote Originally Posted by Marcol View Post
    One way might be to use named ranges and conditional formatting.

    Select from the dropdown in C2, this is the named range "FindWord" with data validation = "RefTable"
    "RefTable" is dynamic, add more words to find as required.

    Hope this helps.

    [EDIT]
    Added count of matches found in Sheet2 to cell D2 Sheet1
    Thanks Marcol for the fast reply

  5. #5
    Registered User
    Join Date
    04-28-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Wildcard Lookup Using Excel

    Thanks a ton Daddylonglegs This really helps You made my day!

  6. #6
    Registered User
    Join Date
    04-30-2011
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003
    Posts
    2

    Thumbs up Re: Wildcard Lookup Using Excel

    Quote Originally Posted by daddylonglegs View Post
    Assuming data starts at row 2 in each sheet then try this formula in B2 in sheet2

    =IF(COUNT(LOOKUP(2^15,SEARCH(Sheet1!A$2:A$5,A2))),"Yes","")

    copy down

    Hi, an extension to Dadylonglegs 's suggested formula. This will give name of defined word occurred
    =VLOOKUP(SUMPRODUCT(Sheet1!$A$2:$A$5,ABS((ISERROR(SEARCH(Sheet1!$B$2:$B$5,A2)))-1)),Sheet1!$A$1:$B$6,2,1)

    RESULT
    Company Name Sector Type
    American Renewable Corp. Renewable
    Infosys None
    Tata BP Solar Solar
    SolarWindFuel CellsRenewable More than 1 word
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-30-2011
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003
    Posts
    2

    Cool Re: Wildcard Lookup Using Excel

    Quote Originally Posted by daddylonglegs View Post
    Assuming data starts at row 2 in each sheet then try this formula in B2 in sheet2

    =IF(COUNT(LOOKUP(2^15,SEARCH(Sheet1!A$2:A$5,A2))),"Yes","")

    copy down

    Hi, an extension to Dadylonglegs 's suggested formula. This will give name of defined word occurred
    =VLOOKUP(SUMPRODUCT(Sheet1!$A$2:$A$5,ABS((ISERROR(SEARCH(Sheet1!$B$2:$B$5,A2)))-1)),Sheet1!$A$1:$B$6,2,1)

    RESULT
    Company Name Sector Type
    American Renewable Corp. Renewable
    Infosys None
    Tata BP Solar Solar
    SolarWindFuel CellsRenewable More than 1 word

+ 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