Formula/Macro | Lookup a List of CSVs, Return Two Lists of CSVs

I think you  may not have found quite the right approach yet. Therefore I suggest another. The code below will split the data in your Returns sheet into separate rows for each SKU.

Sub SplitReturnData()
    ' Variatus @TeachExcel 25 Feb 2020

    Dim SpSKU() As String, SpQty() As String
    Dim Arr As Variant
    Dim R As Long
    Dim i As Integer

    Application.ScreenUpdating = False
    ' change the tab's name as appropriate
    ' replace Table index with table name if there are more tables on the sheet
    With Worksheets("Returns Tracking (2)").ListObjects(1)
        For R = .ListRows.Count To 1 Step -1
            If WorksheetFunction.CountA(.ListRows(R).Range) Then
                Arr = .ListRows(R).Range.Value
                SpSKU = Split(Arr(1, NrtSKU), ",")
                SpQty = Split(Arr(1, NrtQty), ",")
                If UBound(SpSKU) Then
                    For i = UBound(SpSKU) To 1 Step -1
                        With .ListRows.Add(R + 1).Range
                            .Value = Arr
                            .Cells(NrtSKU).Value = Trim(SpSKU(i))
                            On Error Resume Next                ' in case quantity is missing
                            .Cells(NrtQty).Value = CInt(SpQty(i))
                        End With
                        With .ListRows(R).Range
                            .Cells(NrtSKU).Value = Trim(SpSKU(0))
                            .Cells(NrtQty).Value = CInt(SpQty(0))
                            On Error GoTo 0
                        End With
                    Next i
                End If
            End If
        Next R
    End With
    Application.ScreenUpdating = True
End Sub

Replace the formula for the total in column B with this array formula (confirm with Ctl+Shift+Enter).

=SUM(IF([SO'#]<>"",1/COUNTIF([SO'#],[SO'#])))

With this setup all the Vlookups become easy, easier still if you combine the two tables you now have into one and return either the first or second column with your formula, the lookup value being the same for both.

This setup would greatly increase the flexibility of your data. For example, you would be able to scrap individual items instead of having to scrap them by batch. But it would deprive you of the overview your current setup affords. This, however, is standard reporting. One just takes the available data and compresses them into whatever shape is desirable. In this case that would mean code doing the precise opposite of what the above procedure does except that, by the time you get done with the idea, the reversal probably won't be so precise. Having the freedom to design the report the way you want you would probably avail yourself of that opportunity to make some amendments.

Please take a look if this suggestion could work for you.