I have 3 columns with the titles

01/01/08-03/31/08 04/01/08-07/31/08 08/01/08-12/31/08
CAT DOG FISH

Now, I have a report with a specific monthly date (0408) column titles. I
am trying to create a formula that finds the date (0408) in the appropriate
column and then bring back the data I choose (I can get that). Something
like HLookup? but how do I possibly get it to look at the date range (which
is text) and determine if it is within the range? Maybe I am going about
this wrong....so any ideas would be appreciated. I can clarify more if
needed....this may take some work....Thanks in advance!

Thinking more...let me try to clarify....

Imagine a report with Jan-Dec across the top with Sales etc down column A.
Now, I want to lookup the data using a supplier name "ABC"...however that
supplier may change from month to month...so I need to somehow look in the
correct column for the supplier name. It is maintained by adding a column
for instance: 01/01/08-03/01/08 would be "ABC"...but 04/01/08-07/31/08 would
be "XYZ" Now from Jan-Mar...I want the formula to look in the column
01/01/08-03/01/08...somehow I need the formula to look at the monthly date in
report and go to correct column based on the range...this is stumping me and
I am having trouble explaining...let me know if someone is
understanding...Thanks again...

"deeds" wrote:

I have 3 columns with the titles

01/01/08-03/31/08 04/01/08-07/31/08 08/01/08-12/31/08
CAT DOG FISH

Now, I have a report with a specific monthly date (0408) column titles. I
am trying to create a formula that finds the date (0408) in the appropriate
column and then bring back the data I choose (I can get that). Something
like HLookup? but how do I possibly get it to look at the date range (which
is text) and determine if it is within the range? Maybe I am going about
this wrong....so any ideas would be appreciated. I can clarify more if
needed....this may take some work....Thanks in advance!

DATEVALUE() takes dates stored as text and converts it to a number.

=INDEX($A$1:$X$2,2,MATCH(DATEVALUE(A4),DATEVALUE(L EFT($A$1:$X$1,8)),1))

entered as an array formula using ctrl+shift+enter will return the 2nd row
value where the textual date in A4 (will fail if A4 is a number/actual date)
is less than or equal to the largest match in A1 to X1 (which must be in
ascending order).

Is this what you needed?

"deeds" wrote:

Thinking more...let me try to clarify....

Imagine a report with Jan-Dec across the top with Sales etc down column A.
Now, I want to lookup the data using a supplier name "ABC"...however that
supplier may change from month to month...so I need to somehow look in the
correct column for the supplier name. It is maintained by adding a column
for instance: 01/01/08-03/01/08 would be "ABC"...but 04/01/08-07/31/08 would
be "XYZ" Now from Jan-Mar...I want the formula to look in the column
01/01/08-03/01/08...somehow I need the formula to look at the monthly date in
report and go to correct column based on the range...this is stumping me and
I am having trouble explaining...let me know if someone is
understanding...Thanks again...

"deeds" wrote:

I have 3 columns with the titles

01/01/08-03/31/08 04/01/08-07/31/08 08/01/08-12/31/08
CAT DOG FISH

Now, I have a report with a specific monthly date (0408) column titles. I
am trying to create a formula that finds the date (0408) in the appropriate
column and then bring back the data I choose (I can get that). Something
like HLookup? but how do I possibly get it to look at the date range (which
is text) and determine if it is within the range? Maybe I am going about
this wrong....so any ideas would be appreciated. I can clarify more if
needed....this may take some work....Thanks in advance!

Thanks..however...not there yet. Let's say I have 3 columns each has a
different column heading 08/01/07-12/31/07 and 01/01/08-07/31/08 and
08/01/08-12/31/08. Now I want a formula to lookup the date 09/01/07 and
determine which column it falls. So in this case it would fall in the first
column (08/01/07-12/31/07). Like an HLookup function possibly, but it needs
to find it in the range. Thoughts?...

"~L" wrote:

DATEVALUE() takes dates stored as text and converts it to a number.

=INDEX($A$1:$X$2,2,MATCH(DATEVALUE(A4),DATEVALUE(L EFT($A$1:$X$1,8)),1))

entered as an array formula using ctrl+shift+enter will return the 2nd row
value where the textual date in A4 (will fail if A4 is a number/actual date)
is less than or equal to the largest match in A1 to X1 (which must be in
ascending order).

Is this what you needed?

"deeds" wrote:

Thinking more...let me try to clarify....

Imagine a report with Jan-Dec across the top with Sales etc down column A.
Now, I want to lookup the data using a supplier name "ABC"...however that
supplier may change from month to month...so I need to somehow look in the
correct column for the supplier name. It is maintained by adding a column
for instance: 01/01/08-03/01/08 would be "ABC"...but 04/01/08-07/31/08 would
be "XYZ" Now from Jan-Mar...I want the formula to look in the column
01/01/08-03/01/08...somehow I need the formula to look at the monthly date in
report and go to correct column based on the range...this is stumping me and
I am having trouble explaining...let me know if someone is
understanding...Thanks again...

"deeds" wrote:

I have 3 columns with the titles

01/01/08-03/31/08 04/01/08-07/31/08 08/01/08-12/31/08
CAT DOG FISH

