View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
~L ~L is offline
external usenet poster
 
Posts: 177
Default Date range lookup....tough one!

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
A500:D500 (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),D ATEVALUE(LEFT($A$1:$X$1,8)),1))

Still using CTRL+SHIFT+ENTER


"deeds" wrote:

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?



"deeds" wrote:

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" wrote:

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!