formula help: returning a range that is the 1st row of an existingrange

[AC]

Hi

I have a range defined over a table of data, eg myTestRange = B1:T100
The 1st row of this range is the table field/column headings.

I want to find the index number of a particular field. Eg if I had
the following table:
Name, Address, phonenumber, Age, Sex
data.....

my table range would be defined over the table and I want to know the
col index of say the "AGE" field - which would be 4 in this case as
its the 4th column in this range.

I know the MATCH command returns the col index that I want, but what I
dont know how to do is tell Excel to look over the 1st row of my
range.

My table can get moved around so I can never assume it will start in
col B. I need this to dynamically take my named range and figure out
the index of the field I am interested in.

Thanks in advance
AndyC

(please send a copy of replies to my email also, thanks)

 

[Niek Otten]

=MATCH("AGE",myTestRange INDIRECT(ROW(myTestRange)&":"&ROW(myTestRange)),0)

Note the space after myTestRange

 

[Shane Devenshire]

Hi,

Here is the basic formula:

=MATCH("c",OFFSET(myTestRange,,,1,19),)

 

[Shane Devenshire]

I didn't mention that in my example "c" is simply a column title on the first
row of my test range, you might use "Age".