Multiple Between formulas to return a Score.

[ajay_gajree]

<3 10
3 to <6 8
6 to <10 6
10 to <15 4
15 to <20 2
20 to <30 0
30+

Hi I have a set of data with Criteria above for a scorecard.

I want to write a formula so that depending on where a value falls in the LH column, I can return its score from the RH Column.

Is there an easy way to do this?

So if I had a value 11, I want to return a score of 4 in the cell.

I'm sure this must be fairly straight forward!

Best regards

 

[Yahya]

see if this will help you

Excel Workbook
	LC	LD	LE	LF	LG	LH	LI
1	0	10				11	4
2	3	8
3	6	6
4	10	4
5	15	2
6	20	0
Sheet3

Excel 2007

Cell Formulas
Range	Formula
LI1	=LOOKUP(LH1,LC1:LC7,LD1:LD7)

 

[ajay_gajree]

Thank you for your help, but I think I was not clear, my range are Percentages,

I got the answer elsewhere,

The formula was

=IF((LEN(K14)=0),"Missing data",IF(K14>=0.3,0,IF(K14>=0.2,1,IF(K14>=0.15,2,IF(K14>=0.1,4,IF(K14>=0.06,6,IF(K14>=0.03,8,IF(K14>=0,10,"Error"))))))))

Where K14 is my Percentage rate.

Thanks all.

 

[Yahya]

try this

=IF((LEN(K14)=0),"Missing data",LOOKUP(K14,{0;0.03;0.06;0.1;0.15;0.2},{10;8;6;4;2;1}))