Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Reference (XP SP3 / General)

    Hello everybody,

    I am searching for a built-in sheetfunction which searches a selected range for a given string and returns a reference to this cell, if the content was found. I could do this by using the following userdefined function, but I wonder if this function is already included.

    <pre>Function FINDCONTENT(SearchString As String, SearchRange As Range) As Range
    Set FINDCONTENT = SearchRange.Find(What:=SearchString)
    End Function
    </pre>


    Thanks for your suggestions in advance

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Reference (XP SP3 / General)

    You can use a combination of INDEX and MATCH for this:

    INDEX(SearchRange, MATCH(SearchValue, SearchRange, 0))

    refers to the cell within SearchRange where SearchValue is found. You can then apply functions such as OFFSET to the result.

    Depending on what exactly you want, the VLOOKUP and HLOOKUP functions may be useful too. Look them up in the Excel help.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Reference (XP SP3 / General)

    Hello Hans,

    thanks for your reply. As ever, you hit the target 100%. This combination perfectly fits my needs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •