# MUST BE A WAY!!! Returning Multiple Results Based on a Single Criteria and Combining Them HELP!!1

#### kescco

##### Board Regular
Okay, I am trying to explain this simply as I can in hopes someone here can help.

In need Excel to search for DOG in Sheet 2 Column A and in Sheet 1 Column B list a combined list of all names it finds in Sheet 2 Column B. An example is as shown below, in Sheet 1 Column B all names I am looking for are listed.

Is this a MATCH, INDEX, CONCATENATE combination? I appreciate you help. I have racked my brains on this one to no avail.

Sheet 1

 ANIMAL TYPES LIFE Dog Collie, Poodle, Labrador 10 Yrs Cat 8 Yrs Fish 3 Yrs

<tbody>
</tbody>

Sheet 2

 ANIMAL Common Name RARE?? Dog Collie No Cat Siamese No Fish Gold No Dog Poodle No Cat Callico No Fish Guppie No Dog Labrador No Cat Burmese No Fish Tynese Trout Yes

<tbody>
</tbody>

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### Peter_SSs

##### MrExcel MVP, Moderator
Try this user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)
Rich (BB code):
``````Function MakeList(s As String, r As Range) As String
Dim a
Dim rws As Long, i As Long
Dim t  As String

a = r.Value
rws = UBound(a, 1)
For i = 1 To rws
If a(i, 1) = s Then t = t & ", " & a(i, 2)
Next i
MakeList = Mid(t, 3)
End Function``````

Excel Workbook
ABC
1ANIMALCommon NameRARE??
2DogCollieNo
3CatSiameseNo
4FishGoldNo
5DogPoodleNo
6CatCallicoNo
7FishGuppieNo
9CatBurmeseNo
10FishTynese TroutYes
Sheet2

Excel Workbook
ABC
1ANIMALTYPESLIFE
3CatSiamese, Callico, Burmese8 Yrs
4FishGold, Guppie, Tynese Trout3 Yrs
Sheet1

Last edited:

#### Rick Rothstein

##### MrExcel MVP
Here is a link to a mini-blog article of mine which includes a function that can do what you want...

LookUp Value and Concatenate All Found Results

The function contained in the article gives you the ability to specify the delimiter, restrict the returned concatenation to unique values only (each concatenated item appears only once in the concatenation) and restrict the search to an exact match (by letter casing).

#### kescco

##### Board Regular
i appreciate the help. I am going to try the code now and see how it works.

Please note the tables were just an example, my actual Worksheets are much large with much more detail but if I can get the code correct, I can modify it for my larger sheets.

Replies
0
Views
344
Replies
0
Views
485
Replies
1
Views
3K
Replies
0
Views
278
Replies
6
Views
611

1,196,010
Messages
6,012,841
Members
441,733
Latest member
MartijnB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back