Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Return/Concatenate All Results Corresponding To Lookup Value

1. I agreed to these rules
Join Date
30th April 2008
Posts
2

## Return/Concatenate All Results Corresponding To Lookup Value

My first post here,

Have some trouble in excel

For example I have this table
A 1
B 1
A 5
C 2
A 2

how to display the A result like this
A 1;5;2 whereas the vlookup only return 1 value

Thank you

Excel Video Tutorials / Excel Dashboards Reports

2. If you wish to continue using this free service.

Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of your immediate problem.

REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.

3. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Return All Results Corresponding To Lookup Value

Hi blinx,

Welcome to board!

UDF.

VB:
```Function KCONCAT(Var As Variant, _
Optional Delim As String = ",", _
Optional Exc As Boolean = True) As String
'Exc - Removing False from output
Dim a, v
If TypeOf Var Is Range Or IsArray(Var) Then
a = Var
For Each v In a
KCONCAT = KCONCAT & Delim & v
Next
If Len(KCONCAT) > 1 Then
If Exc Then
KCONCAT = Trim\$(Replace(Replace(Mid\$(Trim\$(KCONCAT), 2), _
Delim & "False", ""), "False" & Delim, ""))
Else
KCONCAT = Mid\$(Trim\$(KCONCAT), 2)
End If
End If
Else
KCONCAT = Var
End If
End Function

```
Use,

=KCONCAT(IF(A1:A5=C1,B1:B5),";")

where C1 houses A

Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.

HTH

4. I agreed to these rules
Join Date
30th April 2008
Posts
2

## Re: Return/Concatenate All Results Corresponding To Lookup Value

Thx, That's help

Excel Video Tutorials / Excel Dashboards Reports

5. The formula =ConcatIf(A:A,"A",B:B,";") should work with your situation.

The UDF ConcatIf mirrors the syntax of SumIf

=ConcatIf(compareRange,criteria,[stringsRange],[delimiter])

=ConcatIf(compareRange,"<>") will return a string of all the cells in compareRange.

Example:
ColA ColB
w 1
x 2
y 1
z 1
x 3
y 1
x 2
t 2

ConcatIf (\$A:\$A,"x",\$B:\$B,",")= "2,3,2"
ConcatIf (\$B:\$B,1,\$A:\$A)="wyzy"

VB:
```Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, _
Optional ByVal stringsRange As Range, Optional Delimiter As String) As String
Dim i As Long, j As Long, criteriaMet As Boolean

Set compareRange = Application.Intersect(compareRange, _
compareRange.Parent.UsedRange)

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
ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
End If
Next j
Next i
ConcatIf = mid(ConcatIf, Len(Delimiter) + 1)
End Function

```

Excel Video Tutorials / Excel Dashboards Reports

6. I agreed to these rules
Join Date
19th April 2012
Posts
7

## Re: Return All Results Corresponding To Lookup Value

Hi,

This UDF is a massive help. However I was wondering how would it be possible to start each returned value on a new line within the cell that it returns?

Curently my data returns like the below:

Dixon,Carlsen H,Bull,Richard,Benning,Chris,Ali,Daryl

But would like it to return like so:

 Dixon,Carlsen H Bull,Richard, Benning,Chris, Ali,Daryl I know this maybe possible perhaps with the CHAR formula nested in, but not sure how. Thanks in advance. A.

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Return/Concatenate All Results Corresponding To Lookup Value

Hi thaatrain,

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
•
porno