Hi Will,
Sorry for the delay in replying here, I got caught by a couple of busy days.
I'm sure that one of my colleagues can come up with a miraculous array formula solution for this, but I would tend to reach for VBA in this case. What I've done here is build a custom function for you called "CustomLookup". You'd use it like this:
=CustomLookup(range to look for, range with data table)
So if the data you're looking for is in A5, and your project table is in D5
50:
=CustomLookup(A5,$D$5:$D$50)
The code needs to go into a standard VBA code module, and you can find steps to do that here.
Here's the code:
Code:
Public Function CustomLookup(rngLookFor As Range, rngLookIn As Range) As Boolean
Dim ary() As String
Dim lIndex As Long
Dim lMatch As Long
Application.EnableEvents = False
If IsNumeric(rngLookFor) Then
On Error Resume Next
lMatch = Application.WorksheetFunction.Match(rngLookFor, rngLookIn, False)
If Err.Number = 0 Then
customlookup = True
GoTo ExitPoint
End If
Else
ary() = Split(rngLookFor, ",")
For lIndex = 0 To UBound(ary)
On Error Resume Next
lMatch = Application.WorksheetFunction.Match(CLng(Trim(ary(lIndex))), rngLookIn, False)
If Err.Number = 0 Then
customlookup = True
GoTo ExitPoint
End If
On Error GoTo 0
Next lIndex
End If
ExitPoint:
Application.EnableEvents = True
End Function
Bookmarks