+ Reply to Thread
Results 1 to 8 of 8

Vlookup Function Finding Specific Value in a Cell from a Column of Texts

  1. #1
    Registered User
    Join Date
    08-17-2017
    Location
    San Francisco, CA
    MS-Off Ver
    2016
    Posts
    26

    Vlookup Function Finding Specific Value in a Cell from a Column of Texts

    Hi Forum,

    I want to find a specific value in a cell from a column of long text in another cell using vlookup function (I believe this is the best one). The return value would be any indication that the value is contained in that column of long texts. Below is an example:

    Find "Mary" from C2 in D3:D30, D3 = "Mary had a little lamb" , D4 = "Where is Mary?" = Return value would be "Yes" or "No" Mary is contain in D3:D30 of texts.

    Return Values Scenarios
    1) Yes or No
    2) Count of how many times the value is, 2 times in this example
    3) Return of ID of rows Mary is mentioned, D4 and D3


    Thanks Forum.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Vlookup Function Finding Specific Value in a Cell from a Column of Texts

    this will return what you need for #2... =COUNTIF(D3:D30,"*mary*")
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Vlookup Function Finding Specific Value in a Cell from a Column of Texts

    1) =IF(COUNTIF(D3:D30,"*"&C2&"*"),"Yes","No")

    2) =COUNTIF(D3:D30,"*"&C2&"*")

    3) Would you rather have against each row in the list D3:D30 a Yes or No, if it contains the word Mary? If so, use in E3
    =IF(COUNTIF(D3:D3,"*"&C2&"*"),CELL("address",D3),"")

    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Vlookup Function Finding Specific Value in a Cell from a Column of Texts

    this will get you the first one... {=IF(COUNT(FIND("mary",D3:D30))>0,"Yes","No")}
    notice it is an array formula so it has to be activated by ctrl+shift+enter so the {} appear on either side of it. You can't add them. You click in the formula bar and then hit ctrl+shift+enter at the same time (if you've not used an array before).

  5. #5
    Registered User
    Join Date
    08-17-2017
    Location
    San Francisco, CA
    MS-Off Ver
    2016
    Posts
    26

    Re: Vlookup Function Finding Specific Value in a Cell from a Column of Texts

    Thanks,

    I used =COUNTIF(D3:D30,"*"&C3&"*") ... C3 = Mary.

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Vlookup Function Finding Specific Value in a Cell from a Column of Texts

    I used =COUNTIF(D3:D30,"*"&C3&"*") ... C3 = Mary.
    Yeah, I would've too if I'd have come up with it first. Much better than an array.

  7. #7
    Registered User
    Join Date
    08-17-2017
    Location
    San Francisco, CA
    MS-Off Ver
    2016
    Posts
    26

    Re: Vlookup Function Finding Specific Value in a Cell from a Column of Texts

    Ace,

    For 3)...

    If there's a Column A1 with unique IDs (such as 1,2,3,4.....) where the rows contains word "Mary" were in D4 and D3.... can the return be A4 and A3 reading out the IDs of A4 and A3?

    Thanks much,

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Vlookup Function Finding Specific Value in a Cell from a Column of Texts

    It may help if you follow the instructions in the banner at the top of the page and illustrate you query.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. COUNTIF? or VLOOKUP for 2 column/texts
    By liqt in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 01-26-2017, 11:27 PM
  2. vlookup - based on finding specific text within a cell
    By gromitnz in forum Excel Formulas & Functions
    Replies: 60
    Last Post: 05-16-2016, 06:14 AM
  3. finding the 3rd to 5th texts in a series of texts
    By managingcrap in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2016, 11:39 PM
  4. [SOLVED] Message box pup up when cell contains specific texts
    By kleevin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2013, 02:03 AM
  5. Finding data between two texts in same column and then copy all the rows
    By VKS in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-19-2013, 11:59 AM
  6. [SOLVED] VLOOKUP containing specific texts
    By JakeMann in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2013, 11:48 AM
  7. Replies: 3
    Last Post: 03-02-2011, 01:56 PM

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