And also if the above formula is not working for you then you can also try some additional method that is based on to the similar principles as the one which has been described above. And if the manual intervention is not working for you’re issue then the coding can provide you the baseline to provide you with the audacity of using the below code along with its functionality.
Code:
Option Explicit
Sub Multilookup2()
Dim Lastrow As Long, lastcol As Long
Dim n As Long, x As Long
Dim SrcSht As Object, DstSht As Object
Dim LookUprange As Range, c As Range
Set SrcSht = Sheets("Sheet1")
Set DstSht = Sheets("Sheet2")
Lastrow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set LookUprange = SrcSht.Range("A1:A" & Lastrow)
For Each c In LookUprange
If WorksheetFunction.CountIf(DstSht.Range("1:1"), c.Value) = 0 Then
n = 1
lastcol = DstSht.Cells(1, Columns.Count).End(xlToLeft).Column + 1
DstSht.Cells(1, lastcol) = c.Value
For x = c.Row To Lastrow
If UCase(SrcSht.Cells(x, 1)) = UCase(c.Value) Then
DstSht.Cells(1, lastcol).Offset(n) = SrcSht.Cells(x, 1).Offset(, 1)
n = n + 1
End If
Next
End If
Next
End Sub
This should work for you if you are a coder freak as you have specified your query with the par specification that requires the expert coding trend to view the issue and get solved its problem.
Bookmarks