PDA

View Full Version : [SOLVED] Formula to return value in months in date range match



tammyl
11-23-2014, 11:45 PM
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
11-24-2014, 05:17 AM
Try this

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

tammyl
11-24-2014, 05:55 PM
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
11-24-2014, 10:40 PM
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.....