---

MS Excel 2003: Perform a lookup where the reference value is stored somewhere in a string

This Excel tutorial explains how to write a macro to perform a lookup where the reference value is stored somewhere in a string in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2003/XP/2000/97, I'm trying to do a lookup 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 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:
   MsgBox 'An error occurred.'
   Partial_Lookup = "Error"

End Function