Suggested improvement is to substitute:

If Len(ReturnRng(i)) 0 Then txt = txt & ReturnRng(i) & ", "

for:

txt = txt & ReturnRng(i) & ", "

Greg


"Greg Wilson" wrote:

Perhaps this. Paste to a standard code module:

Function ExtractIf(SearchRng As Range, ReturnRng As Range, _
SearchVal As String) As String
Dim i As Long
Dim txt As String
For i = 1 To SearchRng.Count
If SearchRng(i).Value = SearchVal Then
txt = txt & ReturnRng(i) & ", "
End If
Next
txt = Left(txt, Len(txt) - 2)
ExtractIf = txt
End Function

Assumed is that the cell range containing the search value (i.e. 444) is
A1:A25 and the range to extract the text is C1:C25. Enter this in the
appropriate worksheet cell:

=ExtractIf(A1:A25, C1:C25, 444)

Note that the two ranges (SearchRng and ReturnRng) need not be aligned -
e.g. could be A1:A25 and E27:E51.

Regards,
Greg



"eddy56" wrote:


Hi there.

I have this problem and I hope somebody can help me:

A B C
- - - - - - - - - - - - -
1 ! 7320 John Car
2 ! 7320 Marcy Bus
3 ! 7320 Peter Bike
4 ! 1119 John Car
5 ! 1119 Marcy Bus
6 ! 1119 Peter Bike
7 ! 4886 John Car
8 ! 4886 Marcy Bus
9 ! 4886 Peter Bike

I need a formula that returns me the values in column C that matches
the criteria search founded in column A. Example:

=EXTRACTIF(A1:C9,7320,1,3) = {"Car","Bus","Bike"}
or
=EXTRACTIF(A1:C9,7320,1,3) = "Car, Bus, Bike"

Thanks for your help.

Eddy



--
eddy56
------------------------------------------------------------------------
eddy56's Profile: http://www.excelforum.com/member.php...o&userid=37006
View this thread: http://www.excelforum.com/showthread...hreadid=567294