[Pajoooo]

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!

[JBeaucaire]

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.

[JBeaucaire]

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:

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.

[JBeaucaire]

Duplicate, see above.

[Pajoooo]

What to say? You are simply THE BEST.

THANKS! THANKS! THANKS! THANKS! THANKS!

[Pajoooo]

What to say? You are simply THE BEST.

THANKS! THANKS! THANKS! THANKS! THANKS!

[JBeaucaire]

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.

[Pajoooo]

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

[JBeaucaire]

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.