Hi, i am working on a user defined function.
The function has 2 tasks:
1st to identify the name range out of 21 name ranges basis user input (user input 1) and use it in set range = variable (this is where i need help)
I think here directly referring to the range name also will need indirect function support which i am again not able to do and need help.
2nd then to match the string (user input 2) in the name range defined above and return the adjacent columns (which i am able to do).

The function code i have written does pretty much the task 2, but with only one name range which stays static.

Code begins:
------------------------------------------------------------------------------------
Function lookupdiv(dimd As String) As Variant
Dim rngs As Range
Dim m As Integer

'--- Load the table (range) from the global name
'--- the problem is in the below code line where i do not know how to set rngs to
'----multiple names basis some user input. Here i am able to use only named range called
'-----"Dim_2G3G". However, i have 21 name ranges like this. Also, i think the range reference
'------will require indirect functionality to be used

Set rngs = ActiveWorkbook.Names("Dim_2G3G").RefersToRange


'--- Iterate through all members in the table
'--- (skip first header row),

For m = 2 To rngs.Rows.Count
If (dimd = rngs(m, 1)) Then
'--- A match has been found. Load data end exit the function
lookupdiv = rngs(m, 3)
Exit Function
End If
Next m

'--- if the exeution reach this point, there was no match
'--- Return "error" value
lookupdiv = 0
End Function
--------------------------------------------------------------
Code ends

This is my first post in the forum and i am eagerly looking forward to the support. Thanks