Sorry it toook so long to get back,
Hope you guys get this, but I have tyhe STRING CONCAT VBA installed
and it works great witha =STRINCONCAT(a1:a100,"; ")
you know it considates the whole range with ; in one cell

I copy and pasted the vlookup_concat VBA and no matter how I plug in the
the actual formula it will only produce one email address from the array
What I find strang is the email address it produces is actually the second
in series
=VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ")
is only producing one of the email address in specified range




So if all my office "names" are in col C and the email are in Col F
Say there is Dallas, Austin and Seatlle, 100 rows down, and F has ind. email
address
FYI Col C is sorted A-Z

=StringContcat(",",IF($C$13:$C$500="Katy",$F$12:$F $500,""))

this is the other suggestion with eith String concat, same result only one
email address,
and I did crtl shft enter too btw
please advise

These are the two VBA codes installed
Function VLOOKUP_CONCAT(rngRange As Range, _
strLookupValue As String, intColumn As Integer, _
Optional strDelimiter As String = " ")
Dim lngRow As Long
For lngRow = 1 To rngRange.Rows.Count
If CStr(rngRange(lngRow, 1)) = strLookupValue Then _
VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _
rngRange(lngRow, intColumn)
Next
VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, len(strDelimiter)+1)
End Function





You would need to add a new function to do this. Are you ok with using the
VBEditor?

Press Alt-F11
Click Insert Module
Paste in this code (sorry, it's a little long, be sure you get it all):

===========
Function StringConcat(Sep As String, ParamArray Args()) As String
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
' StringConcat
' This function concatenates all the elements in the Args array,
' delimited by the Sep character, into a single string. This function
' can be used in an array formula.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean

'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
StringConcat = vbNullString
Exit Function
End If


For N = LBound(Args) To UBound(Args)
''''''''''''''''''''''''''''''''''''''''''''''''
' Loop through the Args
''''''''''''''''''''''''''''''''''''''''''''''''
If IsObject(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' OBJECT
' If we have an object, ensure it
' it a Range. The Range object
' is the only type of object we'll
' work with. Anything else causes
' a #VALUE error.
''''''''''''''''''''''''''''''''''''
If TypeOf Args(N) Is Excel.Range Then
'''''''''''''''''''''''''''''''''''''''''
' If it is a Range, loop through the
' cells and create append the elements
' to the string S.
'''''''''''''''''''''''''''''''''''''''''
For Each R In Args(N).Cells
S = S & R.Text & Sep
Next R
Else
'''''''''''''''''''''''''''''''''
' Unsupported object type. Return
' a #VALUE error.
'''''''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
Exit Function
End If

ElseIf IsArray(Args(N)) = True Then

On Error Resume Next
'''''''''''''''''''''''''''''''''''''
' ARRAY
' If Args(N) is an array, ensure it
' is an allocated array.
'''''''''''''''''''''''''''''''''''''
IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
(LBound(Args(N)) <= UBound(Args(N))))
On Error GoTo 0
If IsArrayAlloc = True Then
''''''''''''''''''''''''''''''''''''
' The array is allocated. Determine
' the number of dimensions of the
' array.
'''''''''''''''''''''''''''''''''''''
NumDims = 1
On Error Resume Next
Err.Clear
NumDims = 1
Do Until Err.Number < 0
LB = LBound(Args(N), NumDims)
If Err.Number = 0 Then
NumDims = NumDims + 1
Else
NumDims = NumDims - 1
End If
Loop
''''''''''''''''''''''''''''''''''
' The array must have either
' one or two dimensions. Greater
' that two caues a #VALUE error.
''''''''''''''''''''''''''''''''''
If NumDims 2 Then
StringConcat = CVErr(xlErrValue)
Exit Function
End If
If NumDims = 1 Then
For M = LBound(Args(N)) To UBound(Args(N))
If Args(N)(M) < vbNullString Then
S = S & Args(N)(M) & Sep
End If
Next M

Else
For M = LBound(Args(N), 1) To UBound(Args(N), 1)
If Args(N)(M, 1) < vbNullString Then
S = S & Args(N)(M, 1) & Sep
End If
Next M
For M = LBound(Args(N), 2) To UBound(Args(N), 2)
If Args(N)(M, 2) < vbNullString Then
S = S & Args(N)(M, 2) & Sep
End If
Next M

End If
Else
S = S & Args(N) & Sep
End If
Else
S = S & Args(N) & Sep
End If
Next N

'''''''''''''''''''''''''''''''''''
' Remove the trailing Sep character
'''''''''''''''''''''''''''''''''''
If Len(Sep) 0 Then
S = Left(S, Len(S) - Len(Sep))
End If

StringConcat = S

End Function
===========

The code is also available he
http://www.cpearson.com/excel/stringconcatenation.aspx

Press Alt-Q to close the editor and save your sheet. You've just added a
function called StringConcat() to your sheet and it works very simply.

If your 1000 cells are range A1:A1000, use this formula in another cell:

=StringConcat(", ",A1:A1000)

Voila! Works like a charm. Will that work for you?
--
"Actually, I AM a rocket scientist." -- JB