When I type (Sheet "Pretraga") e.g. "Popovi" in cell A2, in cell F2 must be 76300 BIJELJINA, 76218 RAZLJEVO, 75436 SKELANI (see Sheet "Mesta", red color).

I get only first result of search: 76300 BIJELJINA.

Thanks!

Can you mock up an example (without formulas, of course) of what you're trying to get as you results? I'd like to see your end goal.

Ah, never mind, I understand.

What you're after is called STRING CONCATENATION, merging multiple cells into one based on a criterion... and it is not a built in function to Excel.

Fortunately, 1000s of people have faced and solved this with many User Defined Function. The one I use for your situation is called CONCATIF(). I've installed it into your sheet for you.

Here's the code:

Code:

---

Option Explicit

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Rickson, MrExcel MVP
    ' used as exactly like SUMIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex. =ConcatIf($A$1:$A$10, C1, $B$1:$B$10, ",", True)

Dim i As Long, j As Long

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
   
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

---

===========
How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

==========

This function is like a SUMIF() formula with a couple of extra parameters.

The formula in F2 that gives us that answer is:
=CONCATIF(Mesta!$A$1:$A$100,$A2,Mesta!$E$1:$E$100, CHAR(10),FALSE)

First parameter: Range to evaluate
Second param: comparison string.
Third param: Range to return values from
Fourth param: The delimiting string for the concatenation, I'm using a CHAR(10) in this example which is Excel's way of saying "Line Feed"
Fifth param: (optional) TRUE means eliminate duplicated values, FALSE (or omitted) means string all values even if duplicates appear

NOTE: You do have to format those cells in column F to WRAP TEXT if you want this to display properly.

Duplicate, see above.

What to say? You are simply THE BEST.

THANKS! THANKS! THANKS! THANKS! THANKS!

What to say? You are simply THE BEST.

THANKS! THANKS! THANKS! THANKS! THANKS!

Thanks, but if you click on the RATE ANSWER button in the solution offered, it lets you add to my rep on the forum directly. Much appreciated.

I was attempt "RATE ANSWER", but I get message "You must spread some Reputation around before giving it to JBeaucaire again.". How solve that?

Ah, you'll need to vote (good or bad) for someone else's message in another thread before the forum will let you add to my rep again. It's a failsafe to make sure friends don't just sit there and add rep to each other all day.

No worries.