Thread: NESTED IF & LEFT FUNCTIONS

1. hi all,

i have the following data in a column
RC
R
H3
H1
*
MRC
RC-T

and i use the following formula
=IF(LEFT(W11,1)="R","RE",IF(LEFT(W11,2)="RC","WO", IF(LEFT(W11,1)="H","NEW",IF(LEFT(W11,3)="*","UNDF" ,IF(LEFT(W11,3)="MRC","NEW",)))))

the result i got is

RE
RE
NEW
NEW
UNDF
NEW
RE

and it does not seems to pick up the value for "RC" it set it to "RE" instead of "WO". any suggestions.

dubdub

2. =IF(LEFT(W11,2)="RC","WO",IF(LEFT(W11,1)="R","RE", IF(LEFT(W11,1)="H","NEW",IF(LEFT(W11,3)="*","UNDF" ,IF(LEFT(W11,3)="MRC","NEW",)))))

You need to check the most "onerous" first, otherwise you get the first true answer
eg
If(left(W11,3)="MRC","xxx",left(W11,2)="MR","xx",l eft(W11,1)="M","x"

3. Thanks Mdmackillop.

dubdub

4. Why not make it simpler and just use a vlookup

Place the following in F1:G7

RC WO
R RE
H3 NEW
H1 NEW
* UNDF
MRC NEW
RC-T WO

and then (assuming in column A) the following formula in column B and copy down

=VLOOKUP(A1,\$F\$1:\$G\$7,2,FALSE)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•