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

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