+ Reply to Thread
Results 1 to 3 of 3

Find specific text within a cell using IF and VLOOKUP vba

  1. #1
    Registered User
    Join Date
    04-06-2012
    Location
    Pleasanton, California
    MS-Off Ver
    Excel 2007
    Posts
    3

    Find specific text within a cell using IF and VLOOKUP vba

    Hey all,

    I'm new to the forums but have found many solutions through your great forum so thanks!

    I am having trouble with an excel formula that hopefully someone can help me with.

    Here's the scenario:
    Sheet1 contains questions in column A and answers in column B. The location/order of these questions may vary as questions not answered are filtered out and deleted before being queried and uploaded.
    Sheet2 contains the same questions, but have cells that will be populated with the answers from Sheet1.

    My approach is to run my vba macro to enter in formulas into the blank cells in Sheet2 so that they reference Sheet1's answers. This works great as it returns the answers but I need to make it more complex so that it not only finds the correct question but that it searches within the answer for specific text. If it contains the specific text then return a "Yes" and if it doesn't then return a "No"

    Here's my vba code:
    'This is the code used for the current questions, as you can see i return "nothing" if it is N/A, and a vlookup for the answer if there is a value in Sheet1.
    Sheets("Sheet2").Range("B3").Formula = "=IF(ISNA(VLOOKUP(A3,'Sheet1'!$B$2:$D$373,2,FALSE)),"""",VLOOKUP(A3,'Sheet1'!$B$2:$D$373,2,FALSE))"
    'What I need to do is search within the range, if the questions match, search in the cell in the next column over and whether it contains specific text.
    'Here is an example of searching the text in the cell which works, but i need to search within a range, as the question will change locations
    Sheets("Sheet2").Range("B4").Formula = "=IF(ISERROR(SEARCH(""Chicago"",'Sheet1'!$C$22)),""No"",""Yes"")"

    I hope that I explained this well enough, but in summary i would like to use a vlookup that will find the respective answer cell, search within the text of the cell to see if it contains the desired text, and if it does, return a "Yes", if it doesn't then return a "No". In addition, incorporate a ISNA or ISERROR function that will eliminate a #N/A! or #VALUE! error.

    Thanks for your help in advance!!

    Dean

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    San Francisco
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,159

    Re: Find specific text within a cell using IF and VLOOKUP vba

    Post a workbook with sample data and what the end result looks like.

  3. #3
    Registered User
    Join Date
    04-06-2012
    Location
    Pleasanton, California
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Find specific text within a cell using IF and VLOOKUP vba

    So I was able to accomplish what I needed with this code:
    Sheets("Sheet2").Range("B3").Formula = "=IF(ISERROR(VLOOKUP($D$19,'Sheet1'!$B$2:$D$373,2,FALSE)),""No"",IF(ISERROR(FIND(""Chicago"", VLOOKUP($D$19,'Sheet1'!$B$2:$D$373,2,FALSE))),""No"",""Yes""))"

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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