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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

to reference the first row, or

Please Login or Register  to view this content.

to reference the third row.

It would be nice if I could use

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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!

I believe you want the INDEX() function.

Basically the INDEX() function gives a range (or grid) and then you specify which row and column number you want within that range.

For example: INDEX(A1:E5,3,0) would reference A3:E3 while INDEX(A1:E5,3,3) would reference C3