Pulling multiple lookupvalues in excel's VLOOKUP Formula

Question

expanding off a question I asked previously, but now I'm a little farther ahead thanks to help from you fine folks and from other sources.

Basically I can pull reports from my company server, they are exported in spreadsheet format where each row is a report, and each column is info about the report (report count, date made, report title, etc), the column I am concerned with has a 4 digit code identifying the group that report came from (A205, A206, B208, Q404, there are thousands) Lets call this column the "Report Number"

I am currently using VLOOKUP to find the code on a reference sheet, and then return the name of the group that code is associated with, so if the code is "A205", the formula will instead return "A-TEAM" in the cell. (I have this paired with a macro which does it for hundreds of rows at once, and fills in name in the next empty column)

Right now this works great...IF there is only one code in the "Report Number" column. My problem arises when a report is completed by multiple groups separated by a comma. So in the "Report Number" column, it might have "A205, A206, B208" and I need the formula to output ALL the decoded names in the same format (I.E. "A Team, B Team, C Team) instead of an error, or just the first one.

So, is there a way to do this with VLOOKUP? without nesting IF functions over and over. Or do I need to modify my Macro?

Here is my current macro that works (when I change the parameters to match my worksheet names and whatnot) you can see where the vlookup formula is entered.

Option Explicit
Sub CustomerCodeLookup()

Dim LastRow As Long
Dim LastColumn As Long
Dim RNColumn As Long
Dim RNFirstCell As String

'identify last row of data
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'get first blank column (by assuming first blank cell in row 1 is the first blank column)  
LastColumn = Cells(1, 1).End(xlToRight).Column + 1
'find the column that has "Report Number"
RNColumn = Range("1:1").Find("ReportNumber", LookIn:=xlValues).Column
'Relative address of first cell in Report Number column for use in the formula
RNFirstCell = Cells(2, RNColumn).Address(False, False)

'Add header to the lookup column
Cells(1, LastColumn) = "Group Name"
'insert formula from row 2 until the last data row
Range(Cells(2, LastColumn), Cells(LastRow, LastColumn)) = "=VLOOKUP(LEFT(" & RNFirstCell & ", 5),'C:\Path\to\pulled workbook\[Codes.xlsm]Codereference'!$A:$O,2,0)"
'Remove formula from cells
Range(Cells(2, LastColumn), Cells(LastRow, LastColumn)) = Range(Cells(2, LastColumn), Cells(LastRow, LastColumn)).Value

End Sub

Answer 1

As you don't actually need the formula and to give a try to jeffreyweir array/dictionary suggestion :

Sub CustomerCodeLookup()

Dim P1 As Range, P2 As Range
Dim T2()
Set D1 = CreateObject("scripting.dictionary")
Set P1 = ActiveSheet.UsedRange
Set P2 = Workbooks("Codes.xlsm").Sheets("Codereference").UsedRange
T1 = P1
T3 = P2

For i = 1 To UBound(T3): D1(T3(i, 1)) = T3(i, 2): Next i
For i = 1 To UBound(T1, 2)
    If T1(1, i) Like "ReportNumber" Then RN = i
Next i

a = 1
For i = 2 To UBound(T1)
    ReDim Preserve T2(1 To a)
    St1 = Split(Trim(T1(i, RN)), ",")
    For j = 0 To UBound(St1)
        T2(a) = T2(a) & ", " & D1(St1(j))
    Next j
    T2(a) = Mid(T2(a), 3)
    a = a + 1
Next i

Range("A1").End(xlToRight).Offset(1, 1).Resize(a - 1) = Application.Transpose(T2)

End Sub

EDIT :

Sub CustomerCodeLookup()

Dim P1 As Range, P2 As Range
Dim T2()
Set D1 = CreateObject("scripting.dictionary")
Set P1 = ActiveSheet.UsedRange
Set P2 = Workbooks("Codes.xlsm").Sheets("Codereference").UsedRange
T1 = P1
T3 = P2

'Line below feeds the dictionary as D1(Key)=Item where Key (T3(i, 1)) is first used column of Workbooks("Codes.xlsm").Sheets("Codereference") and Item (T3(i, 2)) second column
For i = 1 To UBound(T3): D1(T3(i, 1)) = T3(i, 2): Next i

For i = 1 To UBound(T1, 2)
    If T1(1, i) Like "ReportNumber" Then RN = i
Next i

a = 1
For i = 2 To UBound(T1)
    ReDim Preserve T2(1 To a)
    St1 = Split(Trim(T1(i, RN)), ",")
    For j = 0 To UBound(St1)
        T2(a) = T2(a) & ", " & D1(Left(Trim(St1(j)), 5))
    Next j
    T2(a) = Mid(T2(a), 3)
    a = a + 1
Next i

Range("A1").End(xlToRight).Offset(1, 1).Resize(a - 1) = Application.Transpose(T2)
Range("A1").End(xlToRight).Offset(0, 1) = "Group Name"

End Sub

Answer 2

You don't really want to be using VLOOKUP to solve this. Depending on what version of Excel you have, any of the following will be better approaches

• Pull all the data into VBA using a Variant Array and use Array manipulation
• Pull all the data into VBA using a Variant Array and use Scripting Dictionaries
• If you've got Excel 2016 or laterUse, use PowerQuery to pull the data into Excel, and shape it how you need it right in the PowerQuery interface.
• If you've got Excel 2013 or later, import the separate tables into the DataModel and join them on the common field, so that you can use a PivotTable to do the reporting you describe. (You can do this even if you don't have the PowerPivot add-in in the SKU of Excel you have installed)

Note that both PowerQuery and PowerPivot are available as a free addin if you're running a version between 2010 and the dates I mention above.

PowerQuery and PowerPivot are by far the easiest way to tackle your issue, and there are plenty of resources on the net and videos on YouTube to get you started.