Hi

First post here... I'm wondering if someone can help.

I'm trying to create a VBA code/formula that allows me to search through data on a spreadsheet and list the results within one cell. Through trawling numerous Excel forums I've so far managed to find VBA code and a user defined formula that does what I want, the only thing is I want the formula to search within an array, rather than a range. I'm afraid altering the VBA is beyond my skills!

I have a series of data - 1000 items reflecting information about 10 properties, with some of the items given a condition rating of A-D. I want to be able to concatenate information about all of the items rated D for building 01 in a single cell. At the moment, the following only gives me all of the condition D rated items for the 10 properties.

User defined formula:

=MYVLOOKUP(lookupvalue,lookup range,index col)

VBA Code:

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
    If r = lookupval Then
        result = result & Chr(10) & r.Offset(0, indexcol - 1)
    End If
Next r
MYVLOOKUP = result
End Function
My thoughts are that the Lookup Range could be an array as per the following, but the code needs to be amended to make it work:

IF($C$2:$C$1001=$G$6,$R$2:$R$1001,"")

Please can you help? I'd be really grateful.