+ Reply to Thread
Results 1 to 6 of 6

How to vlookup if some text cell includes another text cell

  1. #1
    Registered User
    Join Date
    02-19-2020
    Location
    UA
    MS-Off Ver
    2016
    Posts
    7

    Question How to vlookup if some text cell includes another text cell

    Hello! Please help!
    I need to figure out if cells from col A in the sheet2 are included in any cell from col A in sheet1. For example, if A2Sheet2 is included in any cell from col A in Sheet1, if yes, return data from cell C of the first found match.
    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: How to vlookup if some text cell includes another text cell

    I don't think your sample provides enough data, honestly. And what are the expected results for the values you list on Sheet2? Are you trying to match the 'Search Query' term by itself, or that term AND the Campaign type?

    For 'coxreels' in Sheet2!A2, are you specifically looking for '*coxreels*' in any cell in Sheet1 column A? Or would you want *coxreel* to return a match?

    Based on your limited sample data, would the result for Sheet2!A2 be $656.19 from Sheet1!C8 since that's the first instance of 'coxreels' in Sheet1 column A?

  3. #3
    Registered User
    Join Date
    02-19-2020
    Location
    UA
    MS-Off Ver
    2016
    Posts
    7

    Re: How to vlookup if some text cell includes another text cell

    Yes, the returned result for Sheet2!A2 should be $656.19 from Sheet1!C8 since that's the first instance of 'coxreels' in Sheet1 column A. This way I need to have results for all search queries from Sheet2, is there is no instance N/A should be returned.
    Thanks!

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: How to vlookup if some text cell includes another text cell

    You might be able to use something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This uses wildcards to find the search string from Sheet2!A2 in any part of the cell in Sheet1!A2:A21, returning the corresponding value from column C (or #N/A if not found).

    If you only want it to search for cells that START WITH the search string, get rid of the first "*"& in the MATCH formula above.

  5. #5
    Registered User
    Join Date
    02-19-2020
    Location
    UA
    MS-Off Ver
    2016
    Posts
    7

    Re: How to vlookup if some text cell includes another text cell

    Thank you! It works!
    How to modify the formula if I want to return instead of a revenue number of quantity of found instances? For example how much of 'coxreels' were found in Sheet1 column A.

  6. #6
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: How to vlookup if some text cell includes another text cell

    This formula will count the number of times "coxreels" (the value in Sheet2!A2) appears in Sheet1 column A:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. [SOLVED] Extract text from cell which includes a date?
    By hilton426 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-02-2016, 08:44 AM
  2. Replies: 4
    Last Post: 09-21-2015, 05:07 AM
  3. Replies: 2
    Last Post: 03-17-2014, 12:14 PM
  4. If Cell Text Matches Any Text From Range, Then Apply VLOOKUP?
    By eliot1171 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2013, 09:41 PM
  5. look up with cell range, concatentate and text that includes the asterix
    By joolzh13 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-25-2013, 07:57 AM
  6. [SOLVED] Counting Cells that includes exact match and also if a cell includes specific text
    By smclachlan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-19-2012, 07:42 PM
  7. [SOLVED] Text that includes data from a designated cell?
    By Blades in forum Excel General
    Replies: 2
    Last Post: 06-27-2006, 08:10 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