Find specific text within a cell using IF and VLOOKUP vba

[dean.rogers]

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

 

[guerillaunit]

Try this

=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$B$3,2,FALSE)),"""",IF(ISERROR(FIND("obama", VLOOKUP(A1,Sheet1!$A$1:$B$3,2,FALSE))), "No", "Yes"))

I am trying to find Obama in the above code. If you're not trying to find him, change the part with his name

 

[AlphaFrog]

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"

Where is the correct answer (text) to search for each question. What column on sheet2 has the correct answers?

 

[AlphaFrog]

This assumes Sheet1 is the user's Questions (column B) and Answers (column C)

Sheet2 is the List of all questions (column A), Answers (column b), and the Yes\No results (column C).

[color=darkblue]Sub[/color] Find_Answers()

    [color=darkblue]Dim[/color] Question [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Found [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] rngSearch [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] ws1 [color=darkblue]As[/color] Worksheet, ws2 [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]Set[/color] ws1 = Sheets("Sheet1")   [color=green]'User's answers sheet[/color]
    [color=darkblue]Set[/color] ws2 = Sheets("Sheet2")   [color=green]'[/color]
    
    [color=darkblue]Set[/color] rngSearch = ws1.Range("B:B")    [color=green]'Users questions[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Question [color=darkblue]In[/color] ws2.Range("A1", ws2.Range("A" & Rows.Count).End(xlUp))
    
        [color=darkblue]If[/color] [color=darkblue]Not[/color] IsEmpty(Question) [color=darkblue]Then[/color]
        
            [color=darkblue]Set[/color] Found = rngSearch.Find(What:=Question, _
                                       LookIn:=xlValues, _
                                       LookAt:=xlWhole, _
                                       SearchOrder:=xlByRows, _
                                       SearchDirection:=xlNext, _
                                       MatchCase:=False)
                
            [color=darkblue]If[/color] [color=darkblue]Not[/color] Found [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                [color=darkblue]If[/color] InStr(Found.Offset(, 1).Value, Question.Offset(, 1).Value) [color=darkblue]Then[/color]
                    [color=green]'Correct answer[/color]
                    Question.Offset(, 2).Value = "Yes"
                [color=darkblue]Else[/color]
                    [color=green]'Incorrect answer[/color]
                    Question.Offset(, 2).Value = "No"
                [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]Set[/color] Found = [color=darkblue]Nothing[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Else[/color]
            [color=green]'No match found to the question[/color]
            
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] Question
    
    Application.ScreenUpdating = [color=darkblue]True[/color]

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[dean.rogers]

Guerillaunit's code worked perfectly for what i was trying to accomplish, I did make just a few changes to match correctly what i needed,

Here is the final code i used that worked for me

Sheets("Sheet2").Range("B3").Formula = "IF(ISERROR(VLOOKUP(A3,Sheet1!$A$1:$B$3,2,FALSE)),""No"",IF(ISERROR(FIND(""obama"", VLOOKUP(A3,Sheet1!$A$1:$B$3,2,FALSE))), ""No"", ""Yes""))"</pre>
So to answer some of your questions. This code enters the formula into cell B3 on sheet2. Then the formula will use the question in column A to search for the same question in sheet1.range("$A$1:$B$3"). It then looks within the text to see if it has a match for obama, is TRUE then returns a "Yes", If FALSE then it returns "No"

Great help Guerillaunit! This is a HUGE help to me

Cheers,

Dean