matcha argument value in a range of values, if match is found return the value from the first column

Question

I am trying to write a function that can search an Range of cells in a separate sheet and if there is a match I want to set the cell to the value in the same row but the first column of Range. The first column is initials, the other columns in the range will only be integers. I am writing the function in VBA. So far I can't get my function to even match the a value that is there.

My script:

Public Function searchRange(val As Range, sRng As Range)

    Dim cel As Range
    For Each cel In sRng.Cells

        If cel.value = val.value Then
            searchRange = "found!"
        Else
            searchRange = "not found!"
        End If
    Next cel
End Function

Where my function is in the excel sheet

My Search Range

Scott Craner Scott Craner · Accepted Answer · 2017-10-03T18:40:36

Because you are looping through all the cells only the last cell is ultimately returned. You need to adjust the logic a bit.

Public Function searchRange(val As Range, sRng As Range)

    Dim cel As Range
    For Each cel In sRng.Cells

        If cel.value = val.value Then
            searchRange = "found!"
            Exit Function
        End If

    Next cel

    searchRange = "not found!"
End Function

Now as soon as the value is found it returns the correct result and exits the function. If it goes through the whole loop then the value was not found.

As a bonus:

To return the value in Column A, replace;

searchRange = "found!"

with

searchRange = cel.Parent.Cells(cel.row,1).Value

Or if you do not know the actual column number we can return the value in the first column of the search range provided.

searchRange = cel.offset(,-1*(cel.column-sRng.Column)).Value

And just to be complete:

This can be done with native formula in Excel:

=IFERROR(INDEX('All Therapists'!$A:$A,AGGREGATE(15,6,ROW('All Therapists'!$E$27:$Q$50)/('All Therapists'!$E$27:$Q$50=B3),1),"not found!")

matcha argument value in a range of values, if match is found return the value from the first column

1 Answers