InStr()/Select Case [Excel VBA 2010]

assuming a three column table on sheet Ref with the name criterion in column 1, the output value in column 2 and the output cell in column 3

dim vData as variant
dim n as long
dim bMatch as boolean
vData = sheets("Ref").Range("ref_table").value
for n = lbound(vdata) to ubound(vdata)
    If InStr(ActiveWorkbook.Name, vdata(n, 1)) > 0 Then
        ActiveSheet.Range(vData(n, 3)).Value = vData(n, 2)
        ActiveSheet.Range(vData(n, 3)).Select
        bmatch = true
    end if
   if bmatch then exit for
next n