Try this UDF (User Defined function) . From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.
Syntax:
=CONCAT(rngRange,intLookupColumn1, strLookupValue1,
intLookupColumn2, strLookupValue2,
intReturnColumn, strFormat As String, strDelimiter)
rngRange is the data range
intLookupColumn1 is the First lookup column
strLookupValue1 is the value to be looked up in the first lookup column
intLookupColumn2 is the second lookup column
strLookupValue2 is the value to be looked up in the second lookup column
intReturnColumn is the column to be returned
strFormat is optional if to be formatted
strDelimiter is optional delimiter. Default is space
Examples:
'1. With data in A1:C11; to vlookup 'B' and "E" from 2 and 3rd columns and
sort and concatenate each entry with " ," (space followed by a comma) the
formula would be
=concat(A1:C11,2,"B",3,"E",1,"dd-mmm-yy"," ,")
Function CONCAT(rngRange As Range, _
intLookupColumn1 As Integer, strLookupValue1 As String, _
intLookupColumn2 As Integer, strLookupValue2 As String, _
intReturnColumn As Integer, Optional strFormat As String, _
Optional strDelimiter As String = " ")
Dim lngRow As Long, arrDate() As Variant, varDate As Variant
Dim lngTemp1 As Long, lngTemp2 As Long
ReDim arrDate(0)
For lngRow = 1 To rngRange.Rows.Count
If StrComp(CStr(rngRange(lngRow, intLookupColumn1)), _
strLookupValue1, vbTextCompare) = 0 And _
StrComp(CStr(rngRange(lngRow, intLookupColumn2)), _
strLookupValue2, vbTextCompare) = 0 Then
ReDim Preserve arrDate(UBound(arrDate) + 1)
arrDate(UBound(arrDate)) = rngRange(lngRow, intReturnColumn)
End If
Next
If UBound(arrDate) > 1 Then
For lngTemp1 = 1 To UBound(arrDate)
For lngTemp2 = lngTemp1 To UBound(arrDate)
If arrDate(lngTemp1) > arrDate(lngTemp2) Then
varDate = arrDate(lngTemp1)
arrDate(lngTemp1) = arrDate(lngTemp2)
arrDate(lngTemp2) = varDate
End If
Next
Next
End If
For lngTemp1 = 1 To UBound(arrDate)
CONCAT = CONCAT & strDelimiter & Format(arrDate(lngTemp1), strFormat)
Next
CONCAT = Mid(CONCAT, Len(strDelimiter) + 1)
End Function
PS: You can modify this UDF to have the second lookup optional and to have
the sort optional...Thus this could be re-used for all type of LOOKUP()
CONCATENATE() requirements