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

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.