+ Reply to Thread
Results 1 to 10 of 10

Lookup values in Col A that contain partial value from Col B and return value from Col C

  1. 05-02-2014, 12:25 PM #1
    seanomuiri
    seanomuiri is offline
    Registered User
    Join Date
    01-13-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    15

    Lookup values in Col A that contain partial value from Col B and return value from Col C

    Hi - i have a column of text in column A on sheet1. Then i also have a two column table (A:B) on sheet2. In column B on Sheet1 I want to check if the values in column A on sheet1 contain the values from the column A on sheet2, then return the values from column B on sheet2.

    e.g.
    Col A (sheet1)
    "Big Dogs"
    "little Dogs"
    "scary dogs"
    "big bears"
    "little cats"
    "bunny rabbits"

    Col A&B (sheet2)
    Col A / Col B
    "dogs" / "dog link"
    "cats" / "cat link"
    "bears" / "bear link"
    "rabbits" / "rabbit link"

    in Col B on sheet 1 it should return the following values:
    "dog link"
    "dog link"
    "dog link"
    "bear link"
    "cat link"
    "rabbit link"

    How do i achieve this please? I was thinking an IF formula but couldn't figure out how - appreciate some help.
    Reply With QuoteRegister To Reply
  2. 05-02-2014, 12:34 PM #2
    dluhut
    dluhut is offline
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,101

    Re: Lookup values in Col A that contain partial value from Col B and return value from Col

    Paste this formula on your col B on Sheet 1

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 05-02-2014, 12:50 PM #3
    seanomuiri
    seanomuiri is offline
    Registered User
    Join Date
    01-13-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Lookup values in Col A that contain partial value from Col B and return value from Col

    hi - unfortunately, that doesn't work for me - not sure what your FIND formula is doing? am i supposed to fill those find formulas with something?
    Reply With QuoteRegister To Reply
  4. 05-02-2014, 01:17 PM #4
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Lookup values in Col A that contain partial value from Col B and return value from Col

    The formula suggested by dluhut will work assuming that your data on sheet1 in col. A starts from A1 and the values in col. A consist of two words having one space between them (as shown by you on post#1).
    If your data on sheet1 doesn't start from A1 and values in col. A has more than two words with more than 1 space between them, the formula won't work.
    So if your data starts from A2 in col. A, change A1 to A2 in the formula.
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
  5. 05-02-2014, 01:25 PM #5
    seanomuiri
    seanomuiri is offline
    Registered User
    Join Date
    01-13-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Lookup values in Col A that contain partial value from Col B and return value from Col

    my list in col A on sheet1 contains cells with more than two words - some cells do contain 2 words, some 3 words, some as many as 10 words

    btw, the formula didnt work for cells with 2 words either, and i did change the A1 to A2 as you mentioned.
    Reply With QuoteRegister To Reply
  6. 05-02-2014, 01:29 PM #6
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Lookup values in Col A that contain partial value from Col B and return value from Col

    In that case please upload a sample workbook with enough sample data in it along with the desired output filled manually.
    To upload a workbook, click on Go Advanced (bottom right of the Quick Reply box), there you will find a paper click icon to upload a workbook, follow the simple instructions and upload a sample workbook here.
    Reply With QuoteRegister To Reply
  7. 05-02-2014, 02:04 PM #7
    seanomuiri
    seanomuiri is offline
    Registered User
    Join Date
    01-13-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Lookup values in Col A that contain partial value from Col B and return value from Col

    hi sktneer - attached is my sample file, appreciate you looking into this.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 05-02-2014, 02:23 PM #8
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Lookup values in Col A that contain partial value from Col B and return value from Col

    Try this Array Formula in B1 (Sheet1). Since this is an array formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)

    Please Login or Register  to view this content.
    Is this what you want?
    Attached Files Attached Files
    Last edited by sktneer; 05-02-2014 at 02:29 PM.
    Reply With QuoteRegister To Reply
  9. 05-02-2014, 02:42 PM #9
    seanomuiri
    seanomuiri is offline
    Registered User
    Join Date
    01-13-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Lookup values in Col A that contain partial value from Col B and return value from Col

    that's brilliant, thats exactly what i needed, thanks very much! appreciate it!
    Reply With QuoteRegister To Reply
  10. 05-02-2014, 11:04 PM #10
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Lookup values in Col A that contain partial value from Col B and return value from Col

    Glad to help you. Thanks for the feedback.
    Since you are new to the forum so just to tell you that if that takes care of your question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
    Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.
    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. Return any values from partial text in single reference column
    By baba_jb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-30-2013, 01:03 AM
  2. Lookup partial value and return text I define
    By Venessa in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2013, 11:21 AM
  3. Formula needed to return values for partial or full match
    By IntRes in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-07-2012, 08:32 AM
  4. Lookup multiple partial match conditions and return values
    By darklans in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-18-2012, 03:26 AM
  5. VLOOKUP to return values based on partial text
    By cartlidge_adrian in forum Excel General
    Replies: 5
    Last Post: 01-13-2012, 05:20 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