Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Google Sheets Search and Sum in two lists

I have a Google Sheets question I was hoping someone could help with.

I have a list of about 200 keywords which looks like the ones below:

**List 1** 
Italy City trip
Italy Roundtrip
Italy Holiday
Hungary City trip
Czechia City trip
Croatia Montenegro Roundtrip
....
....

And I then have another list with jumbled keywords with around 1 million rows. The keywords in this list don't exactly match with the first list. What I need to do is search for the keywords in list 1 (above) in list 2 (below) and sum all corresponding cost values. As you can see in the list below the keywords from list 1 are in the second list but with other keywords around them. For example, I need a formula that will search for "Italy City trip" from list 1, in list 2 and sum the cost when that keyword occurs. In this case, it would be 6 total. Adding the cost of "Italy City trip April" and "Italy City trip June" together.

**List 2**                   Cost
Italy City trip April         1
Italy City trip June          5 
Next week Italy Roundtrip     4
Italy Holiday next week       1
Hungary City holiday trip     9
....
....

I hope that makes sense.

Any help would be greatly appreciated

Solution

  • try:

    =ARRAYFORMULA(QUERY({IFNA(REGEXEXTRACT(PROPER(C1:C), 
 TEXTJOIN("|", 1, SORT(PROPER(A1:A), 1, 0)))), D1:D}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
  group by Col1 
  label sum(Col2)''", 0))

    0