+ Reply to Thread
Results 1 to 8 of 8

Operation like Vlookup but it looks at specific phrases in the cell?

  1. #1
    Registered User
    Join Date
    06-10-2023
    Location
    Austin, Tx
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Question Operation like Vlookup but it looks at specific phrases in the cell?

    Can I make a rule that if a phrase in A2 contains a specific word, then B2 must be a specific word? I.e.,

    The vlookup feature allows me to say if A2 is dog, B2 is canine. I want to write a sentence like "the dog jumped" in A2, and because it contains the word dog, B2 automatically says "canine."

    Is this possible?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Operation like Vlookup but it looks at specific phrases in the cell?

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


    Or:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Operation like Vlookup but it looks at specific phrases in the cell?

    Or, with 365:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    06-10-2023
    Location
    Austin, Tx
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Re: Operation like Vlookup but it looks at specific phrases in the cell?

    Thanks, but can I make this work for a whole list, so if it sees cat it will make B2 into feline, etc? Something like Animal List.xlsx

  5. #5
    Registered User
    Join Date
    06-10-2023
    Location
    Austin, Tx
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Re: Operation like Vlookup but it looks at specific phrases in the cell?

    Actually, something more like Animal List.xlsx to account for errors, where it becomes an Error if it finds multiple keywords but "other" if it finds no keywords.

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

    Re: Operation like Vlookup but it looks at specific phrases in the cell?

    This proposal employs a table of objects to find and outputs (tbl_Find)
    The formula that produces the output is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    06-10-2023
    Location
    Austin, Tx
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Re: Operation like Vlookup but it looks at specific phrases in the cell?

    That is perfect, thank you

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

    Re: Operation like Vlookup but it looks at specific phrases in the cell?

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. How to exctract specific phrases to columns?
    By jham55 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2021, 04:40 PM
  2. [SOLVED] Counting specific phrases within a given date period
    By antcha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2015, 07:42 PM
  3. VBA Macro to search document for specific keyword/phrases
    By Apcal88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-20-2015, 04:33 PM
  4. Advanced Filter for specific words/phrases within cells
    By dsal24 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-18-2014, 06:33 PM
  5. Deleting rows between same specific keywords/phrases
    By vijaijohn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-27-2013, 01:34 AM
  6. Replies: 0
    Last Post: 10-11-2012, 11:30 AM
  7. Compare lists of phrases in two workbooks and import only missing phrases
    By Fireblejd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2011, 06:59 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1