How To Rank One Cell Based On A Set Range

[tjeacret]

Hello! I'm needing some help. I am trying to make a scorecard for work that is going to be used for performance. I need the excel formula for how to do this, can't figure it out.

Cell A1 = 3.01
I need Cell A2 to give a ranking of 1 - 5 depending on what A1 is. However, I need the variables to point to an adjustable range on a separate sheet. So in the example below, I need excel boxes to put the less than, between, more than, etc. and have them allowed to change.
If A1 is less than 2.85, cell A2 = 1.
If A1 is between 2.86 to 2.99, cell A2 = 2.
If A1 is between 3.0 and 3.19, cell A2 = 3.
If A1 is between 3.2 and 3.4, cell A2 = 4.
If A1 is more than 3.41, cell A2=5.

 

[AlphaFrog]

Excel Workbook
	A
1	3.01
2	3
Sheet1

Cell Formulas
Range	Formula
A2	=LOOKUP(A1,Sheet2!A1:B5)

Excel Workbook
	A	B
1	0	1
2	2.86	2
3	3	3
4	3.2	4
5	3.41	5
Sheet2

 

[T. Valko]

Hello! I'm needing some help. I am trying to make a scorecard for work that is going to be used for performance. I need the excel formula for how to do this, can't figure it out.

Cell A1 = 3.01
I need Cell A2 to give a ranking of 1 - 5 depending on what A1 is. However, I need the variables to point to an adjustable range on a separate sheet. So in the example below, I need excel boxes to put the less than, between, more than, etc. and have them allowed to change.
If A1 is less than 2.85, cell A2 = 1.
If A1 is between 2.86 to 2.99, cell A2 = 2.
If A1 is between 3.0 and 3.19, cell A2 = 3.
If A1 is between 3.2 and 3.4, cell A2 = 4.
If A1 is more than 3.41, cell A2=5.

Try this...

Create this table on some sheet:

<TABLE style="WIDTH: 105pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=138 border=0 x:str><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" span=3 width=46><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=46 height=17>From</TD><TD id=td_post_2637811 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=46>To</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=46>Value</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0.00</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2.85 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2.86 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2.99 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3.00</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3.19 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3.20</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3.40</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">4 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3.41 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3.41+</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">5 </TD></TR></TBODY></TABLE>

Let's assume that table is on Sheet2 in the range A1:C6. A1:C1 being the column labels.

Then, on some other sheet:

A1 = some numeric value like 3.19

Enter this formula in B1:

=IF(A1="","",LOOKUP(A1,Sheet2!A2:C6))

 

[sanrv1f]

Few other options with the same format suggested by #NAME

Excel Workbook
	A
1	3.01
2	3
3	3
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A2	=VLOOKUP(A1,$D$2:$F$6,3,1)
A3	=MATCH(A1,$D$2:$D$6,1)

Excel Workbook
	D	E	F
1	From	To	Rank
2	0	2.85	1
3	2.86	2.99	2
4	3	3.19	3
5	3.2	3.4	4
6	3.41
Sheet1

Excel 2003