VLOOKUP VBA FORMULA

[Cuscino]

Hi Everyone, VBA noob here (and new to this forum)

I m looking for a particular VBA vlookup that seeks multiple values in one cell separated by a comma (name of the product eg. FHGH, RDSD, etc), in another file.

In this last one, we have: in the first column the values to find in each single row, but they´re incorporated in a full sentence (eg. FHGH Product 12 6volt)

I would like the formula to return to me the sum of the values that are in the second column (where there are the prices of each product)

I hope you could help me

Thanks

[SamT]

IN the workbook with with the list of multiple product names, Insert a Module.

PLace this code in that module

Option Explicit

Function TotalOfAllInCell(Cel As Range) As Currency

Const PriceWorkbookNames As String = "???" 'Edit to suit
Const PriceSheetName As String = "???" 'Edit to suit

Dim PriceSht As Worksheet
Dim Found As Range
Dim TotalPrice As Currency
Dim Products As Variant
Dim i As Long

Set PriceSht = Workbooks(PriceWorkbookNames).Sheets(PriceSheetName)

Products = Split(Cel, ",")   'The Product name separator is a comma. 'To make an array

If Not IsArray(Products) Then 'Only one Product name in Cel
  Set Found = PriceSht.Columns("A").Find(Products)    'Edit to correct Product name column
  If Found Is Nothing Then
    TotalOfAllInCell = 0
  Else
    TotalOfAllInCell = Found.Offset(, 1)
    Exit Function
  End If
  
  'More than one product in Cel
Else
  For i = LBound(Products) To UBound(Products)
    Set Found = PriceSht.Columns("A").Find(Trim(Products(i)))   'Trim to remove excess Spaces
    If Not Found Is Nothing Then TotalPrice = TotalPrice + Found.Offset(, 1)
  Next i
  TotalOfAllInCell = TotalPrice
End If
End Function

In the cell of the Column that will contain the total prices of all the product in the Products Cell of the same Row, (I am assuming that the products column is in "A" and the first products cell is "A2") use this formula.

=TotalOfAllInCell(A2)

Then copy it down as needed

Note that the Code does compile, but it is not tested against your data

[Cuscino]

Great!
It works, but if I have the two file in two different directories How can I do it?

Thanks