+ Reply to Thread
Results 1 to 3 of 3

Search a Column for a string in an array and return value corresponding to that string

  1. #1
    Registered User
    Join Date
    05-11-2022
    Location
    tampa
    MS-Off Ver
    Office 365
    Posts
    1

    Search a Column for a string in an array and return value corresponding to that string

    I am trying to search a column with a lot of text in it for a few different text strings, and if found, I would like to display a value that corresponds to that string

    Status:
    Rag: Red, because….
    Rag:Green, moving along
    Rag: Green On track
    RAG: Not Started

    I would like to search the above column for the string listed below on a different sheet, and display in a column on the first sheet, the value in column B

    COLUMN A COLUMN B
    RAG: Green G
    RAG: Red R
    RAG: Amber A
    RAG:Green G
    RAG:Red R
    RAG:Amber A
    RAG: RED/AMBER/GREEN NS
    RAG:Not Started NS
    RAG: NOT STARTED NS
    RAG: Not Started NS

    So the result would be

    Rag: Red, because…. R
    Rag:Green, moving along G
    Rag: Green On track G
    RAG: Not Started NS

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Search a Column for a string in an array and return value corresponding to that string

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Search a Column for a string in an array and return value corresponding to that string

    In B2 copied down:

    =IFERROR(LOOKUP(99999,SEARCH($A$10:$A$19,A2),$B$10:$B$19),"")
    Attached Files Attached Files

+ 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. Search String in Column Return String
    By Notgoodatexcel1 in forum Excel General
    Replies: 3
    Last Post: 06-29-2020, 07:07 PM
  2. Formula to search text string for an array of keywords and return given value
    By JulietLovesExcel in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-08-2019, 06:40 PM
  3. Search for string in a 2d array and return string
    By david0985 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-11-2016, 04:59 AM
  4. Script to Search for a specific String then return cell values above that String-4
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2015, 01:28 AM
  5. [SOLVED] Script to Search for a specific String then return cell values above that String-3
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2015, 01:50 PM
  6. [SOLVED] Return matching string in array if cell contains string contained in the array
    By AaronsZ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-03-2013, 01:55 PM
  7. Search column for string and return every row with that string in new sheet
    By myers601 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2012, 06:02 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1