[SOLVED] Nested if solution in Microsoft excel for partial matching strings

Issue

I am trying to write a formula for nested if in one of the columns in excel.
For ex:
I want to automatically get the values in one column based on the matching values in another column.
Lets say the two columns are A and B.
If A contains any string like “Bank” then i want the column B to be FGT_Bank_OSP.
If A contains any string like “PDM” then i want the column B to be “FGT_PDM_OSP.
Similarly i have 25 other values.

I tried to use something like this but tjis is not working.

=IF(ISNUMBER(SEARCH("DMT_Bank_Sensitive",E5)), "DMT_Bank_SEN_OSP", IF(ISNUMBER(SEARCH("DMT_PDM_Sensitive",E5)), "DMT_PDM_SEN_OSP"),"No")

Sample Data :Sample Data
Do we have any easy way or any function in excel?

Sample Data 2

Solution

Get Names UDF

In Excel e.g. for the value in A1 use it like this:

=getNames(A1)

or if case-insensitive (A=a,B=b) use:

=getNames(A1,1)

The Code

Option Explicit

Function getNames(SearchString As String, _
  Optional CaseInSensitive1 As VbCompareMethod = 0) As String

    Dim SearchNames, ReturnNames, i As Long

    ' Add more values when needed. This can also be written to
    ' retrieve values from a range.
    SearchNames = Array("Bank", _
                        "CTAS", _
                        "PDM")

    ReturnNames = Array("FDG_Bank_Material", _
                        "FDG_CTAS_Data", _
                        "FDG_PDM_Sensitive")

    For i = 0 To UBound(SearchNames)
        If InStr(1, SearchString, SearchNames(i), CaseInSensitive1) <> 0 Then
            getNames = ReturnNames(i)
            Exit For
        End If
    Next i

End Function

Answered By – VBasic2008

Answer Checked By – Timothy Miller (BugsFixing Admin)