Formula to return value in months in date range match

[tammyl]

Hi,
I have been trying to find a formula where a value is returned if the months match.
This is what i have so far but it doesn't work..i get a #value error. i think it's because my lookup array is a range?

Lookup the current month in my column (B$1), if there is a date with matching month in my Holiday range, then return that date.
PBIM is a named range from my "Holiday" sheet.

=INDEX(PBIM,MATCH(Month(B$1),Month(PBIM),0))

Main Data Example

Column A	Column B
	1/12/2014
Board Meeting-Day 1	?
Board Meeting-Day 2	?

Holiday Range

Column A	2014
Board Meeting-Day 1	1/05/2014
Board Meeting-Day 2	2/05/2014
Board Meeting-Day 1	16/12/2014
Board Meeting-Day 2	17/12/2014

I do have task names that match but i don't know how to do the current month check to input the correct date for the relevant month into B2 & B3 cells

i also tried a twist on a vba lookup_concat but couldn't work that either.

Function Date_concat(Search_string As Range, _
  Search_in_col As Range, Return_val_col As Range)
Application.Volatile
Dim i As Long
Dim result As String
Dim value As Variant
Dim fresult As Range
For Each value In Search_string
  value = Month(value)
  If Search_string <> "" Then
    For i = 1 To Search_in_col.Count
      If Search_in_col.Cells(i, 1) = value Then
        result = result & Return_val_col.Cells(i, 1).value & Chr(10)
      End If
    Next i
  End If
Next value
Actual_concat = Trim(result)
End Function

Help!!
Tammy

[Bob Phillips]

Try this

=SUMPRODUCT(--(INDEX(PBIM,0,1)=A1),--(MONTH(INDEX(PBIM,0,2))=MONTH(B1)),INDEX(PBIM,0,2))

[tammyl]

Awesome, thanks. My PBIM date of 3/12/2014 returned into my Period(month) 12 Column.......

When i have blank date cells in the PBIM range, i get a returned result = 0/01/1900 in the columns for months 1 to 11

I tried these formulas below for ISBLANK now get the date 3/12/2014 in my Period 01 column and 0/01/1900 in all other columns. Assuming because i currently only have one date in my PBIM range.

=SUMPRODUCT(--(INDEX(PBIM,0,1)=$A32),--(MONTH(INDEX(--NOT(ISBLANK(PBIM)),0,2))=MONTH(G1)),INDEX(PBIM,0,2))

=SUMPRODUCT(--(INDEX(PBIM,0,1)=$A32),--NOT(ISBLANK(MONTH(INDEX(PBIM,0,2)))=MONTH(H1)),INDEX(PBIM,0,2))


How do i get Sumproduct to return/leave blank if my date in PBIM is blank or doesn't match column month...

Thanks again....

[tammyl]

Hi Again,

I have a solution to return the result to a blank cell instead of 0/01/1900. a little long winded so if there is a better alternative, please let me know (good for learning curve)

if used an IF statement.

=IF(SUMPRODUCT(--(INDEX(Other,0,1)=$A32),--(MONTH(INDEX(Other,0,2))=MONTH(G$1)),INDEX(Other,0,2))=0/1/1900,"",SUMPRODUCT(--(INDEX(Other,0,1)=$A32),--(MONTH(INDEX(Other,0,2))=MONTH(G$1)),INDEX(Other,0,2)))

Thanks again to xld for your expertise.....