+ Reply to Thread
Results 1 to 21 of 21

Return value when part of a cell matches a word in a reference column

  1. #1
    Registered User
    Join Date
    07-28-2015
    Location
    New Zealand
    MS-Off Ver
    2007
    Posts
    9

    Return value when part of a cell matches a word in a reference column

    Hi There

    I have a spreadsheet with 4 columns
    A = Date
    B = Description
    C = Value / Amount
    D = Lookup Column for matching with B

    D has a long list of values like Burger King, McDonalds, Starbucks.
    What I want to do is get the value from C where a word in B matches any of the words in D

    Example
    Column A
    Date
    01/01/2015

    Column B
    Description
    POS W/D BURGER K

    Column C
    Amount
    $20

    Column D
    BURGER K
    MCDONALDS
    STARBUCKS

    requirement - return the value of C into column E

    What I have tried is the following:
    =IF(ISNUMBER(FIND("MCDONALDS",B2)),C2,IF(ISNUMBER(FIND("BURGER K",B2)),C2,0))))

    The above works well but for every additional unique takeaway place I need to add an additional find and there is a limitation on the number of characters in the function.

    Any ideas are really appreciated.
    B

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Return value when part of a cell matches a word in a reference column

    Try this in E2:

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH(D$2:D$100,B2))),C2,0)

    then copy down.

    Hope this helps.

    Pete

  3. #3
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Return value when part of a cell matches a word in a reference column

    Hi,

    Welcome to the Forum.

    Can you post a sample excel file please? See the URL below for steps on how to upload a file.

    http://www.excelforum.com/members/da...ch-a-file.html

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Return value when part of a cell matches a word in a reference column

    ...or is this what you wanted? If not, please attach an Excel sheet, showing some examples AND your expected result

    =SUMPRODUCT((ISNUMBER(SEARCH(D1,$B$2:$B$20)))*$C$2:$C$20)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Return value when part of a cell matches a word in a reference column

    Or


    In E1 Cell


    =SUMIF($B$2:$B$20,"*"&D1&"*",$C$2:$C$20)


    Drag it down


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  6. #6
    Registered User
    Join Date
    07-28-2015
    Location
    New Zealand
    MS-Off Ver
    2007
    Posts
    9

    Re: Return value when part of a cell matches a word in a reference column

    I've tried to attach an example of what I already have.
    Attached Files Attached Files

  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Return value when part of a cell matches a word in a reference column

    May be this in E2:

    =SUMIF(B$2:B$8,"*"&D2&"*",C$2:C$8)
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-28-2015
    Location
    New Zealand
    MS-Off Ver
    2007
    Posts
    9

    Re: Return value when part of a cell matches a word in a reference column

    Almost - I need the result to return the value in column C in line with the description in Column B

  9. #9
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Return value when part of a cell matches a word in a reference column

    OR,

    If you just want to pull the first matching value from column C into E, try the following formula in E2:

    =IFERROR(INDEX(C$2:C$8,MATCH("*"&D2&"*",B$2:B$8,0)),"")

  10. #10
    Registered User
    Join Date
    07-28-2015
    Location
    New Zealand
    MS-Off Ver
    2007
    Posts
    9

    Re: Return value when part of a cell matches a word in a reference column

    sixthsense - thanks for the reply. I unfortunately don't want to sum them but it will be useful later on in my project so thank you.

  11. #11
    Registered User
    Join Date
    07-28-2015
    Location
    New Zealand
    MS-Off Ver
    2007
    Posts
    9

    Re: Return value when part of a cell matches a word in a reference column

    Pete_UK - thanks for your reply - it unfortunately didnt work.

  12. #12
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Return value when part of a cell matches a word in a reference column

    Quote Originally Posted by crazyb78 View Post
    I unfortunately don't want to sum them but it will be useful later on in my project so thank you.
    Or

    In E2 Cell


    =IFERROR(LOOKUP(2,1/SEARCH(LEFT(D2,FIND(" ",D2&" ")-1),$B$2:$B$8),$C$2:$C$8),"")


    Drag it down


    But don't expect accurate result. When there is no reliable search text then the result will also won't be a reliable one

  13. #13
    Registered User
    Join Date
    07-28-2015
    Location
    New Zealand
    MS-Off Ver
    2007
    Posts
    9

    Re: Return value when part of a cell matches a word in a reference column

    Thanks - That didn't give me what I wanted either unfortunately. Column D is only for reference and the numbers don't need to correspond to D but should align with B and C. I will eventually put the reference columns into their on sheet once I figure out the function / formula.

    Thanks for helping so far.

  14. #14
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Return value when part of a cell matches a word in a reference column

    Try the below array formula (Ctrl+Shift+Enter) and copy towards down
    =IFERROR(INDEX($C$2:$C$8,MATCH(1,COUNTIF($D2,"*"&REPLACE(LEFT($B$2:$B$8,FIND("-",$B$2:$B$8)-1),1,8,"")&"*"),0)),"")
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  15. #15
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Return value when part of a cell matches a word in a reference column

    =IFERROR(LOOKUP(2,1/COUNTIF($D2,"*"&MID($B$2:$B$8,9,FIND("-",$B$2:$B$8)-9)&"*"),C$2:C$8),"")
    or try above formula inspired by "Sixthsense"

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Return value when part of a cell matches a word in a reference column

    Put this in E2:

    =IF(ISNUMBER(MATCH(TRIM(SUBSTITUTE(REPLACE(B2,FIND("-",B2),255,""),"POS W/D ",""))&"*",D:D,0)),C2,0)

    then copy down. It works in your sample file.

    Hope this helps.

    Pete

  17. #17
    Registered User
    Join Date
    07-28-2015
    Location
    New Zealand
    MS-Off Ver
    2007
    Posts
    9

    Re: Return value when part of a cell matches a word in a reference column

    Thanks Pete - I think your solution has worked. Thanks to everyone else who gave their input. Much appreciated.

  18. #18
    Registered User
    Join Date
    07-28-2015
    Location
    New Zealand
    MS-Off Ver
    2007
    Posts
    9

    Re: Return value when part of a cell matches a word in a reference column

    Pete - I was wondering if you could help me with one modification. My list / reference column matches almost exactly but I have some descriptions that may vary slightly. Example - "STARBUCKS" may also be "STARBUCKS CBD". If I make this change in the description, it doesn't work.

    Thanks again for the help.

  19. #19
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Return value when part of a cell matches a word in a reference column

    Can't you add STARBUCKS CBD to the bottom of the list in column D?

    Pete

  20. #20
    Registered User
    Join Date
    07-28-2015
    Location
    New Zealand
    MS-Off Ver
    2007
    Posts
    9

    Re: Return value when part of a cell matches a word in a reference column

    Yes I can but this is only a subset of references. I have over 150 with a bunch that might be slightly different and unfortunately every time I visit a new "STARBUCKS", I would need to add that unique identifier to my list. If it's not possible to match it up then Ill go down the path of hard coding them.

    Cheers
    B

  21. #21
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Return value when part of a cell matches a word in a reference column

    Please post another sample file with some more examples of your descriptions in column B and your references in column D.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 14
    Last Post: 11-04-2014, 02:41 PM
  2. Replies: 15
    Last Post: 05-28-2014, 05:57 AM
  3. [SOLVED] Look up a column and return a value 1 cell below the word yes
    By Martin Chamberlin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-01-2013, 03:46 PM
  4. Replies: 9
    Last Post: 07-03-2013, 07:39 AM
  5. Replies: 1
    Last Post: 08-02-2012, 11:39 PM
  6. Replies: 2
    Last Post: 07-14-2011, 09:04 AM
  7. Change the value of the cell if part of it matches
    By egalitarian87 in forum Excel General
    Replies: 2
    Last Post: 05-10-2010, 01:49 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1