Hi, here's a challenge beyond the capacity of my thinker!

I have the formulas below in the respective cells:
In cell E3 is: =INDEX(E$101:E$981,MATCH(I3,I$101:I$981,),)
In cell E4 is: =INDEX(E$101:E$981,MATCH(I4,I$101:I$981,),)
These continue in Col E down to Row 96. Col E is where the final calculations reside that I'm interested in.

These formulas work fine, but I'd like to add some functionality, and this is where it gets complicated for me.

In E2 is a date that I enter. From I2:Z2 are also dates one week apart, with the weekly data beneath these headings. So each column represents a weeks worth of data, headed by the Friday's date in Row 2 above each column. Column I is the most recent data, and I add a new column each week, inserting a new column so the most recent data is always in Col I.

The formulas above work fine as long at I'm only focused on Col I (the most recent data). But I would like to add the ability to the above formulas so that I can change the date in E2, and the column in focus will change instead from Col I, to the column with a date heading that matches E2.

E.g. If I change E2 to 23 Jan, I would like the column in focus to change from I (as per the present formula) and headed by 13 Feb, to Col L (headed by 23 Jan in L2). Changing this column of focus, will therefore change the final results that I want to see in Col E. I can easily do this manually by just changing the I to L in the above formulas, but would be nice if I can have it do it automatically, based on the date I type into E2.

I have tried to focus on ways to combine another Match, along with Substitute and Address, or Concatenate, to look for ways to change the I to L in this part of the forumula MATCH(I3,I$101:I$981,),) but I'm not getting anywhere.

Any thoughts on how to proceed with this would be much appreciated.

Thanks!
Harold

Hi,

Try this

