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