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 22, 2023MVP
In K2 on the Master sheet:
=TEXTJOIN(" / ",TRUE, XLOOKUP(TEXTSPLIT(B2,","),Sheet1!$E$2:$E$1000,Sheet1!$A$2:$A$1000,""))
Adjust the ranges if Sheet1 has more than 1000 data rows.
Fill down.
sruk89
Sep 22, 2023Brass Contributor
Thanks for this much appreciated, it doesn't appear to return all matches, please see below.
In this example in Column B "Sites" i have put "CLCOV" on sheet 1 (Image 2) i did a filter and it returns more than 1 (25 in fact) it only appears to return xxx-LUMN-026
Image 2
- HansVogelaarSep 22, 2023MVP
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- sruk89Sep 23, 2023Brass ContributorHi hans,
I sent you a DM with a link to a sample doc.
Look forward to your response 🙂
Thanks,
Scott- HansVogelaarSep 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.