searching data from 1 column in multiple tab

[LadyHarper]

Good Afternoon,
on a spreadsheet, in column G, I need a formula that will look at text in column B and search columns A on 4 different tabs.

If it finds a cell that contains the text from column B, then paste that cells contents in column G.

Formula or VBA

 

[mse330]

Hi LadyHarper,

Try the below UDF ... In cell G1, type the following formula =FindMyWord(B1)

Function FindMyWord(Txt$) As String

Dim ws As Worksheet, Rg As Range

For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) '<-- change sheet names
   Set Rg = ws.Columns(1).Find(Txt, lookat:=xlPart)
   If Not Rg Is Nothing Then
      FindMyWord = Rg.Value
      Exit Function
   End If
Next

End Function

 

[LadyHarper]

Thank you, this worked!

 

[DanteAmor]

With formula in G1:

=IFERROR(VLOOKUP("*"&B1&"*",Sheet1!A:A,1,0),IFERROR(VLOOKUP("*"&B1&"*",Sheet2!A:A,1,0),
IFERROR(VLOOKUP("*"&B1&"*",Sheet3!A:A,1,0),IFERROR(VLOOKUP("*"&B1&"*",Sheet4!A:A,1,0),"Not Found"))))

 

[LadyHarper]

Hi LadyHarper,

Try the below UDF ... In cell G1, type the following formula =FindMyWord(B1)

Function FindMyWord(Txt$) As String

Dim ws As Worksheet, Rg As Range

For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) '<-- change sheet names
   Set Rg = ws.Columns(1).Find(Txt, lookat:=xlPart)
   If Not Rg Is Nothing Then
      FindMyWord = Rg.Value
      Exit Function
   End If
Next

End Function

Hi, This worked. Is there away to have it continually update. So as I make changes in the sheets, can the data in cell G1 update right away?

 

[Joe4]

Hi, This worked. Is there away to have it continually update. So as I make changes in the sheets, can the data in cell G1 update right away?

See if this change does it:

Function FindMyWord(Txt$) As String

Application.Volatile

Dim ws As Worksheet, Rg As Range

For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) '<-- change sheet names
   Set Rg = ws.Columns(1).Find(Txt, lookat:=xlPart)
   If Not Rg Is Nothing Then
      FindMyWord = Rg.Value
      Exit Function
   End If
Next

End Function

 

[LadyHarper]

See if this change does it:

Function FindMyWord(Txt$) As String

Application.Volatile

Dim ws As Worksheet, Rg As Range

For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) '<-- change sheet names
   Set Rg = ws.Columns(1).Find(Txt, lookat:=xlPart)
   If Not Rg Is Nothing Then
      FindMyWord = Rg.Value
      Exit Function
   End If
Next

End Function

This does not update it if a change is made.

 

[DanteAmor]

Did you try the formula from post #4?