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

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 "")))

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

--

HTH

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


"Monte" wrote in message
...
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 "")))

Thanks Bob. That did it.


"Bob Phillips" wrote:

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

--

HTH

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


"Monte" wrote in message
...

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 "")))

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

"Monte" wrote:

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 "")))