+ 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

Hybrid View

  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 
    =IFERROR(INDEX(A:A,MATCH("*"&B1&"*",A:A,0)),"Not Found")


    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