How to search a Cell Content within a Range in different Excel Sheets

Question

I'm searching for a formula (If it exists) for the following Scenario: In a workbook, I have Sheet "A" and "B" In Sheet "A", on Cell "A1", I have "Rogers" In sheet "B", on cells "B1":"B20" i have a series of "FirstName LastNames"

What I'm Looking for is to run a search of Cell "A1" in book "A" in the range "B1:B2O" in book "B", if the content of Cell "A1" is found within that range, to return "Yes", and if it's not found, to return "No".

I have tried several combinations of SEARCH, FIND, ISNUMBER, IF, IF ERROR formulas, but none have worked for me, which led me to believe, I'm probably nto typing the correct formula.

Any help is highly appreciated.

Answer 1

There may be a prettier way to do this, but with an Array formula (CSE formula) you can do:

 =IF(SUM(IF(NOT(ISERROR(FIND(A1,Sheet2!B1:B20))),1,0))>0, "Yes", "No")

When entering that in press Ctrl+Shift+Enter. This will test each cell in the B1:B20 range to see if it contains A1. If it does it will not throw a #VALUE error. The inside IF will then report 1 which will be summed as more are found. At the end, the outer IF will see if the number is greater than 0 and will say Yes or No.

Answer 2

A VBA approach could also be used. You would create a subroutine (by typing ALT+F11 while the workbook is open) and programmatically tell the macro what to search for and what to return. The only real advantage of a coding approach over a formula is that it's easier to read through and understand what's supposed to happen, but it's not as efficient.

Example:

Sub FindLast()

Dim EApp as Excel.Application
Dim ThisBook as Excel.Workbook
Dim ShtA as Excel.Worksheet
Dim ShtB as Excel.Worksheet
Dim SrchR as Excel.Range
Dim srchStr as String
Dim FindR as Excel.Range
Dim foundVal as Boolean

foundVal = False

Set EApp = GetObject("Excel.Application")
Set ThisBook = EApp.ActiveWorkbook
Set ShtA = ThisBook.Worksheets("A")
Set ShtB = ThisBook.Worksheets("B")
Set SrchR = ShtA.Range("$A$1")
srchStr = SrchR.Value
Set FindR = ShtB.Range("$B$1:$B$20")

For Each VarRange in FindR
  If InStr(VarRange.Value, srchStr) > 0 Then
    foundVal = True
  End If
Next

If foundVal = True Then
  SrchR.Offset(0,1).Value = "Yes"
Else
  SrchR.Offset(0,1).Value = "No"
End If

End Sub