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