+ Reply to Thread
Results 1 to 4 of 4

Finding Text with a range of cells and returning the value of a cell if true

  1. 11-06-2014, 12:21 PM #1
    garydub
    garydub is offline
    Registered User
    Join Date
    10-07-2014
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    1

    Finding Text with a range of cells and returning the value of a cell if true

    Looking for a solution of looking for a text string with a range of cells. If it is contained then return the value of the cell, if not say "Not Found"

    I have tried building the formula but an hitting a blank

    Example

    Contents of Column

    Y:\master-documentation\allopurinol\dp\abo\tab\100mg\1.manufacture\allo-tab-100mg-dp-reg-bmr-[abo-gx-cm2-harman-compression].pdf

    String to search for "allo-tab-100mg-dp-reg-bmr" anywhere in the cell, want the value of the original cell returned.

    Any ideas?

    Thanks
    Gary
    Reply With QuoteRegister To Reply
  2. 11-06-2014, 12:27 PM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Finding Text with a range of cells and returning the value of a cell if true

    With your data in column A and your text search string in B1

    =INDEX(A:A, MATCH("*" & B1 & "*", A:A,0))
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 11-06-2014, 12:29 PM #3
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Finding Text with a range of cells and returning the value of a cell if true

    Try:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This assumes your range of values to lookup is in Column A, and the string to search for is in B1. Adjust those references as necessary.


    Edit: overlapped with ChemistB. Must be the right answer
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...
    Reply With QuoteRegister To Reply
  4. 11-06-2014, 01:00 PM #4
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Finding Text with a range of cells and returning the value of a cell if true

    Ha! I wish that were true.
    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. Finding text in an array but not always in same place and returning adjacent cell value
    By Thomas Bear in forum Excel General
    Replies: 5
    Last Post: 07-14-2014, 06:45 PM
  2. [SOLVED] Finding the nth non empty cell in a row and returning value to text box vba
    By jimalya in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-23-2013, 01:41 PM
  3. IF cell contains TEXT from [RANGE] = TRUE
    By tomshanan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2013, 03:14 AM
  4. Finding matching cells then returning data of another cell
    By GenericPat in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-19-2012, 03:36 PM
  5. [SOLVED] Finding text string within Range and returning Row/Column number
    By IanE78 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-09-2012, 12:13 PM

Tags for this Thread

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