+ Reply to Thread
Results 1 to 3 of 3

Match part of cell to any value in range

  1. #1
    Registered User
    Join Date
    11-23-2012
    Location
    NC
    MS-Off Ver
    Excel 2007
    Posts
    2

    Match part of cell to any value in range

    I'm working on matching a set of search queries to identified keywords I am tracking. Here is an example:

    A B C
    1 query Keyword
    2 policy policy
    3 policy and procedure guideline
    4 sick pay guidelines agenda
    5 calendar brochure
    6 medical leave calendar

    Column A contains the search query. Column C contains the list of keywords. I'm trying to find a formula for column B that will match any part of cell A3 to any cell in C and then tell me what the value of C is. For example I would like cell B2 to read "Policy", B3 to read "policy", likewise B4 should read "guidelines". I'm very close with an Index Match formula, but I can only get it to match the first word and then it goes to the closest value in column C. I say I would like a formula, but a VB script would be ok too (very novice at visual basic though).

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,051

    Re: Match part of cell to any value in range

    Hi mek and welcome to the forum

    copy this down. It will test the values you have in C to see if they match any of the text in column A...
    =MATCH("*"&C2&"*",$A$2:$A$6,0)

    Hope this helps?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-23-2012
    Location
    NC
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Match part of cell to any value in range

    Thanks for the formula and the welcome, FDibbins! That one works for relating it from C to A. I have some instances where the value in C might match other options in A as well. For the example above, A2 and A3 both have the word "policy" in them and should be tagged as "policy" (or match cell 1 in the range) in the B column. When I applied the formula, it indicated the keyword "policy" matched cell 1 of the query column, when it really needed to match 1 and 2 (and possibly more past row 6).

+ 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