Google Sheets Vlookup Matching-CodePudding

I have two Gsheets. The first sheet looks like this…

Fruit_Name
Apple
Banana
Orange
Grape
Watermelon 
Mango
Strawberry
Melon
Blueberry
Tangerine
Papaya

The second sheet looks like this…

Red_Fruits  Yellow_Fruits    Purple_Fruits    Orange_Fruits   Grade
Apple       Banana           Blueberry        Orange          A
Apple       Banana           Blackberry       Papaya          B
Cranberry   Lemon            Grape            Tangerine       C
Watermelon  Banana           Blackberry       Melon           B
Strawberry  Mango            Plum             Papaya          D

This is the desired result…

Fruit_Name  Grade
    Apple   A, B
    Banana  A, B, C
    Orange  A
    Grape   C
    …
    Watermelon 
    Mango
    Strawberry
    Melon
    Blueberry
    Tangerine
    Blackberry
    Papaya

The desired result is basically having the solution return multiple grades as needed based on the second column. How can I get this is google sheets? Note column A is a short list of fruits than column B which I why I need it. Thanks.

CodePudding user response：

Give the following formula a try:

=JOIN(", ",FILTER(Sheet2!$E$2:$E,MMULT(--(Sheet2!$A$2:$D=A2),SEQUENCE(COLUMNS(Sheet2!$A$2:$D$2)))))

CodePudding user response：

use:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, REGEXREPLACE(TRIM(SPLIT(FLATTEN(
 QUERY(QUERY(SPLIT(FLATTEN(SEQUENCE(ROWS(D2:G), COLUMNS(D2:G))&"×"&D2:G&"♥×"&H2:H&","), "×"), 
 "select max(Col3) where Col3<>',' group by Col1 pivot Col2"),,9^9)), "♥")), ",$", ), 2, 0)))