+ Reply to Thread
Results 1 to 9 of 9

Match 2 columns (a,b) not exact and return cell in b that contains cell in column b which

  1. 06-24-2014, 06:34 PM #1
    canuck1
    canuck1 is offline
    Registered User
    Join Date
    06-24-2014
    Location
    Vancouver
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Match 2 columns (a,b) not exact and return cell in b that contains cell in column b which

    ok been stuck all morning, time to ask for help...

    (columnB is empty = insert formula in B2 and copy down)

    columnA columnB columnC
    catseat -------- dog
    adogssnores ---- rat
    birdsfly --------- cat
    haterats


    If columnA A2 contains any element from columnC (not exact just includes the phrase = c4 cat is included in a2 so b2 would include the word 'cat') = then the data matching in columnC will be returned to columnB.

    Here's where i'm at with the formula:
    =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(Table1[Column3],A2,1)))),"FOUND","")
    This returns the following in columnB which is correctly identifying the 'found' rows:
    B2 = FOUND
    B3 = FOUND
    B4 =
    B5 = FOUND

    BUT would like to show the following in columnB (desired answer to question - how to?)
    B2 = CAT
    B3 = DOG
    B4 =
    B5 = RAT

    Any advice would be greatly appreciated as been stuck on this all morning and could really use a hand...
    Reply With QuoteRegister To Reply
  2. 06-24-2014, 07:09 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "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
    Reply With QuoteRegister To Reply
  3. 06-24-2014, 07:30 PM #3
    canuck1
    canuck1 is offline
    Registered User
    Join Date
    06-24-2014
    Location
    Vancouver
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

    Book1.xlsx

    Thanks for the help! Attached a better example in the excel file.
    Reply With QuoteRegister To Reply
  4. 06-24-2014, 10:54 PM #4
    canuck1
    canuck1 is offline
    Registered User
    Join Date
    06-24-2014
    Location
    Vancouver
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

    Any ideas? friendly bump...
    Reply With QuoteRegister To Reply
  5. 06-24-2014, 11:10 PM #5
    JR DHONA
    JR DHONA is offline
    Registered User
    Join Date
    07-01-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

    hi..

    your requirement to show all match value,
    make it more complicated..

    sample : "dog rat "

    anyway,
    please check.

    regards,
    Reply With QuoteRegister To Reply
  6. 06-25-2014, 12:04 AM #6
    canuck1
    canuck1 is offline
    Registered User
    Join Date
    06-24-2014
    Location
    Vancouver
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

    Yes it worked! Thank you!

    One more quick question:
    Have 1000+ entries in column3 which makes it kind of hard to copy/paste the required formula to analyze all the lines. At the moment it looks like it will only work for 4 entries in column3... if I was to enter 1000 lines in column 3 then the formula would also be around 1000 lines = too long

    maybe too much work? - but is there anyway to condense this so we could insert 1000 entries in column3 and have the formula still function?

    alternatively maybe something that could flag as 'Double' if it matches more than one phrase in column3

    Anyways I really appreciate your initial answer to my question... really great stuff!
    Have a nice day!
    Reply With QuoteRegister To Reply
  7. 06-25-2014, 12:48 AM #7
    JR DHONA
    JR DHONA is offline
    Registered User
    Join Date
    07-01-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

    sorry,

    no idea about that,

    just show how many match value,
    and show the 1st match i can do.

    please check it.

    regards,
    Reply With QuoteRegister To Reply
  8. 06-25-2014, 01:02 AM #8
    canuck1
    canuck1 is offline
    Registered User
    Join Date
    06-24-2014
    Location
    Vancouver
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

    That works perfectly!

    Thanks so much for your time...
    Have a nice day!

    Problem solved.
    Reply With QuoteRegister To Reply
  9. 06-25-2014, 05:43 AM #9
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Match 2 columns (a,b) not exact and return cell in b that contains cell in column b wh

    in your after example you just need
    =iferror(LOOKUP(10^99,SEARCH($C$18:$C$21,A18),$C$18:$C$21),"")
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Comparing two text columns and return data based on an exact match
    By AShah33 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2013, 04:23 PM
  2. [SOLVED] Match cell value and return another column value
    By thinkspac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-21-2012, 01:15 PM
  3. [SOLVED] Find / Match a cell value to another cell and return all column values
    By nupema in forum Excel General
    Replies: 6
    Last Post: 09-18-2012, 10:43 AM
  4. Look for a cell's text value in a column and if it matches return the cell value of match
    By usmankhan83 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-02-2012, 11:39 PM
  5. Return the index of the first cell in a one row/column array that does NOT match
    By js8765 in forum Excel General
    Replies: 2
    Last Post: 02-27-2010, 11:17 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1