title: Method to use a UDF to return a Range as String to be used in Lookup Functions
body: <p>For my problem, I have a UDF which accepts a string and using select case it then returns the string reference to the specified array. The return string is a reference to a sheet and range array in the following form: </p>
  
  <blockquote>
    <p>'WorksheetExample'!$B$1:$E$44</p>
  </blockquote>
  
  <p>This UDF is nested within the VLookup/Match in my table to give the correct reference for that case. </p>
  
  <pre><code>Public Function EquipCatRNG(tag As String, tagOrCatRef As String)
  
  'Hardcoded Category Ranges for Matches
  Dim tagRef, catRef As String
  Dim subfix As String
  subfix = Left(tag, 1)
  Select Case subfix
      Case "E"
      'Range for heat exchanger info, 'Heat Exchangers Template (EDS)'!$B$1:$J$53
      tagRef = "'Heat Exchangers Template (EDS)'!$B$1:$J$1"
      catRef = "'Heat Exchangers Template (EDS)'!$B$1:$B$53"
      Case "P"
          If InStr(tag, "Drive") &gt; 0 Then
          tagRef = "'Pumps And Drives Template (EDS)'!$B$44:$G$44"
          catRef = "'Pumps And Drives Template (EDS)'!$B$44:$B$68"
          Else
          tagRef = "'Pumps And Drives Template (EDS)'!$B$1:$G$1"
          catRef = "'Pumps And Drives Template (EDS)'!$B$1:$B$42"
          End If
      Case "T"
          If InStr(tag, "Internals") &gt; 0 Then
          tagRef = "'Towers Template (EDS)'!$B$46:$E$46"
          catRef = "'Towers Template (EDS)'!$B$46:$B$68"
          Else
          tagRef = "'Towers Template (EDS)'!$B$1:$E$1"
          catRef = "'Towers Template (EDS)'!$B$1:$B$44"
          End If
      Case "R"
          tagRef = "'Reactors Template (EDS)'!$B$1:$C$1"
          catRef = "'Reactors Template (EDS)'!$B$1:$B$61"
      Case "H"
          tagRef = "'Heaters Template (EDS)'!$B$1:$C$1"
          catRef = "'Heaters Template (EDS)'!$B$1:$B$45"
      Case "V"
          If InStr(tag, "Internals") &gt; 0 Then
          tagRef = "'Vessels Template (EDS)'!$B$45:$F$45"
          catRef = "'Vessels Template (EDS)'!$B$45:$B$64"
          Else
          tagRef = "'Vessels Template (EDS)'!$B$1:$F$1"
          catRef = "'Vessels Template (EDS)'!$B$1:$B$43"
          End If
  End Select
      tagOrCatRef = LCase(tagOrCatRef)
      If tagOrCatRef = "tag" Then
          EquipCatRNG = tagRef
      Else
          EquipCatRNG = catRef
      End If
  
  End Function
  </code></pre>
  
  <p>Any help is appreciated, I would also be fine with passing the Range directly from the UDF instead of returning a string and then performing some operation to utilize it in the formula. Thank you.</p>
  
  <p>EDIT for Clarity:
  This function passes the String successfully but it is not working as a valid reference in the Vlookup/Match functions in my table. I need to know how I either: 
  1. Perform a formula operation on the returned string post-UDF so that the Vlookup accepts it as a valid reference?
  2. Pass it initially as a range?</p>