Please help with formula

Vitas · Mar 14th, 2002, 01:38 AM

I need C1 to return one of four values depending on condition in A1:B1
IF A1=A and B1=B than C1=17
IF A1=A and B1=C than C1=20
IF A1=D and B1=B than C1=25
IF A1=D and B1=C than C1=28

What formula or code should I use?
Can anybody help. Thanks in advance.

Mudface · Mar 14th, 2002, 01:58 AM

Well, the following seems to work OK, but I'm sure there's a better way of doing it; it's also case insensitive: -

=IF(AND(A1="A",B1="B"),17,IF(AND(A1="A",B1="C"),20,IF(AND(A1="D",B1="B"),25,IF(AND(A1="D",B1="C"),28,""))))

Aladin Akyurek · Mar 14th, 2002, 02:01 AM

Quote:

On 2002-03-14 00:38, Vitas wrote:
I need C1 to return one of four values depending on condition in A1:B1
IF A1=A and B1=B than C1=17
IF A1=A and B1=C than C1=20
IF A1=D and B1=B than C1=25
IF A1=D and B1=C than C1=28

What formula or code should I use?
Can anybody help. Thanks in advance.

Vitas,

=IF(AND(OR(A1={"A","D"}),OR(B1={"B","C"})),VLOOKUP(A1&B1,{"AB",17;"AC",20;"DB",25;"DC",28},2,0),0)

This formula returns zero if A1 and B1 do not have expected values.

Aladin

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 01:38 AM	#1
Vitas New Member Join Date: Feb 2002 Location: Lithuania Posts: 9	I need C1 to return one of four values depending on condition in A1:B1 IF A1=A and B1=B than C1=17 IF A1=A and B1=C than C1=20 IF A1=D and B1=B than C1=25 IF A1=D and B1=C than C1=28 What formula or code should I use? Can anybody help. Thanks in advance.

Mar 14th, 2002, 01:58 AM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Well, the following seems to work OK, but I'm sure there's a better way of doing it; it's also case insensitive: - =IF(AND(A1="A",B1="B"),17,IF(AND(A1="A",B1="C"),20,IF(AND(A1="D",B1="B"),25,IF(AND(A1="D",B1="C"),28,""))))