Date range lookup....tough one!

[deeds]

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!

 

[deeds]

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...

 

[~L]

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

=INDEX($A$1:$X$2,2,MATCH(DATEVALUE(A4),DATEVALUE(LEFT($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]

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]

Does

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

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

 

[deeds]

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!

 

[deeds]

Here is what I currently have: a sumproduct formula that I have identified
the column to look in for a Supplier Name (A1:A400)...however, this column
will change now based on the work done below. Essentially I want to change
the reference of A1:A400...to whichever column I find the date range
match...so it may be column A, B, C, G, H, etc....it needs to find the date
range match and use that column.

 

[deeds]

Alright...here is what I need....

YorN Date 08/30/07-08/30/08 09/01/08-09/01/09 Sales
Y 0808 CAT DOG
500

Lookup values:
Y
0808
CAT (in the 08/30/07-08/30/08 column)
Return sales =500
Now, columns can move around...so I need something that actually looks for
the column heading to determine which column to look in. i.e. Date will not
always fall in column B so instead of having a lookup always look in column B
it needs to say look in column "DATE"....any ideas?

 

[~L]

Since we already have this in INDEX, to match a vendor name as well we can
just add another MATCH.

=INDEX($A$1:$X$2,MATCH(B4,$A$1:$A$400,0),MATCH(DATEVALUE(A4),DATEVALUE(LEFT($A$1:$X$1,8)),1))

Where B4 is the location of the vendor name to be matched, the formula is
entered via CTRL+SHIFT+ENTER, and all aforementioned restrictions apply.

 

[~L]

Will there ever be duplicates that need to be added together, or is that not
a concern?

If you have a table of lookup values (The date, Y/N, Cat/Dog) arranged in
A500500 (or whever):

=INDEX($A$1:$X$2,MATCH(B500&C500&D500,$A$1:$A$400&$B$1:$B$400&$C$1:$C$400,0),MATCH(DATEVALUE(A500),DATEVALUE(LEFT($A$1:$X$1,8)),1))

Still using CTRL+SHIFT+ENTER

 

[deeds]

Thanks...should this return 500? I am having trouble making this work...is
this looking for the column headers and then looking down that column?
Thanks again

 

[deeds]

We are getting close I think...let me try this...

What I want to do is a sumproduct type formula that looks at column headers
to determine which column instead of column reference (A1:A400). Here is the
standard sumproduct formula:
=sumproduct((A2:A400=Y)*(B2:B400=0808)*(C2:C400=CAT)*(D2400))
want it to this:
=sumproduct(("ColumnY/N"=Y)*("DATE"=0808)*("Supp1"=CAT)*(D2400))
So, within the formula it looks for a column named "Y/N" etc... All in
all...I want to replace the column references of A2:A400 to "Y/N" column
header...because the "Y/N" column may change reference...hope this
helps...any ideas?

 

[~L]

That formula is no good now that I re-examine the new form of this data.
Sorry about that.

Try:

=SUMPRODUCT(--($A$2:$A$500="Y"),--($E$2:$E$500))*--(INDIRECT(ADDRESS(ROW(),MATCH(DATEVALUE(B2),DATEVALUE(LEFT($A$1:$D$1,8)),1),1,1))="CAT")

entered with CTRL+SHIFT+ENTER this returned '500' from the provided data
(and expected values from simulated data I created), though you will probably
want to replace Y and CAT with cell references.
The same restrictions from before still apply.

 

[Peo Sjoblom]

In what way would the headers change and why?

If it is within the same table you could use DSUM


Assume the table is called MyTable (A2400)

That you have the headers per your sumproduct formula

then you create a criteria range, assume it is F1:H2 and would look like



Y/N DATE Supp1
Y 808 CAT


then your formula would look like



=DSUM(MyTable,"Sales",F1:H2)

meaning it will sum the entries in the Sales column
when the above criteria in F1:H2 are TRUE

You can put the columns in any order as long as you do
it within that table.






--


Regards,


Peo Sjoblom

 

[~L]

Should have seen this sooner, but this formula fails in the case of any
duplicates on A1:A500="Y". I'll keep working on it.

 

[deeds]

Thanks so much for your help here! I hope we can get this...please see my
latest post with ultimately what I want to happen...I hope I am going about
this the right way! Basically I want a sumproduct formula NOT tied to
specific ranges but by the column headers so instead of A1:A400 I want it
to look in the column = to the header I put in...because the columns may move
around....

 

[~L]

That does help.

This leads me to think you want a dynamic named range described in further
detail by two Excel users far more qualified than myself:
http://www.cpearson.com/excel/named.htm
http://www.contextures.com/xlNames01.html#Dynamic

I'm still working out the details, but this is what I'm aiming for. Tell me
if I'm on the wrong track.

 

[~L]

Sorry for the delay, work stuff.

The answer is to define the following dynamic named ranges:

Date
=OFFSET(INDIRECT(ADDRESS(2,MATCH("Date",$A$1:$X$1,0),1)),0,0,COUNTA($A$2:$A$5000))

Range *note that pasting this won't help because you must read the 'see
below' note
=OFFSET(INDIRECT(ADDRESS(2,MATCH(DATEVALUE(->SEEBELOW<-),DATEVALUE(LEFT($A$1:$X$1,8)),1))),0,0,COUNTA($A$2:$A$5000))

Sales
=OFFSET(INDIRECT(ADDRESS(2,MATCH("Sales",$A$1:$X$1,0),1)),0,0,COUNTA($A$2:$A$5000))

Yorn
=OFFSET(INDIRECT(ADDRESS(2,MATCH("YorN",$A$1:$X$1,0),1)),0,0,COUNTA($A$2:$A$5000))

And for the Sumproduct:
=SUMPRODUCT(--(Yorn="Y"),--(DATEVALUE(Date)>DATEVALUE(->SEEBELOW<-)),--(Range="Cat"),--(Sales))

->SeeBelow<- Notes
For the Range formula, there's no way I can figure around using a reference
to what date exactly you are looking for. When you formulate the sumproduct,
you will have to use a date in any case. Just use the same one in defining
the name as you use in the sumproduct. To make it easy on yourself, set up a
value of tables to the right of (but not in row1) or below your data with the
values you want to lookup, then for each item in the Sumproduct and for
determining the Range, refer to those cells.

Some features of your data must be true for these to work properly, but I
think you'll see where to modify the functions if these are not true:

Data headers are arranged across columns along row 1 from column A to column
X with the names "YorN", "Date" (formatted as text), "Sales", and the date
ranges sorted in ascending order.

Data begins in row 2 and does not exceed row 5000. Data in the date column
is formatted as text (if this is ever not true, remove the DATEVALUE(Date)
and replace with Date, but keep DATEVALUE(LEFT()).

If that explodes, let me know!

 

[deeds]

Thanks!.....this is amazing how much you have helped. Thanks again....I
will make this work...

 

[PXP]

just came across this thread when googling...i've a similar issue and was wondering whether you were able to get your issue resolved...i realise this is a pretty old issue -nonetheless, would appreciate if you could let me know the final status.

Regards
PXP