Forum Discussion

sruk89's avatar
sruk89
Copper Contributor
Sep 22, 2023

** HELP ** Need to return multiple results/values

Hi,   Not sure what is best to use here, XLOOKUP, INDEX & MATCH or FILTER but i need help   What I'm looking to achieve is return multiple values on tab "Master" Column K "Open Orders" based on t...
  • HansVogelaar's avatar
    HansVogelaar
    Sep 23, 2023

    sruk89 

    Step 1:

    On the Formulas tab of the ribbon, in the Defined Names group, click Define Name.

    Enter OrRow in the Name box, and enter the formula =LAMBDA(r,OR(r)) in the Refers To box.

    Click OK to save the name.

    The LAMBDA Function is a relatively new function in Excel. It allows us to create custom functions. This is a very simple one: it ORs the values of the supplied range.

     

    Step 2:

    The formula in K2 on the Master sheet is

     

    =TEXTJOIN(" / ",TRUE, FILTER(Sheet1[Asset Reference],BYROW(Sheet1[Current Parent]=TEXTSPLIT([@Sites],","),OrRow),""))

     

    TEXTSPLIT([@Sites],",") splits the value of the Sites column in the same row with comma as delimiter.

    So if Sites contains "MRBUX,MRCHA", TEXTSPLIT will return "MRBUX" and "MRCHA" as separate values.

     

    Sheet1[Current Parent]=TEXTSPLIT([@Sites],",") compares the Current Parent column of Sheet1 to these values. It returns TRUE for each match, FALSE for each mismatch.

    In the above example, where TEXTSPLIT returns 2 values, Sheet1[Current Parent]=TEXTSPLIT([@Sites],",")  returns 2 columns of TRUE/FALSE values.

     

    Normally, OR tests ALL cells in a range and returns one result. Here, however, we want to test row by row. We want to return a row if either of the values returned by TEXTSPLIT in that row is TRUE (a match).

     

    We use the new BYROW Function for this purpose. We feed it Sheet1[Current Parent]=TEXTSPLIT([@Sites],",") and apply our custom function OrRow to it.

    The result is ONE TRUE/FALSE value per row.

     

    FILTER(Sheet1[Asset Reference],BYROW(Sheet1[Current Parent]=TEXTSPLIT([@Sites],","),OrRow),"")

    filters the Asset Reference column on Sheet1 and returns only the rows for which BYROW(...) returns TRUE.

     

    Finally, TEXTJOIN concatenates the result with " / " as delimiter.

Resources