Lookup value

[NilsYan]

Hi all,

I want excel to return me the value of 1, when the percentage score is higher than 90%, value of 2, when the the percentage score is between 80% and 90%, and so on. Would appreciate your help.
[TABLE="width: 177"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 177"]
<tbody>[TR]
[TD="align: right"]90%[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]80%[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]70%[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]60%[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]50%[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]40%[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]30%[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]20%[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]10%[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]0%[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

If you sort the table from lowest to highest you can use

Excel 2013/2016
	A	B	C	D
1				33%
2	0%	10		7
3	10%	9
4	20%	8
5	30%	7
6	40%	6
7	50%	5
8	60%	4
9	70%	3
10	80%	2
11	90%	1
Color

Cell Formulas
Range	Formula
D2	=INDEX(B2:B11,MATCH(D1,A2:A11,1))

 

[joeu2004]

=10 - INT(A1*10)

where A1 is the percentage.

 

[NilsYan]

Thanks

 

[NilsYan]

If you sort the table from lowest to highest you can use
Excel 2013/2016

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]33%[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]80%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Color​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=INDEX(B2:B11,MATCH(D1,A2:A11,1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks, it works.

However, when D1=90% D2=1; D1=80% D2=2; D1=70% D2=3; D1=60% D2=4; but when D1=50% D2=6(instead of 5); D1=40% D2=7 (instead of 4); and so on

 

[Aladin Akyurek]

Thanks, it works.

However, when D1=90% D2=1; D1=80% D2=2; D1=70% D2=3; D1=60% D2=4; but when D1=50% D2=6(instead of 5); D1=40% D2=7 (instead of 4); and so on

50% would be classified as 5, not as 6, under Fluff's set up, and it is.

By the way, if you would want to keep the descending order:

=INDEX($B$1:$B$10,MATCH(MIN(D1,MAX($A$1:$A$10)),$A$1:$A$10,-1))

The ascending order is more convenient though.

 

[NilsYan]

50% would be classified as 5, not as 6, under Fluff's set up, and it is.

By the way, if you would want to keep the descending order:

=INDEX($B$1:$B$10,MATCH(MIN(D1,MAX($A$1:$A$10)),$A$1:$A$10,-1))

The ascending order is more convenient though.

Excellent, thanks everyone!