Forum Discussion
sruk89
Sep 22, 2023Brass Contributor
** 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...
- Sep 23, 2023
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.
HansVogelaar
Sep 23, 2023MVP
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.
sruk89
Sep 23, 2023Brass Contributor
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!
- HansVogelaarSep 23, 2023MVP
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.
- sruk89Sep 23, 2023Brass Contributor
Thanks for explaining, much appreciated!
I have entered in the info as mentioned but I'm getting the following error:- HansVogelaarSep 23, 2023MVP
Which version and build of Office do you have? You can check it by selecting File > Account. This is what I see: