I am trying to write a formula for nested if in one of the columns in excel.
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?
Get Names UDF
In Excel e.g. for the value in
A1 use it like this:
or if case-insensitive (A=a,B=b) use:
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)