VBA "index" in a function

[Rolly_Sefu]

Hy

As an index formula would work =Index(A1:A10,Match(C1,B1:B10,0)) this is the most basic index and match.

I need to get multiple results in the same cell divided by a comma based on a value: C1

So I created this function:

Function speciallookup(result_range As Range, lookup_range As Range, lookup_criteria As Double) As String
'result_range=A1:A10
'lookup_range=B1:B10
'lookup_criteria=C1
For Each cell In lookup_range
    If cell = lookup_criteria Then
        x = x & cell.Offset(0, -1) & ", "
    End If
Next cell
speciallookup = Left(x, Len(x) - 2)
End Function

My question is how can I get the result from result_range and not use the cell.offset ?

 

[Domenic]

Try...

Function speciallookup(result_range As Range, lookup_range As Range, lookup_criteria As Double) As Variant
    Dim x As String
    Dim i As Long
    
    If lookup_range.Rows.Count > 1 And lookup_range.Columns.Count > 1 Or _
        result_range.Rows.Count > 1 And result_range.Columns.Count > 1 Or _
        lookup_range.Cells.Count <> result_range.Cells.Count Then
            speciallookup = CVErr(xlErrNA)
            Exit Function
    End If
    
    x = ""
    For i = 1 To lookup_range.Cells.Count
        If lookup_range(i).Value = lookup_criteria Then
            x = x & ", " & result_range(i).Value
        End If
    Next i
    
    If Len(x) = 0 Then
        speciallookup = CVErr(xlErrNA)
    Else
        x = Mid(x, 3)
        speciallookup = x
    End If
    
End Function

Note that the function will return #N/A when no matching entries are found. It also returns #N/A when the ranges are not the same size.

Hope this helps!

 

[Rolly_Sefu]

Hy, thank you, this is exactly what I wanted.

Did not occur to me to use the range as item ( lookup_range(i) ) and then just give the same item nr from the other range.

Briliant.

Thanks.

 

[Domenic]

You're very welcome. Glad I could help.

Cheers!