=INDEX(E$101:E$981,MATCH(I3,I$101:Z$981,),MATCH($E $2,$I$2:$Z$2,0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Harold Good" wrote:

Hi, here's a challenge beyond the capacity of my thinker!

I have the formulas below in the respective cells:
In cell E3 is: =INDEX(E$101:E$981,MATCH(I3,I$101:I$981,),)
In cell E4 is: =INDEX(E$101:E$981,MATCH(I4,I$101:I$981,),)
These continue in Col E down to Row 96. Col E is where the final calculations reside that I'm interested in.

These formulas work fine, but I'd like to add some functionality, and this is where it gets complicated for me.

In E2 is a date that I enter. From I2:Z2 are also dates one week apart, with the weekly data beneath these headings. So each column represents a weeks worth of data, headed by the Friday's date in Row 2 above each column. Column I is the most recent data, and I add a new column each week, inserting a new column so the most recent data is always in Col I.

The formulas above work fine as long at I'm only focused on Col I (the most recent data). But I would like to add the ability to the above formulas so that I can change the date in E2, and the column in focus will change instead from Col I, to the column with a date heading that matches E2.

E.g. If I change E2 to 23 Jan, I would like the column in focus to change from I (as per the present formula) and headed by 13 Feb, to Col L (headed by 23 Jan in L2). Changing this column of focus, will therefore change the final results that I want to see in Col E. I can easily do this manually by just changing the I to L in the above formulas, but would be nice if I can have it do it automatically, based on the date I type into E2.

I have tried to focus on ways to combine another Match, along with Substitute and Address, or Concatenate, to look for ways to change the I to L in this part of the forumula MATCH(I3,I$101:I$981,),) but I'm not getting anywhere.

Any thoughts on how to proceed with this would be much appreciated.

Thanks!
Harold

Hi, this didn't work, it ends up with #N/A. When I click thru it with the
Evaluate Formula button, it seems to begin with matching I3. But I think it
first needs to match the E2 to the correct column prior to matching Row 3.

In Evaluate Formula, the #N/A appears while matching I3 in I$101:Z$981

Harold

===============
"Shane Devenshire" wrote in
message ...
Hi,

Try this

=INDEX(E$101:E$981,MATCH(I3,I$101:Z$981,),MATCH($E $2,$I$2:$Z$2,0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Harold Good" wrote:

Hi, here's a challenge beyond the capacity of my thinker!

I have the formulas below in the respective cells:
In cell E3 is: =INDEX(E$101:E$981,MATCH(I3,I$101:I$981,),)
In cell E4 is: =INDEX(E$101:E$981,MATCH(I4,I$101:I$981,),)
These continue in Col E down to Row 96. Col E is where the final
calculations reside that I'm interested in.

These formulas work fine, but I'd like to add some functionality, and
this is where it gets complicated for me.

In E2 is a date that I enter. From I2:Z2 are also dates one week apart,
with the weekly data beneath these headings. So each column represents a
weeks worth of data, headed by the Friday's date in Row 2 above each
column. Column I is the most recent data, and I add a new column each
week, inserting a new column so the most recent data is always in Col I.

The formulas above work fine as long at I'm only focused on Col I (the
most recent data). But I would like to add the ability to the above
formulas so that I can change the date in E2, and the column in focus
will change instead from Col I, to the column with a date heading that
matches E2.

E.g. If I change E2 to 23 Jan, I would like the column in focus to change
from I (as per the present formula) and headed by 13 Feb, to Col L
(headed by 23 Jan in L2). Changing this column of focus, will therefore
change the final results that I want to see in Col E. I can easily do
this manually by just changing the I to L in the above formulas, but
would be nice if I can have it do it automatically, based on the date I
type into E2.

I have tried to focus on ways to combine another Match, along with
Substitute and Address, or Concatenate, to look for ways to change the I
to L in this part of the forumula MATCH(I3,I$101:I$981,),) but I'm not
getting anywhere.

Any thoughts on how to proceed with this would be much appreciated.

Thanks!
Harold

the #N/A appears while matching I3 in I$101:Z$981

The lookup_array must be a 1 dimensional array (single row/column).

See if this sample helps:

...........A..........B..........C..........D
1....................X..........Y..........Z
2........5..........2...........6..........3
3........3..........1...........5..........4
4........7..........4...........8..........6

You want to lookup 3 and Y.

F1 = 3
G1 = Y

=VLOOKUP(F1,A1:D4,MATCH(G1,A1:D1,0),0)

=INDEX(B2:D4,MATCH(F1,A2:A4,0),MATCH(G1,B1:D1,0))

--
Biff
Microsoft Excel MVP


"Harold Good" wrote in message
...
Hi, this didn't work, it ends up with #N/A. When I click thru it with the
Evaluate Formula button, it seems to begin with matching I3. But I think
it first needs to match the E2 to the correct column prior to matching Row
3.

In Evaluate Formula, the #N/A appears while matching I3 in I$101:Z$981

Harold

===============
"Shane Devenshire" wrote in
message ...
Hi,

Try this

=INDEX(E$101:E$981,MATCH(I3,I$101:Z$981,),MATCH($E $2,$I$2:$Z$2,0))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Harold Good" wrote:

Hi, here's a challenge beyond the capacity of my thinker!

I have the formulas below in the respective cells:
In cell E3 is: =INDEX(E$101:E$981,MATCH(I3,I$101:I$981,),)
In cell E4 is: =INDEX(E$101:E$981,MATCH(I4,I$101:I$981,),)
These continue in Col E down to Row 96. Col E is where the final
calculations reside that I'm interested in.

These formulas work fine, but I'd like to add some functionality, and
this is where it gets complicated for me.

In E2 is a date that I enter. From I2:Z2 are also dates one week apart,
with the weekly data beneath these headings. So each column represents a
weeks worth of data, headed by the Friday's date in Row 2 above each
column. Column I is the most recent data, and I add a new column each
week, inserting a new column so the most recent data is always in Col I.

The formulas above work fine as long at I'm only focused on Col I (the
most recent data). But I would like to add the ability to the above
formulas so that I can change the date in E2, and the column in focus
will change instead from Col I, to the column with a date heading that
matches E2.

E.g. If I change E2 to 23 Jan, I would like the column in focus to
change from I (as per the present formula) and headed by 13 Feb, to Col
L (headed by 23 Jan in L2). Changing this column of focus, will
therefore change the final results that I want to see in Col E. I can
easily do this manually by just changing the I to L in the above
formulas, but would be nice if I can have it do it automatically, based
on the date I type into E2.

I have tried to focus on ways to combine another Match, along with
Substitute and Address, or Concatenate, to look for ways to change the I
to L in this part of the forumula MATCH(I3,I$101:I$981,),) but I'm not
getting anywhere.

Any thoughts on how to proceed with this would be much appreciated.

Thanks!
Harold