Now, I have a report with a specific monthly date (0408) column titles. I
am trying to create a formula that finds the date (0408) in the appropriate
column and then bring back the data I choose (I can get that). Something
like HLookup? but how do I possibly get it to look at the date range (which
is text) and determine if it is within the range? Maybe I am going about
this wrong....so any ideas would be appreciated. I can clarify more if
needed....this may take some work....Thanks in advance!

Does

=INDEX($A$1:$X$2,1,MATCH(DATEVALUE(A4),DATEVALUE(L EFT($A$1:$X$1,8)),1))

produce the desired result? (Still entered with CTRL+SHIFT+ENTER and the
same restrictions from before)

"deeds" wrote:

Thanks..however...not there yet. Let's say I have 3 columns each has a
different column heading 08/01/07-12/31/07 and 01/01/08-07/31/08 and
08/01/08-12/31/08. Now I want a formula to lookup the date 09/01/07 and
determine which column it falls. So in this case it would fall in the first
column (08/01/07-12/31/07). Like an HLookup function possibly, but it needs
to find it in the range. Thoughts?...

"~L" wrote:

DATEVALUE() takes dates stored as text and converts it to a number.

=INDEX($A$1:$X$2,2,MATCH(DATEVALUE(A4),DATEVALUE(L EFT($A$1:$X$1,8)),1))

entered as an array formula using ctrl+shift+enter will return the 2nd row
value where the textual date in A4 (will fail if A4 is a number/actual date)
is less than or equal to the largest match in A1 to X1 (which must be in
ascending order).

Is this what you needed?

"deeds" wrote:

Thinking more...let me try to clarify....

Imagine a report with Jan-Dec across the top with Sales etc down column A.
Now, I want to lookup the data using a supplier name "ABC"...however that
supplier may change from month to month...so I need to somehow look in the
correct column for the supplier name. It is maintained by adding a column
for instance: 01/01/08-03/01/08 would be "ABC"...but 04/01/08-07/31/08 would
be "XYZ" Now from Jan-Mar...I want the formula to look in the column
01/01/08-03/01/08...somehow I need the formula to look at the monthly date in
report and go to correct column based on the range...this is stumping me and
I am having trouble explaining...let me know if someone is
understanding...Thanks again...

"deeds" wrote:

I have 3 columns with the titles

01/01/08-03/31/08 04/01/08-07/31/08 08/01/08-12/31/08
CAT DOG FISH

Now, I have a report with a specific monthly date (0408) column titles. I
am trying to create a formula that finds the date (0408) in the appropriate
column and then bring back the data I choose (I can get that). Something
like HLookup? but how do I possibly get it to look at the date range (which
is text) and determine if it is within the range? Maybe I am going about
this wrong....so any ideas would be appreciated. I can clarify more if
needed....this may take some work....Thanks in advance!

OUTSTANDING! It works. Now, it returns the column heading...I now need to
somehow work it into a sumproduct formula (which I already have)...so that it
knows which column to look at. So, it needs to look for the correct heading
and look down that column....Thanks again!

"~L" wrote:

Does

=INDEX($A$1:$X$2,1,MATCH(DATEVALUE(A4),DATEVALUE(L EFT($A$1:$X$1,8)),1))

produce the desired result? (Still entered with CTRL+SHIFT+ENTER and the
same restrictions from before)

"deeds" wrote:

Thanks..however...not there yet. Let's say I have 3 columns each has a
different column heading 08/01/07-12/31/07 and 01/01/08-07/31/08 and
08/01/08-12/31/08. Now I want a formula to lookup the date 09/01/07 and
determine which column it falls. So in this case it would fall in the first
column (08/01/07-12/31/07). Like an HLookup function possibly, but it needs
to find it in the range. Thoughts?...

"~L" wrote:

DATEVALUE() takes dates stored as text and converts it to a number.

=INDEX($A$1:$X$2,2,MATCH(DATEVALUE(A4),DATEVALUE(L EFT($A$1:$X$1,8)),1))

entered as an array formula using ctrl+shift+enter will return the 2nd row
value where the textual date in A4 (will fail if A4 is a number/actual date)
is less than or equal to the largest match in A1 to X1 (which must be in
ascending order).

Is this what you needed?

"deeds" wrote:

Thinking more...let me try to clarify....

Imagine a report with Jan-Dec across the top with Sales etc down column A.
Now, I want to lookup the data using a supplier name "ABC"...however that
supplier may change from month to month...so I need to somehow look in the
correct column for the supplier name. It is maintained by adding a column
for instance: 01/01/08-03/01/08 would be "ABC"...but 04/01/08-07/31/08 would
be "XYZ" Now from Jan-Mar...I want the formula to look in the column
01/01/08-03/01/08...somehow I need the formula to look at the monthly date in
report and go to correct column based on the range...this is stumping me and
I am having trouble explaining...let me know if someone is
understanding...Thanks again...

"deeds" wrote:

I have 3 columns with the titles

01/01/08-03/31/08 04/01/08-07/31/08 08/01/08-12/31/08
CAT DOG FISH

Now, I have a report with a specific monthly date (0408) column titles. I
am trying to create a formula that finds the date (0408) in the appropriate
column and then bring back the data I choose (I can get that). Something
like HLookup? but how do I possibly get it to look at the date range (which
is text) and determine if it is within the range? Maybe I am going about
this wrong....so any ideas would be appreciated. I can clarify more if
needed....this may take some work....Thanks in advance!