help with cell referencing in named ranges

[dave]

I have a named range covering about 30 columns of data.
I'd like to lookup within the named range based on values
in the 1st column, and a specific date which is the label
on top of each of the other 29 columns. I have typed in a
new column number as necessary, but I'd like to automate
the column number by looking at a separate cell which
gives the date.

Ex: Dates across the top of the named range are 1/1/90,
1/1/91, etc thru 1/1/2003(in sheet1).

Lets say I have a cell in sheet2(d1)that has the date
1/1/2003. In that same sheet starting in cell a1 I have a
value which correspond to a value in the 1st column of the
named range. In cell b1, I'd like a formula to find the
meeting point between the date in d1(sheet2) and the value
in a1(sheet2) within the named range.

Hopefully I was clear enuff to get help.
tia,
Dave

 

[Peter Atherton]

Dave

I tested this with the following data.

John 01/01/95

Persons 01/01/91 01/01/92 01/01/93 01/01/94 01/01/95
David 29.02 22.18 23.33 71.92 67.54
James 49.14 3.07 6.96 5.65 37.44
Jane 35.56 69.12 26.98 20.46 73.68
Janet 26.61 17.83 26.65 56.32 66.66
Jasmine 36.93 7.69 13.68 44.26 29.45
Jennifer26.22 0.51 61.77 26.90 35.17
John 13.73 13.60 16.71 37.01 40.43
Jonathon74.88 38.87 9.78 26.50 69.43
Jules 46.30 4.17 64.36 42.87 49.19
Julian 50.92 74.60 25.15 6.88 46.46
Juliet 0.48 73.11 38.39 74.29 27.49

I created three named ranges. Dates, Persons and myRng.

MyRng covers all the data
Persons covers column A from the Header "Persons"
Dates is the range A3 to F3

When you use Named ranges it does not matter if the ranges
are in a different sheet from the formula.

The formula to retutn the value is:

=INDEX(MyRng,MATCH($A$1,Persons,0),MATCH($D$1,dates,0))

Regards
Peter