* In IF Statement

[Mike]

Novice Excel 2003 user who knows absolutely nothing about VBA. I am trying
to use an IF statement referencing "1*" in my criteria but it doesn't seem to
be able to do this. I have departments "AR" in cell F1, "BSC" in F2,
"Finish" in F3, and "Surface" in F4. AR is equal to 1, BSC equal to 2,
Finish equal to 3, and Surface equal to 4. In B2:B29 I reference department
numbers 1, 2, 3, or 4, and then a letter equal to A through V. Basically
what I am looking for is a formula that will enter the department name in
C2:C29 based on the department number in B2:B29, ignoring the letter
accompanying the number.

Example:

"1A" entered in cell B2 needs to cause "AR" to appear in C2.
"2F" entered in cell B3 needs to cause "BSC" to appear in C3.

Any help would be greatly appreciated.

 

[CurlyDave]

Try Vlookup
http://www.contextures.com/xlFunctions02.html

 

[AltaEgo]

Some options:
1) Choose() as follows:

=CHOOSE(LEFT(B1,1),$F$1,$F$2,$F$3,$F$4)


- the LEFT() function returns the first character of of the value in column
B (1 to 4)
-the CHOOSE function returns the first to fourth value depending on the
value provided by the LEFT() function.

2) INDEX() as follows

=INDEX($F$1:$F$4,LEFT(B1,1),1)

- the LEFT() function returns the first character of of the value in
column B (1 to 4)
- the INDEX function returns from the table at F1:F4 the value found at the
row returned by the LEFT() function

 

[Mike]

Worked great. Thanks for the tip.

 

[sanjeev]

pls advise What is HTML program