On Mon, 21 Jul 2008 08:46:02 -0700, Michael
wrote:
We have a grid based on the two metrics. We can only look at the two metrics
when trying to find their overall performance. We have a grid so is there a
way to use vlookup to find the spot on the grid that it corresponds to? The
grid is too large to paste here.
Thanks
"M Kan" wrote:
Logically, what differentiates a Champion from Oriented from Performance?
Those seem to be the only 3 classifications. You could create an aggregate
score based on the two performance scores and then use this to VLOOKUP a
classficiation
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips
"Michael" wrote:
I currently have a long If statement to calculate a person's performance
based on their scores. I believe the if statement is too long because Excel
is not liking the formula after I added two more parts to it. If someone has
any idea on how to make this work, I would be more than grateful.
=IF(AND(C3=114,D3=92),"Champion",if(and(c3=114, d3=75),"Oriented",if(and(c3107,
d3-77),"Oriented",if(and(c3=100,d3=79),"Oriented",I f(and(c3=86,d3=80),"Oriented",if(and(c3=85,d3=8 6),"Oriented",if(and(c3=84,d3=87),"Oriented",if(a nd(c3=82,d3=89),
"Oriented",if(and(c3=80,d3=91),"Oriented","Perfo rmance")))))))))
Columns C and D basically just have diffent scores for their performances.
Thanks
0 80 81 82 83 84 85 86
87 88 89 90 91 92 93 94 95
96 97 98 99 100 101 102 103 104
105 106 107 108 109 110 111 112 113
114
0 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1
75 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
2
76 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
2
77 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 2 2 2 2 2 2 2
2
78 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 2 2 2 2 2 2 2
2
79 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1
1 1 1 1 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
80 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
81 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
82 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
83 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
84 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
85 1 1 1 1 1 1 1 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
86 1 1 1 1 1 1 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
87 1 1 1 1 1 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
88 1 1 1 1 1 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
89 1 1 1 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
90 1 1 1 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
91 1 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2
92 1 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2 2 2
3
If you put the above table in cells A1:AK20 of Sheet2 then you can try
the following formula to find the score.
=CHOOSE(VLOOKUP(D3,Sheet2!A1:AK20,MATCH(C3,Sheet2! A1:AK1)),"Performance","Oriented","Champion")
Hope this helps / Lars-Åke