Excel 2010 Setting Up Table LookUp with 3 Variables

stevezim

New Member
Joined
Jun 19, 2012
Messages
3
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>
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

stevezim

New Member
Joined
Jun 19, 2012
Messages
3
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Maybe ...

Code:
=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

New Member
Joined
Jun 19, 2012
Messages
3
Maybe ...

Code:
=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top