Excel 2007 : Referencing a row of a named range.

Hey guys,


I have a function that has a range (a single row) as an input. I can determine the index of the row that I need, and can manually select it from the named range, but I need to be able to reference it via some other method.

To help visualize what I am trying to do, here is the format of the named range that I wish to reference:

INDEX_COLUMN       DATA_COLUMNS
      1          A   B   C   D   E
      2          F   G   H   I   J
      3          K   L   M   N   O
      4          P   Q   R   S   T
      5          U   V   W   X   Y

For the sake of this question, let "A1" = 1, "A2" = 2, and so on, with "B1" = A, "C1" = B, etc.

I need to be able to input to my function something other than

function(..., B1:D1,...)

to reference the first row, or

function(..., B3:D3,...)

to reference the third row.

It would be nice if I could use

function(..., NamedRange.Row(index),...)

or something like that.

The reason behind this is that this table is a lookup table and the formula is being used on a different sheet where the locations of the function calls do not necessarily correspond to the positions needed from the lookup table,
e.g.

function(..., Row 3,...)
function(..., Row 12,...)
function(..., Row 1,...)

and the cells containing the function calls are occasionally sorted. (The order of the rows in the lookup table never changes.)

I will need to use my function for several thousand lookups but the lookup table might only have 50 rows. Also, if it helps, the lookup table is of known width.


Thanks in advance for any help on this!