I am trying to look up text and return a value.

Brief explanation:
I am trying to look up text and return a value.

The key words I am looking up are contained in worksheet 1.

Worksheet 2 contains a table with 3 columns. Column 1 is a work order number. Column 2 is a description of the work order with keywords embedded in there somewhere. Column 3 contains a status of the work order (closed, open, researching)


I need to find the work order number and status of the work order for each keyword. Attached is a sample workbook. Help!

My lookup dilemma.xlsx

One solution is to create User Defined Functions (UDF):
Note: you may have to change the sheet name from "worksheet 2" to what ever your actual sheet name is

Option Explicit
Public Function WorkNum(ByVal ServerName As String) As String
    Dim Descriptions    As Worksheet, _
        LastDescript    As Range, _
        Testcell        As Range, _
        NamePosition    As Variant
        
    Set Descriptions = Sheets("worksheet 2")
    Set LastDescript = Descriptions.Cells(Rows.Count, "B").End(xlUp)
    
    With Descriptions.Range("B2", LastDescript)
        Set NamePosition = .Find(ServerName)
    
        If Not NamePosition Is Nothing Then
            WorkNum = NamePosition.Offset(0, -1).Value
            Exit Function
        End If
    End With
End Function

B2 =WorkNum(A2)

---

Public Function Option Explicit
Public Function WorkNum(ByVal ServerName As String) As String
    Dim Descriptions    As Worksheet, _
        LastDescript    As Range, _
        Testcell        As Range, _
        NamePosition    As Variant
        
    Set Descriptions = Sheets("worksheet 2")
    Set LastDescript = Descriptions.Cells(Rows.Count, "B").End(xlUp)
    
    With Descriptions.Range("B2", LastDescript)
        Set NamePosition = .Find(ServerName)
    
        If Not NamePosition Is Nothing Then
            WorkNum = NamePosition.Offset(0, -1).Value
            Exit Function
        End If
    End With
End Function

In C2: =WorkStat(A2)

---

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)