# Returning multiple matches and and displaying in a single cell with using UDF

1. ## Returning multiple matches and and displaying in a single cell with using UDF

Hello everyone, This is my first post so I hope I provide the appropriate information to help come up with a working solution.

1. It has to work with SharePoint and the Excel viewer/editor.
2. It can't use UDF because of #1
3. It has to use multiple workbooks because one is replaced daily by an automated system. (I only need to read from this workbook)

Workbook 1 (This is where data entry and formulas reside)

 ProductID (A1) TrackingIDS (B1) AH0001 12345, 67890 AH0002 123, 456, 789

WorkBook 2

 TrackingIDs (A1) ProductID (B1) 12345 AH0001 67890 AH0001 123 AH0002 456 AH0002 789 AH0002

Worksheet 1 column B needs to be populated with the values from Worksheet 2 where Worksheet 1 Column A equals Worksheet 2 Column B.

Any number of rows will need to be returned by the formula. It could be 0 or it could be 20.

I have made this work with UDF but I need to do it with a formula if possible due to the SharePoint and Excel Web component requirements.

2. ## Re: Returning multiple matches and and displaying in a single cell with using UDF

There are going to be limits. I set a maximum of 5 Tracking ID's per product in this ARRAYED Formula in B2

=TRIM(IFERROR(INDEX(Sheet1!\$A\$2:\$A\$6, SMALL(IF(Sheet1!\$B\$2:\$B\$6=A2, ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1),1)),"")&" "&IFERROR(INDEX(Sheet1!\$A\$2:\$A\$6, SMALL(IF(Sheet1!\$B\$2:\$B\$6=A2, ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1),2)),"")&" "&IFERROR(INDEX(Sheet1!\$A\$2:\$A\$6, SMALL(IF(Sheet1!\$B\$2:\$B\$6=A2, ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1),3)),"")&" "& IFERROR(INDEX(Sheet1!\$A\$2:\$A\$6, SMALL(IF(Sheet1!\$B\$2:\$B\$6=A2, ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1),4)),"")&" "&IFERROR(INDEX(Sheet1!\$A\$2:\$A\$6, SMALL(IF(Sheet1!\$B\$2:\$B\$6=A2, ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1),5)),""))

I have a simple space separator because if you use commas, you end up with extra commas at the end of the string.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

3. ## Re: Returning multiple matches and and displaying in a single cell with using UDF

Thank you for your quick response and working example file. I will see if I can make this work since my internal customer is looking for more than 5. By the looks of this I can get it to go a little higher with some manipulation.

4. ## Re: Returning multiple matches and and displaying in a single cell with using UDF

After further testing I have run into a problem and could use further assistance. I was clear about my datasets and this doesn't appear to work as I get past the first 6 rows. Both worksheets could have an infinite number of rows. I have tried replacing the ranges with a larger range and it's returning a blank field for the results.

Thanks.

5. ## Re: Returning multiple matches and and displaying in a single cell with using UDF

Here is an example of it for 6 duplicates and rows 2 to 600. This formula takes a lot of memory and the larger the range, the longer it will take to calculate (depending on your computer). It may not have been returning a blank field, just not done calculating. You may have to go get a cup of coffee or tea. If your range is too large, it becomes impractical so keep it as small as possible. The range of "infinite" that you suggested is definitely too large.

=TRIM(IFERROR(INDEX(Sheet1!\$A\$2:\$A\$600, SMALL(IF(Sheet1!\$B\$2:\$B\$600=A2, ROW(\$A\$2:\$A\$600)-ROW(\$A\$2)+1),1)),"")&" "&IFERROR(INDEX(Sheet1!\$A\$2:\$A\$600, SMALL(IF(Sheet1!\$B\$2:\$B\$600=A2, ROW(\$A\$2:\$A\$600)-ROW(\$A\$2)+1),2)),"")&" "&IFERROR(INDEX(Sheet1!\$A\$2:\$A\$600, SMALL(IF(Sheet1!\$B\$2:\$B\$600=A2, ROW(\$A\$2:\$A\$600)-ROW(\$A\$2)+1),3)),"")&" "& IFERROR(INDEX(Sheet1!\$A\$2:\$A\$600, SMALL(IF(Sheet1!\$B\$2:\$B\$600=A2, ROW(\$A\$2:\$A\$600)-ROW(\$A\$2)+1),4)),"")&" "&IFERROR(INDEX(Sheet1!\$A\$2:\$A\$600, SMALL(IF(Sheet1!\$B\$2:\$B\$600=A2, ROW(\$A\$2:\$A\$600)-ROW(\$A\$2)+1),5)),"") & “ “ & IFERROR(INDEX(Sheet1!\$A\$2:\$A\$600, SMALL(IF(Sheet1!\$B\$2:\$B\$600=A2, ROW(\$A\$2:\$A\$600)-ROW(\$A\$2)+1),1)),""))

6. ## Re: Returning multiple matches and and displaying in a single cell with using UDF

Thanks for all your help. I was able to get the range expanded late last night and it's all working fine.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1