Formula Referencing Columns By Date
 

Hi, I'm trying to get a formula to reference a column for values depending
on the corresponding date headers. My layout is below

In A2 is TODAY()
In J4 thru AJ4 is =IF(J8<"",I4+1,"")
In I4 is 11/1/2009
In E7 is =J7/J28
In E8 is =J8/J28
In E9 is =J9/J28
Etc...

What I'd like to do is to have the formula look at A2 and find the
corresponding date in J4 Thru AJ4 so that it'll only use the numbers in a
given column that are derrived from today only.

As you can tell my formula is simple but I'm beginging to think what I am
looking for is a tad more.


Thanks in Advance,
Rob

Formula Referencing Columns By Date
 

I am not sure I understood you query correctly.

The below will return the column index
=MATCH(A2,J4:AJ4,0)

Using the below two formulas you can refer to the 3rd row of the array
J4:AJ100 which is row number 6

=HLOOKUP(A2,$J$4:$AJ$100,3)
=INDEX($J4:$AJ$100,3,MATCH($A$2,$J$4:$AJ$4,0))



If this post helps click Yes
---------------
Jacob Skaria


"Rob" wrote:

Hi, I'm trying to get a formula to reference a column for values depending
on the corresponding date headers. My layout is below

In A2 is TODAY()
In J4 thru AJ4 is =IF(J8<"",I4+1,"")
In I4 is 11/1/2009
In E7 is =J7/J28
In E8 is =J8/J28
In E9 is =J9/J28
Etc...

What I'd like to do is to have the formula look at A2 and find the
corresponding date in J4 Thru AJ4 so that it'll only use the numbers in a
given column that are derrived from today only.

As you can tell my formula is simple but I'm beginging to think what I am
looking for is a tad more.


Thanks in Advance,
Rob

Formula Referencing Columns By Date
 

This formula works perfectly!

=INDEX($J4:$AJ$100,3,MATCH($A$2,$J$4:$AJ$4,0))

Thanks!!!!!!!


"Jacob Skaria" wrote:

I am not sure I understood you query correctly.

The below will return the column index
=MATCH(A2,J4:AJ4,0)

Using the below two formulas you can refer to the 3rd row of the array
J4:AJ100 which is row number 6

=HLOOKUP(A2,$J$4:$AJ$100,3)
=INDEX($J4:$AJ$100,3,MATCH($A$2,$J$4:$AJ$4,0))



If this post helps click Yes
---------------
Jacob Skaria


"Rob" wrote:

Hi, I'm trying to get a formula to reference a column for values depending
on the corresponding date headers. My layout is below

In A2 is TODAY()
In J4 thru AJ4 is =IF(J8<"",I4+1,"")
In I4 is 11/1/2009
In E7 is =J7/J28
In E8 is =J8/J28
In E9 is =J9/J28
Etc...

What I'd like to do is to have the formula look at A2 and find the
corresponding date in J4 Thru AJ4 so that it'll only use the numbers in a
given column that are derrived from today only.

As you can tell my formula is simple but I'm beginging to think what I am
looking for is a tad more.


Thanks in Advance,
Rob