Google Sheets loop VLOOKUP and concatenate results
I'm wanting to loop through a list of values in a column (ie: Value1|Value2|Value3), use those to VLOOKUP a column, then return the concatenated values into the cell.
Sheet example: BUSINESSES.csv
| ID | Business Name | Services | Tags | (Expected Outcome) |
|---|---|---|---|---|
| 6259 | 22Handy | Business | Test1|Test2 | |
| 6260 | AAFMAA Wealth | Business|Financial Management | Test1|Test2|Finances|Accounting|Budgeting |
Notice the "TAGS" column is empty. This is where I want to return results (and enter the formula).
The goal is to take each "Service" and look through the following separate sheet example:
Example: SERVICES.csv
| Service Name | Tags to add |
|---|---|
| Financial Management | Finances|Accounting|Budgeting |
| Business | Test|Test2 |
So if a Business has a service of "Business" then it should return "Test1|Test2"
If a business has the services "Business|Financial Management" then it should return "Test1|Test2|Finances|Accounting|Budgeting" because it is pulling tags from both of the services the business is assigned to.
I've tried different combinations of INDEX, VLOOKUP, FILTER, and REGEXMATCH but to no avail.
Something like: =INDEX( FILTER( Services!A2:A, IF( REGEXMATCH( Services!A2:A, C2,))))......
try:
=ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(TRIM(FLATTEN(QUERY(TRANSPOSE(IFNA(VLOOKUP(
IF(IFERROR(SPLIT(C2:C, "|"))="",, SPLIT(C2:C, "|")),
{G:G, SUBSTITUTE(H:H, " ", "×")}, 2, 0))),,9^9))), " ", "|"), "×", " "))
