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

Copper Contributor



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 the codes based in Column B "Sites" however as we can see there can be one or multiples codes in Column B "Sites" which are separated by a comma "," and based on those values i want it to return the values found on tab "sheet 1", Column A "Asset Reference" (Image 2 below) in image 2 in Tab Sheet 1Column E "Current Parent" we can reference these codes against Column B "Sites" on tab "Master"


Image 1




Image 2



Any help is appreciated from anyone!

@Hans Vogelaar maybe you might know?






15 Replies


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.

@Hans Vogelaar ,


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




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?

Hi hans,

I sent you a DM with a link to a sample doc.

Look forward to your response :)




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 @Hans Vogelaar  as always!

Can you walk me through how to do this on the actual sheet, these are completely new functions to me. Appreciate it!

best response confirmed by sruk89 (Copper Contributor)


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.

@Hans Vogelaar,


Thanks for explaining, much appreciated!

I have entered in the info as mentioned but I'm getting the following error:





Which version and build of Office do you have? You can check it by selecting File > Account. This is what I see:


@Hans Vogelaar.,



When i open your sample doc you sent me it works fine, however when i try to copy what you've done, it doesn't seem to let me.


Which formula do you see for OrRow in Name Manager (on the Formulas tab of the ribbon) in my version of the workbook?

@Hans Vogelaar ,

This is what can be seen in your version:




This is what is in my version:


The only difference i can see is what i've highlighted in the red circle, not sure whether this would have any significance?





@Hans Vogelaar 


Phase 2b Pilot Scenarios.xlsx - here is the actually file, I have removed the sensitive data.






Yes, that difference is relevant. In the earlier version, Sheet1 contained a table of the same name; in your "real" workbook, Sheet1 does not contain a table. I converted the data on Sheet1 to a table, and used its name (Table1 was assigned by Excel, but you can change it if you prefer) in the formula.

@Hans Vogelaar ,


Ahhh yes, now i see. I have now done that and it works fine!


Thank you so much for you help, patience and time Hans!