Can wildcards be used in the Logic Test of an Excel "IF" function?

[Guest]

Currently, I'm using seven nested "IF" functions to return one of three
account numbers. Is it possible to use a wildcard in the Logic Test to
decrease the formula to three nested "IF" functions? If so, how? If not, any
suggestions?

current formula: =if (cell contents = "CIR CC", return "110",if (cell
contents = "CIR PC", return "110",if (cell contents = "ADV CC", return
"250",if (cell contents = "ADV PC", return "250",if (cell contents = "ED CC",
return "350",if (cell contents = "ED PC", return "350",""))))))

desired formula: =if (cell contents begins with "C", return "110",if(cell
contents begins with "A", return "250",if(cell contents begins with "E",
return "350", otherwise "")))

 

[Bob Phillips]

=IF(LEFT(L1,1)="C","110",IF(LEFT(L1,1)="A","250",IF(LEFT(L1,1)="E","350","")
))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob. That did it.


:

=IF(LEFT(L1,1)="C","110",IF(LEFT(L1,1)="A","250",IF(LEFT(L1,1)="E","350","")))

--

HTH

RP
(remove nothere from the email address if mailing direct)



Currently, I'm using seven nested "IF" functions to return one of three
account numbers. Is it possible to use a wildcard in the Logic Test to
decrease the formula to three nested "IF" functions? If so, how? If not,
anysuggestions?

current formula: =if (cell contents = "CIR CC", return "110",if (cell
contents = "CIR PC", return "110",if (cell contents = "ADV CC", return
"250",if (cell contents = "ADV PC", return "250",if (cell contents = "ED CC",
return "350",if (cell contents = "ED PC", return "350",""))))))

desired formula: =if (cell contents begins with "C", return "110",if(cell
contents begins with "A", return "250",if(cell contents begins with "E",
return "350", otherwise "")))

 

[Guest]

Hi Monte,

You may want to use the Vlookup function. This will free up the problem of
7 cases.

To employ Vlookup, set up a table which list down the codes (CIR CC etc.) in
1 column and numbers in another column. Now use the Vlookup function to
extract numbers from the table. There is good help in the Help menu

Regards,

Ashish Mathur