simplify and if formula

[d0wnt0wn]

hey guys,

I am wondering if there is a more simple way to do this

=IF(AND(B3=8,C3="B"),4,IF(AND(B3=9,C3="B"),7,IF(AND(B3=7,C3="A"),0,IF(AND(B3=8,C3="A"),2,IF(AND(B3=9,C3="A"),5,IF(AND(B3=7,C3="B"),2,IF(AND(B3=7,C3="C"),4,IF(AND(B3=8,C3="C"),6,IF(AND(B3=9,C3="c"),9,IF(AND(B3=7,C3="B"),2,IF(AND(B3=7,C3="P"),-2,IF(AND(B3=8,C3="P"),0,IF(AND(B3=9,C3="P"),3,"")))))))))))))

essentially its looking for 2 criteria to give me a value. For example if cell B3=8 and C3=A the result is 2..... 8 is woth 2 points and A is worth 0... but what i really want is to make it so i can easily add more criteria to get my result and be able to change the value of each of those criteria without having to change the whole formula each time

 

[Fluff]

How about
=IFERROR(IF(C3="A",CHOOSE(B3-6,0,2,5),IF(C3="B",CHOOSE(B3-6,2,4,7),IF(C3="C",CHOOSE(B3-6,4,6,9),IF(C3="P",CHOOSE(B3-6,-2,0,3),"")))),"")

 

[jasonb75]

Would be best with a lookup table, (formula in D3), I've also included a formula in D4 that works without the table, but doing it that way is just bad practice.

The List.xlsx
	B	C	D	E	F	G	H	I	J
2						A	B	C	P
3	8	B	4		7	0	2	4	-2
4	9	C	9		8	2	4	6	0
5					9	5	7	9	3
Sheet1

Cell Formulas
Range	Formula
D3	D3	=INDEX($G$3:$J$5,MATCH(B3,$F$3:$F$5,0),MATCH(C3,$G$2:$J$2,0))
D4	D4	=IFERROR(INDEX({0,2,4,-2;2,4,6,0;5,7,9,3},MATCH(B4,{7,8,9},0),MATCH(C4,{"A","B","C","P"},0)),"")

 

[Z51]

Another option, using LOOKUP:

=IF(COUNTA(B3:C3)=2,LOOKUP(B3&C3,{"7A","7B","7C","7P","8A","8B","8C","8P","9A","9B","9C","9P"},{0,2,4,-2,2,4,6,0,5,7,9,3}),"")

 

[Fluff]

Another option
=CHOOSE(B3-6,0,2,5)+SWITCH(C3,"A",0,"B",2,"C",4,"P",-2)

 

[d0wnt0wn]

wow lots of answer.... ill try them all and report back thanks!

 

[d0wnt0wn]

Another option
=CHOOSE(B3-6,0,2,5)+SWITCH(C3,"A",0,"B",2,"C",4,"P",-2)

fluff could you please explain this formula to me... it works great but i have never seen choose and switch before... if i want to add more conditions do i just add another +switch?

 

[jasonb75]

Another one based on Fluff's idea from post 5

=CHOOSE(B3-6,0,2,5)+(SEARCH(C3,"PABC")-2)*2

Shorter formulas like this are not always better though, especially if the result values change in a way that you might not be able to follow the original pattern.

 

[Fluff]

For choose

CHOOSE Function

The Excel CHOOSE function returns a value from a list using a given position or index. For example, =CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references.

exceljet.net

Switch

SWITCH Function

The Excel SWITCH function compares one value against a list of values, and returns a result corresponding to the first match found. When no match is found, SWITCH can return an optional default value.

exceljet.net

To add more criteria for C3 just add them to the switch formula.

 

[d0wnt0wn]

fluff could you please explain this formula to me... it works great but i have never seen choose and switch before... if i want to add more conditions do i just add another +switch?

i dont know what the B3-6 part means