if statement

[ncapanzana]

Hello,

I need your expertise on excel pls.

I want to populate the column A based on the data available in B, C and D. how can I achieve this? I have to do it

Rules:
1. If the values in column B start with MP or MB, put 'METRO' in the same row in column A
2. If the values in column C start with OM, put 'OTHERS' in the same row in column A
3. If column B and column C are NULL/Blank and the value in column D is <8 , put 'CASH' in the same row in column A
4. If column B and column C are NULL/Blank and the value in column D is >=8, put 'DOOR' in the same row in column A

Notes: if there is a value in column B, column C is always NULL
If there is a value in column C, column B is always NULL

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MB9874[/TD]
[TD][/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MP97124[/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]OM[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]OM[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3.9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
Nino

 

[par60056]

=if(or(left(Bx,2)="MB",left(Bx,2)="MP"),"METRO",if(left(Cx,2)="OM","OTHERS",if(Dx<8,"CASH","DOOR")))

 

[par60056]

This may not be absolutely correct. The sample data does not show a case where there is something in B other than an MB or MP. This assumes if it is not MP or MB column B is treated as empty. The same for Column C having something other than OM in it.

 

[texasalynn]

or another

Excel 2010
	A	B	C	D
2	METRO	MB9874		41
3	METRO	MP97124		8
4	Others		OM	9
5	Others		OM	4
6	CASH			3
7	CASH			7
8	CASH			3.9
9	DOOR			8
10	DOOR			8.5
11	DOOR			10
Sheet1

Cell Formulas
Range	Formula
A2	=IF(OR(LEFT(B2,2)={"MP","MB"}),"METRO",IF(C2="OM","Others",IF(AND(B2="",C2="",D2<8),"CASH",IF(AND(B2="",C2="",D2>=8),"DOOR",""))))

 

[ncapanzana]

thank you very much par60056 & texasalynn... appreciate your prompt solution.

Regards,
Nino