Use Cell Contents As Function Array

Hello,

I have a problem which I feel cannot be solved without some complex vba.

I am creating a flexible template to quickly run some basic comparisons across multiple fields with the minimum amount of user manipulation.

I have used the formula below to return the address of a unique range (which there will be many)....

=ADDRESS(MATCH(B11,'Sheet 2'!B:B,0),3,1,1,"Sheet 2")&":"&ADDRESS(LOOKUP(2,1/(Dynamic Named Range=B11),ROW(Dynamic Named Range)),3,1,1)

I have changed some labels for confidentiality purposes.

But imagine cells B1:B10 on Sheet 2 contain the word "Dog". Cell B11 on the active sheet where the formula exits also contains the word "Dog". The Dynamic Named Range refers to text in Column B on Sheet 2. The result is something like...

'Sheet 2'!$C$1:$C$10

Now I have my unique range, the problem is using it without having to manually copy and paste into a function's syntax.

The function I am using is a modified version of the Nth_Occurrence function, the VBA code can be found on Google. The syntax works like this:
Nth_Occurrence(range, lookup value, occurence, row offset, col offset). I am looking for a piece of data, based on a value looked up within the unique range which may or may not occur more than once.

The VBA for the function has been modified from the popular code found on Google to ensure that say for an example a value only occurs twice within a range. If you attempt to find the third occurrence an error value is returned, where as before the function looped back to the beginning of the range to find a third occurrence.

Right so the problem here is, how do I use the contents of the formula first used, to return the unique range, in the Nth_Occurrence function? I have attempted so many different array formulas to no avail. I think the solution may lie in using one of the many old Excel formulas I have not used before to create a particular type of named range.

My method is fine for looking up one value. But data may be dropped into the sheet resulting in there being 100+ unique values each with it's own corresponding unique range. Requiring the user to copy and paste the initial formula appropriately to create all the unique ranges, then paste these ranges into each Nth_Occurrence function, results in no time being saved and opens up the possibility of user error.

For any forum moderators viewing this request, please advise if you would like to see some example data, leave your email and I will contact you. Any thoughts on the matter would be greatly appreciated.