Fill category column based on SKU lookup

Question

I have a Google Sheet with two worksheets.

The first worksheet (Sheet1) contains a list of products with multiple columns. The columns of interest are F and R:

• Column F: Product SKU
• Column R: Category(-ies)

The product SKU consists of two sections. The first one is the category indicator and the second one (after the dash) is the actual product number.

The second one (Sheet2) contains a list of categories:

• Column A: Category SKU
• Column B: Main category
• Column C: Subcategory
• Column D: Subcategory

What I would like to do is autopopulate the category column (R) of the first worksheet with the category names found on the second worksheet based on the product SKU. Since each product belongs to a master category and to one or more subcategories, the category should be populated like this:

Φουλάρια Πασμίνες>Βαμβακερά>Classic

Ie, category and subcategories separated by ">"

Ideally, I would like all product categories to be populated like this:

Φουλάρια Πασμίνες|Φουλάρια Πασμίνες>Βαμβακερά|Φουλάρια Πασμίνες>Βαμβακερά>Classic

because that would assign each product to the main category and to all subcategories.

Here is the Google Sheet: https://docs.google.com/spreadsheets/d/1qY1ry3rJbAexeTy7Y31ueKR9uaYKLXnu1jGbZCsqK1U/edit?usp=sharing

Answer 1

Please try the following

=ArrayFormula(IFERROR(VLOOKUP(REGEXEXTRACT(F3:F268,"(.*)-"),
                  {U3:U98,V3:V98&"|"&V3:V98&" >"&W3:W98&"|"&V3:V98&" >"&W3:W98&" >"&X3:X98},2,0)))

(do adjust ranges to your needs)

(καλές δουλειές)