Question:  I'm trying to do a lookup in Excel where the reference value is stored somewhere in a string.

For example, in row 2 below, cell D2 should return "Fixed Housing Expenses" as cell B2 contains the word "Compuserve". In row 3, cell D3 should return "Salary" as cell B3 contains the word "MyCompany".

Answer:  Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, we've created a custom function called Partial_Lookup which accepts 3 parameters.

1. The first parameter is the full string (ie: 12.34.56.78.010 / Compuserve February)
2. The second parameter is the range that contains the partial string that you wish to find in parameter #1 (ie: CompuServe)
3. The third parameter is the column number in the range from which the matching value must be returned (ie: Fixed housing expenses)

You can press Alt-F11 to view the VBA code.

Macro Code:

The macro code looks like this:

Function Partial_Lookup(pValue As String, pRange As Range, pPosition As Integer) As String

    'pValue is the full string (ie: 12.34.56.78.010 / Compuserve February)
    'pRange is the partial string that you wish to find in pValue (ie: Compuserve)
    'pPosition is the column number in the range from which the matching value
    ' must be returned (ie: Fixed housing expenses)

    Dim LValue As String
    Dim LSearchFor As String

    Dim LCntr As Integer
    Dim LRowStart As Integer
    Dim LRowEnd As Integer
    Dim LColStart As Integer

    Dim LPos As Integer

    On Error GoTo Err_Execute

    'Determine search range
    LRowStart = pRange.Row
    LRowEnd = LRowStart + pRange.Rows.Count - 1
    LColStart = pRange.Column

    'Search each value in the range until the string is found in pValue
    For LCntr = LRowStart To LRowEnd
        LSearchFor = Range(Chr(64 + LColStart) & LCntr).Value
        'Return the matching value
        If InStr(1, pValue, LSearchFor) > 0 Then
            Partial_Lookup = Range(Chr(64 + LColStart + pPosition - 1) & LCntr).Value
            Exit Function
        End If
    Next

    'No match was found
    Partial_Lookup = "n/a"

    Exit Function

Err_Execute:
    'An error occurred
    Partial_Lookup = "Error"

End Function