Search for data and return cell reference

  • Thread starter Thread starter exoticdisease
  • Start date Start date
E

exoticdisease

I would like a forumla that searches my spreadsheet for a word, e.g. "july"
and returns the reference of the cell that it is in, so I can use it for the
rest of a lookup formula.

Any help would be most appreciated,
Rob
 
Try this UDF:

Public Function julyy(inRng As Range) As String
Set r = Application.Caller
ad = r.Address
julyy = ""
For Each rr In inRng
If Not Intersect(rr, r) Is Nothing Then
Else
If rr.Value = "july" Then
julyy = rr.Address
Exit Function
End If
End If
Next
End Function
 
See Help information for the function =Match()

Returns the relative position of an item in an array that matches a
specified value in a specified order. Use MATCH instead of one of the LOOKUP
functions when you need the position of an item in a range instead of the
item itself.
HTH
John
 
A UDF perhaps

this goes in a general module

Public Function FindMe(SrchFor As String, Rng As Range) As String
Set x = Application.Caller
For Each c In Rng
If Not Intersect(c, x) Is Nothing Then
Else
If c.Value = SrchFor Then
FindMe = c.Address
Exit Function
End If
End If
Next
End Function

call with =findme(a1,a2:d100)
Were A1 is what you are looking for

Mike
 
Back
Top