Formula to FIND specific TEXT within a worksheet

Is there a formula that will find specific text (Employee ID) in a worksheet and offset one row down and return the value in the offset cell to another worksheet? I am able to do it with a macro but I think a formula would be easier if it would work.

Thank you for all your help
I use Excel 2007

Research HLookup.

Let's have a look at your code and/or attach a representative workbook…

Below is the code that does work but I would like a formula (if possible) to find specific text and offset one row and return the value in the offset cell, is that possible?

Thank you for your help

Code:

---

Option ExplicitSub FindAllOnBoard()
    'Defining the variables.
    Dim GCell, GCell1, GCell2, GCell3, GCell4, GCell5, GCell6, GCell7 As Range
    Dim Page$, Txt$, Txt1, Txt2, Txt3, Txt4, Txt5, Txt6, Txt7, MyPath$, MyWB$, MySheet$
    Dim DestSh As Worksheet
   
    ThisWorkbook.Sheets("Lookup").Range("B42").ClearContents
   
    Set DestSh = ThisWorkbook.Worksheets("Lookup")


    'The text for which to search.
    Txt = "Employee ID"
   
    'Turn off screen updating, and then open the target workbook.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    'Search for the specified text
    Set GCell = Sheets("CognosIntermData").Cells.Find(Txt, LookIn:=xlValues, LookAt:=xlWhole) '.Cells.Find(Txt) 'ActiveSheet


    'Record the address of the data, along with the date, in the current workbook.
    'With ThisWorkbook.Sheets("Lookup Data").Range("B1")
   
    ThisWorkbook.Sheets("CognosIntermData").Activate
   
    If GCell <> "" Then
    DestSh.Range("B42").Value = GCell.Offset(1, 0)
 
      End If
   
  End Sub

---

…from which I glean you want to find a value within an entire sheet, whichever column and row it's in.
Will the sought text appear more than once in the sheet? If so which one should we take (eg. it's found in both cells C2 and B3; which one to take?)
Are we only to use the built-in functions in the formula? If so which version of Excel are you using? Does it have any of the dynamic functions such as FILTER, UNIQUE?
Would it be OK to create a User defined function in a macro that might take the form of a formula on a sheet such as?:
=FindInRange("Employee ID",CognosIntermData!A1:T1000)
Bedtime here.

p45cal,

Thank you for responding, I have come up with a formula (=ADDRESS(MAX(ROW(CognosIntermData!1:100)*(IF(CognosIntermData!1:100=B37,1, 0))),MAX(COLUMN(CognosIntermData!$1:$100)*IF(CognosIntermData!1:100=B37,1,0 )))) that finds the location where the text is but I am unfamiliar with ADDRESS and how to use the returned address/cell location and get it to OFFSET one row to get the value required.

The sought text only appears once on the worksheet. If I can not get a formula to return the OFFSET value, I need a user defined function to complete the task if you have time to let me know how to complete that task.

Thank you for any and all help

Don't bother with ADDRESS; using the same idea try:

Code:

---

=INDEX(CognosIntermData!1:100,MAX(ROW(CognosIntermData!1:100)*(IF(CognosIntermData!1:100=B37,1, 0)))+1,MAX(COLUMN(CognosIntermData!$1:$100)*IF(CognosIntermData!1:100=B37,1,0 )))

---

Alternatively a user-defined function:

Code:

---

Function FindInArray(Sought, myRange)
Set GCell = myRange.Find(Sought, LookIn:=xlValues, LookAt:=xlWhole)
If GCell Is Nothing Then FindInArray = "Not found" Else FindInArray = GCell.Offset(1).Value
End Function

---

The equivalent to the above using this UDF is:

Code:

---

=FindInArray(B37,CognosIntermData!1:100)

---

But please, answer my questions, again: which version of Excel are you using? Does it have any of the dynamic functions such as FILTER, UNIQUE?
An extra question: do you have the LET function available to you on the sheet?
Knowing the answer to this could considerably shorten and make more efficient the first formula.

p45cal,

Your suggestions worked well!

Thank you for all your help.