Help needed on date matching and cell reference.

[GBExcel via OfficeKB.com]

Hi,

I need a formula to return the value of a cell from a nearby column, based on
a matching of the months and years within a range of dates to the months and
years within a given date. Here is the setup:

Cell BA58 contains the given date.

Column AP, starting in Cell AP59 and going down to AP2000 contains the array
of dates that need to be evaluated to find which date matches the given date
in Cell BA58

Column AR, starting in Cell AR59 and going down to AR2000 contains the array
of numbers from which the result must be displayed. If a match is found
between the given date in Cell BA58 and a date within the array of dates in
AP59:2000, then the result should be the cell value taken from the same row
from column of numbers AR59:2000.

The dates from AP59 and down look like this:
12 Dec 2009
14 Jan 2010
12 Feb 2010
12 Mar 2010
12 Apr 2010
12 May 2010
Etc.

The given date in BA58 looks like this: 03/2009

The numbers from AR59 and down look like this:
0
9
8
7
4
3
Etc.


This is probably easy when one knows how, but I don't -- so I am stuck.

Help appreciated.

GBExcel

 

[T. Valko]

Will the date in BA58 appear *only once* (or possibly, not at all) in
AP59:AP2000?

 

[GBExcel via OfficeKB.com]

I'm not sure that I understand your question, but I hope that this helps.

The date in BA58 is derived as follows:

=TEXT(TODAY(),"mm/yyyy")

It could also be any date such as
=TEXT([Any date],"mm/yyyy")

I am using BA58 as a control so that I can call data and produce reports
based on the date in BA58.

Thanking you.
GBExcel

Will the date in BA58 appear *only once* (or possibly, not at all) in
AP59:AP2000?

Hi,

[quoted text clipped - 46 lines]

GBExcel

 

[T. Valko]

Here's what you posted:

----------
Cell BA58 contains the given date.

Column AP, starting in Cell AP59 and going down to AP2000 contains the array
of dates that need to be evaluated to find which date matches the given date
in Cell BA58
----------

My question to you is:

Will the date in cell BA58 appear *only once* in the range AP59:AP2000?

The date in BA58 is derived as follows:
=TEXT(TODAY(),"mm/yyyy")

Let's assume BA58 = 012010

In the range AP59:AP2000, will there be *only one instance* of a date in
January 2010? Based on your posted sample data:

----------
12 Dec 2009
14 Jan 2010
12 Feb 2010
12 Mar 2010
12 Apr 2010
12 May 2010
Etc.
----------

There is *only one* date that meets the condition. However, the amount of
sample data posted might not be a true representative sample of your real
data.

--
Biff
Microsoft Excel MVP

I'm not sure that I understand your question, but I hope that this helps.

The date in BA58 is derived as follows:

=TEXT(TODAY(),"mm/yyyy")

It could also be any date such as
=TEXT([Any date],"mm/yyyy")

I am using BA58 as a control so that I can call data and produce reports
based on the date in BA58.

Thanking you.
GBExcel

Will the date in BA58 appear *only once* (or possibly, not at all) in
AP59:AP2000?

Hi,

[quoted text clipped - 46 lines]

GBExcel

 

[GBExcel via OfficeKB.com]

Will the date in cell BA58 appear *only once* in the range AP59:AP2000?

Yes, that is correct.

GBExcel

 

[T. Valko]

Try this...

=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)

 

[GBExcel via OfficeKB.com]

Thank you. Will try it.

The double minus (as in "--") is new to me. What does it do?

GBExcel

 

[T. Valko]

=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)

The double minus (as in "--") is new to me.
What does it do?

The SUMPRODUCT function works with numbers. It multiplies arrays of numbers
together then returns the sum total of that multiplication. For example:

1...3
2...4
6...2

=SUMPRODUCT(A1:A3,B1:B3) = 23

(1*3)+(2*4)+(6*2) = 23

In this formula:

=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)

We have one array of numbers in the range AR59:AR2000. So, we need to come
up with another array of numbers in order for the SUMPRODUCT function to do
what it does.

This expression will return an array of either TRUE or FALSE:

TEXT(AP59:AP2000,"mmyyyy")=BA58

Based on your posted sample data we get:

BA58 = 012010

12 Dec 2009 = 122009 = 012010 = FALSE
14 Jan 2010 = 012010 = 012010 = TRUE
12 Feb 2010 = 022010 = 012010 = FALSE
12 Mar 2010 = 032010 = 012010 = FALSE
12 Apr 2010 = 042010 = 012010 = FALSE
12 May 2010 = 052010 = 012010 = FALSE
etc
etc

We need to convert those logicals to numbers. One way to do that is to use
the double unary minus --.

--TRUE = 1
--FALSE = 0

--(TEXT(AP59:AP2000,"mmyyyy")=BA58)

Then we get an array of 1s or 0s:

12 Dec 2009 = 122009 = 012010 = --FALSE = 0
14 Jan 2010 = 012010 = 012010 = --TRUE = 1
12 Feb 2010 = 022010 = 012010 = --FALSE = 0
12 Mar 2010 = 032010 = 012010 = --FALSE = 0
12 Apr 2010 = 042010 = 012010 = --FALSE = 0
12 May 2010 = 052010 = 012010 = --FALSE = 0
etc
etc

Now we have 2 arrays of numbers, the array of 1s and 0s and the array of
numbers in the range AR59:AR2000. Those arrays might look something like
this:

0...10
1...14
0...0
0...7
0...22
0...19
etc
etc

The arrays are multiplied together:

0*10 = 0
1*14 = 14
0*0 = 0
0*7 = 0
0*22 = 0
0*19 = 0
etc
etc

SUMPRODUCT then sums the results of that multiplication:

SUMPRODUCT({0;14;0;0;0;0}) = 14

So, lookup "012010" in the range of dates AP59:AP2000 and return the
corresponding numeric value from AR59:AR2000:

=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)

=14

For more on SUMPRODUCT see this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[GBExcel via OfficeKB.com]

Thank you,

You've gone more than the extra mile.

Appreciate it.

GBExcel

 

[T. Valko]

You're welcome. Thanks for the feedback!