Forum Discussion
** HELP ** Need to return multiple results/values
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.
I sent you a DM with a link to a sample doc.
Look forward to your response 🙂
Thanks,
Scott
See the attached version. I created a small lambda function - see Name Manager on the Formulas tab of the ribbon - to be able to "or" a range row by row.
Thanks HansVogelaar as always!
Can you walk me through how to do this on the actual sheet, these are completely new functions to me. Appreciate it!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.
Thanks for explaining, much appreciated!
I have entered in the info as mentioned but I'm getting the following error:
