Excel 2010 Setting Up Table LookUp with 3 Variables

[stevezim]

My Excel skills are a little rusty and I was trying to assign scores in a table based on the value of 3 variables (Var) in Excel 2010. I put an example below. The idea is the higher score of Var 1 and Var 2 are better. I also want to add a third value which is an alphanumeric and they are different but essentially of equal value. Here is a table and in the last 2 columns I have put in scores that I would assign the value in this table. If the values don't fall into the assigned ranges I would assign an N/A. Any help anyone can give me would be greatly appreciated in coming up with a formula. Thanks! -- Steve
For example here are 3 scenarios and the score I would want to come out.

<CODE>Var 1 = 108, Var 2 = 0.5, Var3 = Red, Score = 3Var 1 = 117, Var 2 = 1.7, Var 3 = Yellow, Score = 8Var 1 = 98, Var 2 = 2.0, Var 3 = Red, Score = N/A Scores ScoresVar 1 >= Var 1 < Var 2 >= Var 2 < Var 3 Red Yellow100 105 1 2 Red or Yellow 1 2105 110 0 1 Red or Yellow 3 4110 115 1 2 Red or Yellow 5 6115 120 1 2 Red or Yellow 7 8100 105 2 3 Red or Yellow 9 10105 110 2 3 Red or Yellow 11 12</CODE></PRE>

 

[stevezim]

My first time on Mr Excel and not quite sure what I'm doing and saw the formatting of table was messed up and I didn't see how to edit so I thought would try to fix it in the answer. Thanks again for any help you can give me.

My Excel skills are a little rusty and I was trying to assign scores in a table based on the value of 3 variables (Var) in Excel 2010. I put an example below. The idea is the higher score of Var 1 and Var 2 are better. I also want to add a third value which is an alphanumeric and they are different but essentially of equal value. Here is a table and in the last 2 columns I have put in scores that I would assign the value in this table. If the values don't fall into the assigned ranges I would assign an N/A. Any help anyone can give me would be greatly appreciated in coming up with a formula. Thanks! -- Steve
For example here are 3 scenarios and the score I would want to come out.

<CODE>Var 1 = 108, Var 2 = 0.5, Var3 = Red, Score = 3
Var 1 = 117, Var 2 = 1.7, Var 3 = Yellow, Score = 8
Var 1 = 98, Var 2 = 2.0, Var 3 = Red, Score = N/A

					Scores</SPAN>	Scores</SPAN>
Var 1 >=</SPAN>	Var 1 <</SPAN>	Var 2 >=</SPAN>	Var 2 <</SPAN>	Var 3</SPAN>	Red</SPAN>	Yellow</SPAN>
100</SPAN>	105</SPAN>	1</SPAN>	2</SPAN>	Red or Yellow</SPAN>	1</SPAN>	2</SPAN>
105</SPAN>	110</SPAN>	0</SPAN>	1</SPAN>	Red or Yellow</SPAN>	3</SPAN>	4</SPAN>
110</SPAN>	115</SPAN>	1</SPAN>	2</SPAN>	Red or Yellow</SPAN>	5</SPAN>	6</SPAN>
115</SPAN>	120</SPAN>	1</SPAN>	2</SPAN>	Red or Yellow</SPAN>	7</SPAN>	8</SPAN>
100</SPAN>	105</SPAN>	2</SPAN>	3</SPAN>	Red or Yellow</SPAN>	9</SPAN>	10</SPAN>
105</SPAN>	110</SPAN>	2</SPAN>	3</SPAN>	Red or Yellow</SPAN>	11</SPAN>	12</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=4><COL><COL span=2></COLGROUP>

</CODE>

 

[shg]

Maybe ...

=IFERROR(CHOOSE(MATCH(Variable1, {100,105,110,115,120}),
LOOKUP(Variable2, {1,2,3}, {1,8,"-"}) + (Variable3="Y"),
LOOKUP(Variable2, {0,1,2,3}, {3,"-",11,"-"}) + (Variable3="Y"),
LOOKUP(Variable2, {0,1,2}, {"-",5,"-"}) + (Variable3="Y"),
LOOKUP(Variable2, {0,1,2}, {"-",7,"-"}) + (Variable3="Y")), NA())

 

[stevezim]

Maybe ...

=IFERROR(CHOOSE(MATCH(Variable1, {100,105,110,115,120}),
LOOKUP(Variable2, {1,2,3}, {1,8,"-"}) + (Variable3="Y"),
LOOKUP(Variable2, {0,1,2,3}, {3,"-",11,"-"}) + (Variable3="Y"),
LOOKUP(Variable2, {0,1,2}, {"-",5,"-"}) + (Variable3="Y"),
LOOKUP(Variable2, {0,1,2}, {"-",7,"-"}) + (Variable3="Y")), NA())

Thanks shg I really appreciate it... I'll give it a shot!