+ Reply to Thread
Results 1 to 8 of 8

Wildcard Lookup

  1. #1
    Registered User
    Join Date
    10-14-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Wildcard Lookup

    I’m trying to write a formula that will search for certain words contained in the cells of Column A and then return those words in column B. The contents of each cell will contain more information than the key word being searched for.

    The aim will be to import transaction descriptions into Column A and then to search the descriptions for key words which will be used as transaction categories. So for example, Column A may contain…

    A
    1 1234 petrol
    2 4565 joe blogs div
    3 Interest Charge

    I want to search A1, A2, A3 etc for the words “petrol”, “div” and “Charge” and then return “petrol”, “div” or “charge” into the corresponding row in column B. So that B1 shows “Petrol”, B2 shows “Div”, B3 shows “Charge”. Any help would be much appreciated. I don’t think you can use wild cards with nested IFs so I’m a bit stuck.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Wildcard Lookup

    You can use a variety of IFs

    Please Login or Register  to view this content.
    Change SEARCH to FIND if you wish for your tests to be case sensitive.

  3. #3
    Registered User
    Join Date
    10-14-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Wildcard Lookup

    Wow. Thanks very much for the quick reply. This works perfectly!

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Wildcard Lookup

    To be safe you should probably revert to

    Please Login or Register  to view this content.
    eg a string containing Surcharge would not return Charge using the above whereas it would using the previous approach.

  5. #5
    Registered User
    Join Date
    10-14-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Wildcard Lookup

    Am I going to run into the 7 IF limit with this formular? Is there any way I could have more than 7 categories? Thanks

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Wildcard Lookup

    you could create a table and drag down see attached
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Wildcard Lookup

    If you were to create a listing of the keywords, lets say they exist in C1:C10 with strings possibly containing keywords in A1 (and below), then

    Please Login or Register  to view this content.
    - the above looks for exact matches - ie dog <> dogs ... tread <> read etc...

    - should multiple keywords be found in A1 the above will return the last, if you prefer the first the above can be reverted to an INDEX based approach, if indifferent the above is probably the slightly simpler of the two.

  8. #8
    Registered User
    Join Date
    10-14-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Wildcard Lookup

    Thanks again. That's perfect.

+ 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