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